Skip site navigation (1) Skip section navigation (2)

Re: Full text search with ORDER BY performance issue

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>, Krade <krade(at)krade(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Full text search with ORDER BY performance issue
Date: 2009-07-29 15:13:34
Message-ID: 603c8f070907290813h1e814784t482ffb394a70d36a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Jul 29, 2009 at 10:22 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> If love is an uncommon word, there's no help for queries of this type
>> being slow unless the GIN index can return the results in order.  But
>> if love is a common word, then it would be faster to do an index scan
>> by timestamp on the baserel and then treat comment_tsv @@
>> plainto_tsquery('love') as a filter condition.  Is this a selectivity
>> estimation bug?
>
> Doesn't look like it: estimated number of matches is 253635, actual is
> 259828, which is really astonishingly close considering what we have to
> work with.  It's not clear though what fraction of the total that
> represents.

Hmm, good point.  It seems like it would be useful to force the
planner into use the other plan and get EXPLAIN ANALYZE output for
that for comparison purposes, but off the top of my head I don't know
how to do that.

...Robert

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-07-29 15:29:46
Subject: Re: Full text search with ORDER BY performance issue
Previous:From: Tom LaneDate: 2009-07-29 14:22:24
Subject: Re: Full text search with ORDER BY performance issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group