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

Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Bryce Nesbitt <bryce2(at)obviously(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
Date: 2010-02-15 16:41:43
Message-ID: 603c8f071002150841w53cda2f6l7901e7af06da862@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, Feb 13, 2010 at 2:58 AM, Bryce Nesbitt <bryce2(at)obviously(dot)com> wrote:
> So as the op, back to the original posting....
>
> In the real world, what should I do?  Does it make sense to pull the "AND
> articles.indexed" clause into an outer query?  Will that query simply
> perform poorly on other arbitrary combinations of words?

It's really hard to say whether a query that performs better is going
to always perform better on every combination of words.  My suggestion
is - try it and see.  It's my experience that rewriting queries is a
pretty effective way of speeding them up, but I can't vouch for what
will happen in your particular case.  It's depressing to see that
increasing the statistics target didn't help much; but that makes me
think that the problem is that your join selectivity estimates are
off, as Tom and Jorge said upthread.  Rewriting the query or trying an
upgrade are probably your only options.

...Robert

In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2010-02-15 17:24:27
Subject: Re: Almost infinite query -> Different Query Plan when changing where clause value
Previous:From: Ben ChobotDate: 2010-02-15 16:16:04
Subject: Re: 8.1 -> 8.4 regression

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