John Arbash Meinel <[EMAIL PROTECTED]> writes: > > -> Hash (cost=1418.68..1418.68 rows=3226 width=4) (actual > > time=77.062..77.062 rows=0 loops=1) > > This seems to be at least one of the problems. The planner thinks there > are going to be 3000+ rows, but in reality there are 0.
No, that's a red herring. Hash nodes always report 0 rows. > > Nested Loop (cost=0.00..23849.81 rows=7533 width=8) (actual > > time=0.341..198.162 rows=5798 loops=1) > > -> Seq Scan on pdb_entry (cost=0.00..1418.68 rows=3226 width=4) > > (actual time=0.145..78.177 rows=3329 loops=1) > > Filter: ((resolution > 0::double precision) AND (resolution < > > 1.7::double precision)) > > -> Index Scan using chain_pdb_id_ind on "chain" (cost=0.00..6.87 > > rows=6 width=8) (actual time=0.021..0.027 rows=2 loops=3329) > > Index Cond: ("outer".id = "chain".pdb_id) The actual number of records is pretty close to the estimated number. And the difference seems to come primarily from selectivity of the join where it thinks an average of 6 rows will match every row whereas in fact an average of about 2 rows matches. So it thinks it's going to read about 18,000 records out of 67,000 or about 25%. In that case the sequential scan is almost certainly better. In fact it's going to read about 6,000 or just under 10%, in which case the sequential scan is probably still better but it's not so clear. I suspect the only reason you're seeing such a big difference when I would expect it to be about even is because nearly all the data is cached. In that case the non-sequential access pattern of the nested loop has little effect. You might get away with lowering random_page_cost but since it thinks it's going to read 25% of the table I suspect you'll have to get very close to 1 before it switches over, if it does even then. Be careful about tuning settings like this based on a single query, especially to unrealistically low values. You might also want to try raising the statistics target on pdb_entry. See if that makes the estimate go down from 6 to closer to 2. -- greg ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])