pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTwo fast searches turn slow when used with OR clause
2+ messages / 2 participants
[nested] [flat]
* Two fast searches turn slow when used with OR clause
@ 2010-08-05 18:34 Craig James <craig_james@emolecules.com>
2010-08-18 03:22 ` Re: Two fast searches turn slow when used with OR clause Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Craig James @ 2010-08-05 18:34 UTC (permalink / raw)
To: pgsql-performance
I can query either my PARENT table joined to PRICES, or my VERSION table joined to PRICES, and get an answer in 30-40 msec. But put the two together, it jumps to 4 seconds. What am I missing here? I figured this query would be nearly instantaneous. The VERSION.ISOSMILES and PARENT.ISOSMILES columns both have unique indexes. Instead of using these indexes, it's doing a full-table scan of both tables, even though there can't possibly be more than one match in each table.
I guess I could rewrite this as a UNION of the two subqueries, but that seems contrived.
This is PG 8.3.10 on Linux.
Thanks,
Craig
=> explain analyze select p.price, p.amount, p.units, s.catalogue_id, vn.version_id
-> from plus p join sample s
-> on (p.compound_id = s.compound_id and p.supplier_id = s.supplier_id)
-> join version vn on (s.version_id = vn.version_id) join parent pn
-> on (s.parent_id = pn.parent_id)
-> where vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'
-> or pn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'
-> order by price;
Sort (cost=71922.00..71922.00 rows=1 width=19) (actual time=4337.114..4337.122 rows=10 loops=1)
Sort Key: p.price Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=18407.53..71921.99 rows=1 width=19) (actual time=1122.685..4337.028 rows=10 loops=1)
-> Hash Join (cost=18407.53..71903.71 rows=4 width=20) (actual time=1122.624..4336.682 rows=7 loops=1)
Hash Cond: (s.version_id = vn.version_id)
Join Filter: ((vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'::text) OR (pn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'::text))
-> Hash Join (cost=8807.15..44470.73 rows=620264 width=54) (actual time=431.501..2541.329 rows=620264 loops=1)
Hash Cond: (s.parent_id = pn.parent_id)
-> Seq Scan on sample s (cost=0.00..21707.64 rows=620264 width=24) (actual time=0.008..471.340 rows=620264 loops=1)
-> Hash (cost=5335.40..5335.40 rows=277740 width=38) (actual time=431.166..431.166 rows=277740 loops=1)
-> Seq Scan on parent pn (cost=0.00..5335.40 rows=277740 width=38) (actual time=0.012..195.822 rows=277740 loops=1)
-> Hash (cost=5884.06..5884.06 rows=297306 width=38) (actual time=467.267..467.267 rows=297306 loops=1)
-> Seq Scan on version vn (cost=0.00..5884.06 rows=297306 width=38) (actual time=0.017..215.285 rows=297306 loops=1)
-> Index Scan using i_plus_compound_id on plus p (cost=0.00..4.51 rows=4 width=26) (actual time=0.039..0.041 rows=1 loops=7)
Index Cond: ((p.supplier_id = s.supplier_id) AND (p.compound_id = s.compound_id))
Total runtime: 4344.222 ms
(17 rows)
If I only query the VERSION table, it's very fast:
x=> explain analyze select p.price, p.amount, p.units, s.catalogue_id, vn.version_id
-> from plus p
-> join sample s on (p.compound_id = s.compound_id and p.supplier_id = s.supplier_id)
-> join version vn on (s.version_id = vn.version_id)
-> where vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1' order by price;
Sort (cost=45.73..45.74 rows=1 width=19) (actual time=32.438..32.448 rows=10 loops=1)
Sort Key: p.price
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..45.72 rows=1 width=19) (actual time=32.309..32.411 rows=10 loops=1)
-> Nested Loop (cost=0.00..36.58 rows=2 width=20) (actual time=32.295..32.319 rows=7 loops=1)
-> Index Scan using i_version_isosmiles on version vn (cost=0.00..8.39 rows=1 width=4) (actual time=32.280..32.281 rows=1 loops=1)
Index Cond: (isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'::text)
-> Index Scan using i_sample_version_id on sample s (cost=0.00..28.12 rows=6 width=20) (actual time=0.011..0.024 rows=7 loops=1)
Index Cond: (s.version_id = vn.version_id)
-> Index Scan using i_plus_compound_id on plus p (cost=0.00..4.51 rows=4 width=26) (actual time=0.010..0.011 rows=1 loops=7)
Index Cond: ((p.supplier_id = s.supplier_id) AND (p.compound_id = s.compound_id))
Total runtime: 32.528 ms
(12 rows)
Same good performance if I only query the PARENT table:
x=> explain analyze select p.price, p.amount, p.units, s.catalogue_id, pn.parent_id from plus p join sample s on (p.compound_id = s.compound_id and p.supplier_id = s.supplier_id) join parent pn on (s.parent_id = pn.parent_id) where pn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1' order by price;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=57.73..57.74 rows=1 width=19) (actual time=43.564..43.564 rows=10 loops=1)
Sort Key: p.price
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..57.72 rows=1 width=19) (actual time=43.429..43.537 rows=10 loops=1)
-> Nested Loop (cost=0.00..48.58 rows=2 width=20) (actual time=43.407..43.430 rows=7 loops=1)
-> Index Scan using i_parent_isosmiles on parent pn (cost=0.00..8.38 rows=1 width=4) (actual time=27.342..27.343 rows=1 loops=1)
Index Cond: (isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'::text)
-> Index Scan using i_sample_parent_id on sample s (cost=0.00..40.09 rows=9 width=20) (actual time=16.057..16.070 rows=7 loops=1)
Index Cond: (s.parent_id = pn.parent_id)
-> Index Scan using i_plus_compound_id on plus p (cost=0.00..4.51 rows=4 width=26) (actual time=0.010..0.011 rows=1 loops=7)
Index Cond: ((p.supplier_id = s.supplier_id) AND (p.compound_id = s.compound_id))
Total runtime: 43.628 ms
x=> \d version
Table "x.version"
Column | Type | Modifiers
------------+---------+-----------
version_id | integer | not null
parent_id | integer | not null
isosmiles | text | not null
coord_2d | text |
Indexes:
"version_pkey" PRIMARY KEY, btree (version_id)
"i_version_isosmiles" UNIQUE, btree (isosmiles)
"i_version_parent_id" btree (parent_id)
Foreign-key constraints:
"fk_parent" FOREIGN KEY (parent_id) REFERENCES parent(parent_id) ON DELETE CASCADE
x=> \d parent
Table "x.parent"
Column | Type | Modifiers
-----------+---------+-----------
parent_id | integer | not null
isosmiles | text | not null
coord_2d | text |
Indexes:
"parent_pkey" PRIMARY KEY, btree (parent_id)
"i_parent_isosmiles" UNIQUE, btree (isosmiles)
=> \d sample
Table "reaxys.sample"
Column | Type | Modifiers
--------------------+---------+-----------------------------------------------------
sample_id | integer | not null default nextval('sample_id_seq'::regclass)
sample_id_src | integer |
parent_id | integer | not null
version_id | integer | not null
supplier_id | integer | not null
catalogue_id | integer | not null
catalogue_issue_id | integer | not null
load_id | integer | not null
load_file_id | integer |
compound_id | text | not null
cas_number | text |
purity | text |
chemical_name | text |
url | text |
price_code | text |
comment | text |
salt_comment | text |
Indexes:
"sample_pkey" PRIMARY KEY, btree (sample_id)
"i_sample_casno" btree (cas_number)
"i_sample_catalogue_id" btree (catalogue_id)
"i_sample_catalogue_issue_id" btree (catalogue_issue_id)
"i_sample_chem_name" btree (chemical_name)
"i_sample_compound_id" btree (compound_id)
"i_sample_load_id" btree (load_id)
"i_sample_parent_id" btree (parent_id)
"i_sample_sample_id_src" btree (sample_id_src)
"i_sample_supplier_id" btree (supplier_id)
"i_sample_version_id" btree (version_id)
Foreign-key constraints:
"fk_item" FOREIGN KEY (version_id) REFERENCES version(version_id) ON DELETE CASCADE
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Two fast searches turn slow when used with OR clause
2010-08-05 18:34 Two fast searches turn slow when used with OR clause Craig James <craig_james@emolecules.com>
@ 2010-08-18 03:22 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Robert Haas @ 2010-08-18 03:22 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Thu, Aug 5, 2010 at 2:34 PM, Craig James <craig_james@emolecules.com> wrote:
> => explain analyze select p.price, p.amount, p.units, s.catalogue_id,
> vn.version_id
> -> from plus p join sample s
> -> on (p.compound_id = s.compound_id and p.supplier_id = s.supplier_id)
> -> join version vn on (s.version_id = vn.version_id) join parent pn
> -> on (s.parent_id = pn.parent_id)
> -> where vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'
> -> or pn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1'
> -> order by price;
Well, you can't evaluate the WHERE clause here until you've joined {s vn pn}.
> If I only query the VERSION table, it's very fast:
>
> x=> explain analyze select p.price, p.amount, p.units, s.catalogue_id,
> vn.version_id
> -> from plus p
> -> join sample s on (p.compound_id = s.compound_id and p.supplier_id =
> s.supplier_id)
> -> join version vn on (s.version_id = vn.version_id)
> -> where vn.isosmiles = 'Fc1ncccc1B1OC(C)(C)C(C)(C)O1' order by price;
But here you can push the WHERE clause all the way down to the vn
table, and evaluate it right at the get go, which is pretty much
exactly what is happening.
In the first case, you have to join all 297,306 vn rows against s,
because they could be interesting if the other half of the WHERE
clause turns out to hold. In the second case, you can throw away
297,305 of those 297,306 rows before doing anything else, because
there's no possibility that they can ever be interesting.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-08-18 03:22 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-05 18:34 Two fast searches turn slow when used with OR clause Craig James <craig_james@emolecules.com>
2010-08-18 03:22 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox