Search Postgresql Archives

Re: Forcing the right queryplan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Henk van Lingen wrote:
Now there are two types of query plans:

syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, syslogtag, infounitid, message FROM systemevents WHERE ( ( to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) ) ORDER BY id DESC LIMIT 100; QUERY PLAN Limit (cost=0.00..10177.22 rows=100 width=159)
   ->  Index Scan Backward using systemevents_pkey on systemevents  (cost=0.00..
1052934.86 rows=10346 width=159)
         Filter: (to_tsvector('english'::regconfig, message) @@ to_tsquery('131.
211.112.9'::text))
(3 rows)

This one is useless (takes very long). However this one:
Hello Henk,

I saw your other mail today, I'm replying on this one for better formatting.

With a limit of 100 the planner guesses it will find 100 matching rows within some cost. At 500 rows the cost is higher than that of the second plan:
syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, syslogtag, infounitid, message FROM systemevents WHERE (  ( to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) )  ORDER BY id DESC LIMIT 500;
QUERY PLAN --------------------------------------------------------------------------------
-----------------------------------
 Limit  (cost=40928.89..40930.14 rows=500 width=159)
   ->  Sort  (cost=40928.89..40954.76 rows=10346 width=159)
         Sort Key: id
         ->  Bitmap Heap Scan on systemevents  (cost=2898.06..40413.36 rows=1034
6 width=159)
               Recheck Cond: (to_tsvector('english'::regconfig, message) @@ to_t
squery('131.211.112.9'::text))
               ->  Bitmap Index Scan on msgs_idx  (cost=0.00..2895.47 rows=10346
 width=0)
Index Cond: (to_tsvector('english'::regconfig, message) @@ to_tsquery('131.211.112.9'::text))
(7 rows)

works acceptable.
How to use the right plan regardless of the 'LIMIT-size'?
The planner obviously thinks it will have read 100 rows from systemevents backwards earlier than it actually does, with the where clause that contains the scanning for string 131.211.112.9. Increasing the stats target in this case will probably not help, since the statistics will not contain selectivity for all possible ts queries.

If the index is useless anyway, you might consider dropping it. Otherwise, increasing random_page_cost might help in choosing the otherplan, but on the other hand that plan has index scanning too, so I'm not to sure there.

If that doesn't help, it would be interesting to see some output of vmstat 1 (or better: iostat -xk 1) to see what is the bottleneck during execution of the first plan. If it is IO bound, you might want to increase RAM or add spindles for increased random io performance. If it is CPU bound, it is probably because of executing the to_tsvector function. In that case it might be interesting to see if changing ts_vectors cost (see ALTER FUNCTION ... COST .../ http://developer.postgresql.org/pgdocs/postgres/sql-alterfunction.html) again helps the planner to favor the second plan over the first.

regards,
Yeb Havinga


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux