Re: Query optimization problem

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Zotov <zotov(at)oe-it(dot)ru>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Query optimization problem
Date: 2010-07-20 14:31:01
Message-ID: AANLkTi=S6GjWxeSnoHOeL4ciBA2LcbR6eEZAyEUMFGLM@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Jul 20, 2010 at 1:57 AM, Zotov <zotov(at)oe-it(dot)ru> wrote:
> i wrote to
>   pgsql-bugs(at)postgresql(dot)org
> they tell me write to
>   pgsql-performance(at)postgresql(dot)org
> they tell me write here
>
> I don`t whant know how optimize query myself (i know it), and i think it
> must do planner.

According to the EXPLAIN ANALYZE output, your "slow" query is
executing in 0.007 ms, and your "fast" query is executing in 0.026 ms
(i.e. not as quickly as the slow query). Since you mention that it
takes 7 s further down, I suspect this is not the real EXPLAIN ANALYZE
output on the real data that you're having a problem with. You might
have better luck if you post the actual EXPLAIN ANALYZE output here.
Incidentally, sorry for not responding sooner to your private email -
I was on vacation last week. But please do keep all replies on-list
so that everyone can comment.

All that having been said, I think the issue here is that the query
planner isn't inferring that d1.ID=<some constant> implies d2.ID=<some
constant>, even though there's a join clause d1.ID=d2.ID. I'm not
really sure why it isn't doing that... I suspect Tom Lane is the only
person who can comment intelligently on that, and he's away this week
(but if anyone else has an idea, feel free to jump in...).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Matthew Wakeling 2010-07-20 14:55:18 Re: Trouble with COPY IN
Previous Message Robert Haas 2010-07-20 14:22:23 Re: Query results differ depending on operating system (using GIN)