Mark Kirkwood <markir@paradise.net.nz> writes:
> Might be worth trying a larger statistics target (say 100), in the hope
> that the planner then has better information to work with.
I concur with that suggestion. Looking at Marc's problem:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=13918.23..26550.58 rows=17 width=8) (actual time=4053.403..83481.769 rows=13415 loops=1)
Hash Cond: ("outer".url_id = "inner".rec_id)
-> Index Scan using n8_word on ndict8 (cost=0.00..12616.09 rows=3219 width=8) (actual time=113.645..79163.431
rows=15533loops=1)
Index Cond: (word_id = 417851441)
-> Hash (cost=13913.31..13913.31 rows=1968 width=4) (actual time=3920.597..3920.597 rows=0 loops=1)
-> Seq Scan on url (cost=0.00..13913.31 rows=1968 width=4) (actual time=3.837..2377.853 rows=304811 loops=1)
Filter: ((url || ''::text) ~~ 'http://archives.postgresql.org/%%'::text)
Total runtime: 83578.572 ms
(8 rows)
the slowness is not really in the LIKE, it's in the indexscan on
ndict8 (79 out of 83 seconds spent there). The planner probably would
not have chosen this plan if it hadn't been off by a factor of 5 on the
rows estimate. So try knocking up the stats target for ndict8.word_id,
re-analyze, and see what happens.
regards, tom lane
