From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Neil Conway <neilc@samurai.com>
Cc: Markus Schaber <schabios@logi-track.com>
Cc: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: Multiple Uniques
Date: 10 Sep 2004 02:34:28 -0400
Message-ID: <877jr2tyx7.fsf@stark.xeocode.com> (raw)
In-Reply-To: <13334.1094785631@sss.pgh.pa.us>
References: <20040902154340.5b1a669a@kingfisher.intern.logi-track.com>
<19516.1094134957@sss.pgh.pa.us>
<4140E1EF.1040303@samurai.com>
<13334.1094785631@sss.pgh.pa.us>
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Neil Conway <neilc@samurai.com> writes:
>
> > How many cycles are we really talking about, though? I have a patch
> > which I'll send along in a few days which implements a similar
> > optimization: if a subselect is referenced by EXISTS or IN, we can
> > discard DISTINCT and ORDER BY clauses in the subquery
>
> I don't think either of those is worth doing. ORDER BY in a sub-select
> isn't even legal SQL, much less probable, so why should we expend even
> a nanosecond to optimize it? The DISTINCT is more of a judgement call,
> but my thought when I looked at it originally is that it would give
> people a possible optimization knob. If you write DISTINCT in an IN
> clause then you can get a different plan (the IN reduces to an ordinary
> join) that might or might not be better than without it. We shouldn't
> take away that possibility just on the grounds of nanny-ism.
Just one user's 2c: Consider the plight of dynamically constructed queries.
The queries within "IN" clauses are particularly likely to be constructed this
way. The query in the IN clause could be a constructed in an entirely separate
function without any idea that it will be used within an IN clause.
E.g. something like:
$accessible_ids = $security_manager->get_accessible_ids_query($this->userid);
$selected_columns = $this->selected_columns_parameters();
$query = "select $selected_columns where id IN ($accessible_ids)"
In an ideal world functionally equivalent queries should always generate
identical plans. Of course there are limitations, it's not an ideal world, but
as much as possible it should be possible to write code without having to
worry whether the optimizer will be able to figure it out.
--
greg
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: gsstark@mit.edu, tgl@sss.pgh.pa.us, neilc@samurai.com, schabios@logi-track.com
Subject: Re: Multiple Uniques
In-Reply-To: <877jr2tyx7.fsf@stark.xeocode.com>
* 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