planner row-estimates for tsvector seems horribly wrong

From: Sushant Sinha <sushant354(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: planner row-estimates for tsvector seems horribly wrong
Date: 2010-10-24 12:44:21
Message-ID: 1287924261.4758.12.camel@yoffice
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I am using gin index on a tsvector and doing basic search. I see the
row-estimate of the planner to be horribly wrong. It is returning
row-estimate as 4843 for all queries whether it matches zero rows, a
medium number of rows (88,000) or a large number of rows (726,000).

The table has roughly a million docs.

I see a similar problem reported here but thought it was fixed in 9.0
which I am running.

http://archives.postgresql.org/pgsql-hackers/2010-05/msg01389.php

Here is the version info and detailed planner output for all the three
queries:

select version();

version

PostgreSQL 9.0.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(Gentoo 4.3.4 p1.1, pie-10.1.5) 4.3.4, 64-bit

Case I: FOR A NON-MATCHING WORD
===============================

explain analyze select count(*) from docmeta,
plainto_tsquery('english', 'dyfdfdf') as qdoc where docvector @@ qdoc;
QUERY
PLAN

Aggregate (cost=20322.17..20322.18 rows=1 width=0) (actual
time=0.058..0.058 rows=1 loops=1)
-> Nested Loop (cost=5300.28..20310.06 rows=4843 width=0) (actual
time=0.055..0.055 rows=0 loops=1)
-> Function Scan on qdoc (cost=0.00..0.01 rows=1 width=32)
(actual time=0.005..0.005 rows=1 loops=1)
-> Bitmap Heap Scan on docmeta (cost=5300.28..20249.51
rows=4843 width=270) (actual time=0.046..0.046 rows=0 loops=1)
Recheck Cond: (docmeta.docvector @@ qdoc.qdoc)
-> Bitmap Index Scan on doc_index (cost=0.00..5299.07
rows=4843 width=0) (actual time=0.044..0.044 rows=0 loops=1)
Index Cond: (docmeta.docvector @@ qdoc.qdoc)
Total runtime: 0.092 ms

CASE II: FOR A MEDIUM-MATCHING WORD
===================================
explain analyze select count(*) from docmeta,
plainto_tsquery('english', 'quit') as qdoc where docvector @@ qdoc;
QUERY
PLAN

Aggregate (cost=20322.17..20322.18 rows=1 width=0) (actual
time=1222.856..1222.857 rows=1 loops=1)
-> Nested Loop (cost=5300.28..20310.06 rows=4843 width=0) (actual
time=639.275..1212.460 rows=88545 loops=1)
-> Function Scan on qdoc (cost=0.00..0.01 rows=1 width=32)
(actual time=0.006..0.007 rows=1 loops=1)
-> Bitmap Heap Scan on docmeta (cost=5300.28..20249.51
rows=4843 width=270) (actual time=639.264..1196.542 rows=88545 loops=1)
Recheck Cond: (docmeta.docvector @@ qdoc.qdoc)
-> Bitmap Index Scan on doc_index (cost=0.00..5299.07
rows=4843 width=0) (actual time=621.877..621.877 rows=88545 loops=1)
Index Cond: (docmeta.docvector @@ qdoc.qdoc)
Total runtime: 1222.907 ms

Case II: FOR A HIGH-MATCHING WORD
=================================

explain analyze select count(*) from docmeta,
plainto_tsquery('english', 'j') as qdoc where docvector @@ qdoc;
QUERY
PLAN

Aggregate (cost=20322.17..20322.18 rows=1 width=0) (actual
time=742.857..742.858 rows=1 loops=1)
-> Nested Loop (cost=5300.28..20310.06 rows=4843 width=0) (actual
time=126.804..660.895 rows=726985 loops=1)
-> Function Scan on qdoc (cost=0.00..0.01 rows=1 width=32)
(actual time=0.004..0.006 rows=1 loops=1)
-> Bitmap Heap Scan on docmeta (cost=5300.28..20249.51
rows=4843 width=270) (actual time=126.795..530.422 rows=726985 loops=1)
Recheck Cond: (docmeta.docvector @@ qdoc.qdoc)
-> Bitmap Index Scan on doc_index (cost=0.00..5299.07
rows=4843 width=0) (actual time=113.742..113.742 rows=726985 loops=1)
Index Cond: (docmeta.docvector @@ qdoc.qdoc)
Total runtime: 742.906 ms

Thanks,
Sushant.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jan Urbański 2010-10-24 12:57:47 Re: planner row-estimates for tsvector seems horribly wrong
Previous Message Robert Haas 2010-10-24 12:16:38 Re: knngist - 0.8