I only have a few days of experience with postgres and it is working
great, but when I start building up test queries I ran into a problem I
don't understand.
One query works fast, returning results in under a second. If I insert
one more join into the table however, it switches to nested-loops and
takes minutes. It does this randomly.. using integer compares in the
query lines is fast for instance, but using LIKE operators causes it to
use loops again.
I have about 200,000 items per table, so nested loops cause lots of pain.
The database has objects with lots (100 to 1000) 'specs' for each object
in another table, so I have to pull them out to do queries and sorting on
them.
Here are the two queries. They are the same except the first has
two 'find' joins and the other has three.
I assume that I am hitting some limit somewhere that is causing postgres
to change it's idea on how to run the query. Can I force it to use hash
joins?
Thanks!
So this is fast...
EXPLAIN ANALYZE SELECT * FROM logical
LEFT JOIN model ON logical.uid = model.logical_uid
LEFT JOIN company ON model.company_uid = company.uid
LEFT JOIN type ON logical.type::INT = type.uid
JOIN specs spec_find1 ON spec_find1 .spec_uid='8' AND spec_find1 .text LIKE '%'
AND spec_find1 .logical_uid=model.logical_uid
JOIN specs spec_find2 ON spec_find2.spec_uid='8' AND spec_find2.text LIKE '%'
AND spec_find2.logical_uid=model.logical_uid
LEFT JOIN specs specs_sort ON specs_sort.spec_uid='4' AND
specs_sort.logical_uid=logical.uid
ORDER BY specs_sort.num;
QUERY PLAN
-
Sort (cost=5398.43..5398.44 rows=6 width=247) (actual time=331.546..333.303
rows=3555 loops=1)
Sort Key: specs_sort.num
Sort Method: quicksort Memory: 981kB
-> Hash Left Join (cost=1087.68..5398.35 rows=6 width=247) (actual
time=37.309..315.451 rows=3555 loops=1)
Hash Cond: (model.company_uid = company.uid)
-> Hash Left Join (cost=1086.28..5396.86 rows=6 width=217) (actual
time=37.235..308.787 rows=3555 loops=1)
Hash Cond: (logical.uid = specs_sort.logical_uid)
-> Hash Left Join (cost=694.84..5004.96 rows=6 width=180)
(actual time=22.433..284.832 rows=3555 loops=1)
Hash Cond: ((logical.type)::integer = type.uid)
-> Nested Loop (cost=693.55..5003.62 rows=6 width=168)
(actual time=22.361..273.502 rows=3555 loops=1)
-> Hash Join (cost=693.55..4953.84 rows=6
width=110) (actual time=22.330..237.717 rows=3555 loops=1)
Hash Cond: (model.logical_uid =
spec_find1.logical_uid)
-> Seq Scan on model (cost=0.00..3337.82
rows=184182 width=36) (actual time=0.017..99.289 rows=184182 loops=1)
-> Hash (cost=691.60..691.60 rows=156
width=74) (actual time=21.795..21.795 rows=2196 loops=1)
-> Hash Join (cost=339.84..691.60
rows=156 width=74) (actual time=8.558..19.060 rows=2196 loops=1)
Hash Cond: (spec_find1.logical_uid
= spec_find2.logical_uid)
-> Seq Scan on specs spec_find1
(cost=0.00..326.89 rows=1036 width=37) (actual time=0.023..6.765 rows=2196
loops=1)
Filter: (((text)::text ~~
'%'::text) AND (spec_uid = 8))
-> Hash (cost=326.89..326.89
rows=1036 width=37) (actual time=8.508..8.508 rows=2196 loops=1)
-> Seq Scan on specs
spec_find2 (cost=0.00..326.89 rows=1036 width=37) (actual time=0.010..6.667
rows=2196 loops=1)
Filter: (((text)::text
~~ '%'::text) AND (spec_uid = 8))
-> Index Scan using logical_pkey on logical
(cost=0.00..8.28 rows=1 width=58) (actual time=0.006..0.007 rows=1 loops=3555)
Index Cond: (logical.uid = model.logical_uid)
-> Hash (cost=1.13..1.13 rows=13 width=12) (actual
time=0.024..0.024 rows=13 loops=1)
-> Seq Scan on type (cost=0.00..1.13 rows=13
width=12) (actual time=0.004..0.011 rows=13 loops=1)
-> Hash (cost=287.57..287.57 rows=8309 width=37) (actual
time=14.773..14.773 rows=8172 loops=1)
-> Seq Scan on specs specs_sort (cost=0.00..287.57
rows=8309 width=37) (actual time=0.012..8.206 rows=8172 loops=1)
Filter: (spec_uid = 4)
-> Hash (cost=1.18..1.1
