From: Tom Lane <tgl@sss.pgh.pa.us>
To: Yaocl <chunlinyao@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: A query become very slow after upgrade from 8.1.10 to 8.4.5
Date: Tue, 02 Nov 2010 18:30:08 -0400
Message-ID: <17301.1288737008@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTi=HfppUjzNUhajtBE6u42DHzvgsEPz2bXgKBRx8@mail.gmail.com>
References: <AANLkTi=HfppUjzNUhajtBE6u42DHzvgsEPz2bXgKBRx8@mail.gmail.com>
Yaocl <chunlinyao@gmail.com> writes:
> SELECT t_a.id FROM t_a
> WHERE EXISTS ( SELECT t_b.id FROM t_b, t_c
> WHERE t_b.id = t_a.id AND t_c.flag = 'f')
I have some hopes for fixing this in 9.1, but nothing is going to happen
in 8.4 or 9.0. In the meantime, is it intentional that there is no join
clause between t_b and t_c? That'd be a lot more efficient as two
separate EXISTS tests, ie
WHERE EXISTS ( SELECT 1 FROM t_b WHERE t_b.id = t_a.id ) AND
EXISTS ( SELECT 1 FROM t_c WHERE t_c.flag = 'f')
but I wonder whether this query doesn't simply reflect a logic error on
the client side.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, chunlinyao@gmail.com
Subject: Re: A query become very slow after upgrade from 8.1.10 to 8.4.5
In-Reply-To: <17301.1288737008@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox