Wrong rows count estimation (explain, gist, tsearch)

From: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Wrong rows count estimation (explain, gist, tsearch)
Date: 2009-09-28 07:09:55
Message-ID: c3a7de1f0909280009h2a6c6846q8d1726b36e6ad007@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi, community

I have a table containing column for FTS and an appropriate index:

zzz=# \d search_table
...
obj_tsvector | tsvector |
not null default ''::tsvector
...
"i_search_table__tsvector_1" gist (obj_tsvector) WHERE obj_status_did = 1

The table filled with about 7.5E+6 rows. Most of them have different
from default values in obj_tsvector column. I use "estimated rows
count trick" to make search results counter faster, and every time
when obj_tsvector is used estimation rows count is extremely differ
from actual (eg. 6821 vs 372012). I played with SET STATISTICS but
have no success.

zzz=# EXPLAIN ANALYZE SELECT count(1) FROM search_table WHERE
obj_status_did = 1 AND obj_tsvector @@ (make_tsquery('(музыка)',
'utf8_russian'));

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=25226.63..25226.64 rows=1 width=0) (actual
time=14832.455..14832.455 rows=1 loops=1)
-> Bitmap Heap Scan on search_table (cost=465.16..25209.57
rows=6821 width=0) (actual time=3202.390..14731.096 rows=371026
loops=1)
Recheck Cond: (obj_status_did = 1)
Filter: (obj_tsvector @@ '''музыка'''::tsquery)
-> Bitmap Index Scan on i_search_table__tsvector_1
(cost=0.00..463.45 rows=6821 width=0) (actual time=2919.257..2919.257
rows=372012 loops=1)
Index Cond: (obj_tsvector @@ '''музыка'''::tsquery)
Total runtime: 14832.555 ms
(7 rows)

PG version - 8.3.7, STATISTICS is set to 500 for the column.

What's wrong with it? Is it possible to solve the problem? Thanx.

--
Regards,
Sergey Konoplev

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sergey Konoplev 2009-09-28 07:12:08 Re: Wrong rows count estimation (explain, gist, tsearch)
Previous Message Nico Callewaert 2009-09-28 06:53:43 Re: Delphi connection ?