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

Re: Queryplan within FTS/GIN index -search.

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: jesper(at)krogh(dot)cc
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Queryplan within FTS/GIN index -search.
Date: 2009-10-23 16:01:02
Message-ID: 1256313662.2580.810.camel@jdavis (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 2009-10-23 at 09:45 +0200, jesper(at)krogh(dot)cc wrote:
> No, it definately has to go visit the index/table to confirm findings, but
> that why I wrote Queryplan in the subject line, because this os only about
> the strategy to pursue to obtain the results. And a strategy about
> limiting the amout of results as early as possible (as PG usually does)
> would be what I'd expect and MCV can help it guess on that.

I see what you're saying: you could still index the common terms like
normal, but just not look for anything in the index if it's an MCV. That
sounds reasonable, based on the numbers you provided.

>                Index Cond: (ftsbody_body_fts @@ to_tsquery('commonterm &
> spellerror'::text))
>  Total runtime: 862.771 ms
> (6 rows)

...

>                Index Cond: ((ftsbody_body_fts @@
> to_tsquery('commonterm'::text)) AND (ftsbody_body_fts @@
> to_tsquery('spellerror'::text)))
>  Total runtime: 8.724 ms
> (6 rows)
> 

Something seems strange here. Both are a single index scan, but having a
single complex search key is worse than having two simple search keys. 

Perhaps the real problem is that there's a difference between these
cases at all? I don't see any reason why the first should be more
expensive than the second.

Regards,
	Jeff Davis


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-10-23 16:20:18
Subject: Re: query planning different in plpgsql?
Previous:From: Grzegorz JaƛkiewiczDate: 2009-10-23 15:56:36
Subject: Re: query planning different in plpgsql?

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