I have three tables in my app, call them tableA
, tableB
, and tableC
. tableA
has fields for tableB_id
and tableC_id
, with indexes on both. tableB
has a field foo
with an index, and tableC
has a field bar
with an index.
When I do the following query:
select *
from tableA
left outer join tableB on tableB.id = tableA.tableB_id
where lower(tableB.foo) = lower(my_input)
it is really slow (~1 second).
When I do the following query:
select *
from tableA
left outer join tableC on tableC.id = tabelA.tableC_id
where lower(tableC.bar) = lower(my_input)
it is really fast (~20 ms).
From what I can tell, the tables are about the same size.
Any ideas as to the huge performance difference between the two queries?
UPDATES
Table sizes:
tableA: 2061392 rows
tableB: 175339 rows
tableC: 1888912 rows
postgresql-performance tag info
Postgres version - 9.3.5
Full text of the queries are above.
Relevant info from tables:
- tableA
- tableB_id, integer, no modifiers, storage plain
- "index_tableA_on_tableB_id" btree (tableB_id)
- tableC_id, integer, no modifiers, storage plain,
- "index_tableA_on_tableB_id" btree (tableC_id)
- tableB_id, integer, no modifiers, storage plain
- tableB
- id, integer, not null default nextval('tableB_id_seq'::regclass), storage plain
- "tableB_pkey" PRIMARY_KEY, btree (id)
- foo, character varying(255), no modifiers, storage extended
- "index_tableB_on_lower_foo_tableD" UNIQUE, btree (lower(foo::text), tableD_id)
- tableD is a separate table that is otherwise irrelevant
- "index_tableB_on_lower_foo_tableD" UNIQUE, btree (lower(foo::text), tableD_id)
- id, integer, not null default nextval('tableB_id_seq'::regclass), storage plain
- tableC
- id, integer, not null default nextval('tableC_id_seq'::regclass), storage plain
- "tableC_pkey" PRIMARY_KEY, btree (id)
- bar, character varying(255), no modifiers, storage extended
- "index_tableC_on_tableB_id_and_bar" UNIQUE, btree (tableB_id, bar)
- "index_tableC_on_lower_bar" btree (lower(bar::text))
- id, integer, not null default nextval('tableC_id_seq'::regclass), storage plain
Hardware:
OS X 10.10.2
CPU: 1.4 GHz Intel Core i5
Memory: 8 GB 1600 MHz DDR3
Graphics: Intel HD Graphics 5000 1536 MB
Solution
Looks like running vacuum and then analyze on all three tables fixed the issue. After running the commands, the slow query started using "index_patients_on_foo_tableD".
lower(tableB.foo) = lower(my_input)
won't use an index. Paste CREATE TABLE and some INSERT statements into your question. – Mike Sherrill 'Cat Recall'VACUUM
solved the issue, you should inspect your autovacuum settings Normally autovacuum should kick in and clean up automatically - unless you constantly have locks on the table. – Erwin Brandstetter