Re: Queryplan within FTS/GIN index -search.

From: Richard Huxton <dev(at)archonet(dot)com>
To: jesper(at)krogh(dot)cc
Cc: Jeff Davis <pgsql(at)j-davis(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Queryplan within FTS/GIN index -search.
Date: 2009-10-23 08:26:26
Message-ID: 4AE168B2.2020606@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

jesper(at)krogh(dot)cc wrote:
>
> So getting them with AND inbetween gives x100 better performance. All
> queries are run on "hot disk" repeated 3-5 times and the number are from
> the last run, so disk-read effects should be filtered away.
>
> Shouldn't it somehow just do what it allready are capable of doing?

I'm guessing to_tsquery(...) will produce a tree of search terms (since
it allows for quite complex expressions). Presumably there's a standard
order it gets processed in too, so it should be possible to generate a
more or less efficient ordering.

That structure isn't exposed to the planner though, so it doesn't
benefit from any re-ordering the planner would normally do for normal
(exposed) AND/OR clauses.

Now, to_tsquery() can't re-order the search terms because it doesn't
know what column it's being compared against. In fact, it might not be a
simple column at all.

So - there would either need to be:
1. Some hooks from the planner to reach into the tsquery datatype.
2. A variant to_tsquery_with_sorting() which would take the column-name
or something and look up the stats to work against.

#1 is the better solution, but #2 might well be simpler to implement as
a work-around for now.
--
Richard Huxton
Archonet Ltd

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message jesper 2009-10-23 09:04:22 Re: Queryplan within FTS/GIN index -search.
Previous Message jesper 2009-10-23 07:45:44 Re: Queryplan within FTS/GIN index -search.