From: eric soroos <eric-psql@soroos.net>
To: pgsql-sql@postgresql.org
Subject: Re: Query for filtering records
Date: Tue, 03 Dec 2002 12:52:04 -0800
Message-ID: <57416401.1173202572@[4.42.179.151]> (raw)
In-Reply-To: <20021203194728.GA917@temp.joelburton.com>
> SELECT * ...
> FROM ...
> WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or
> groupnum='d' OR ... )
>
> is bound to be _much_ faster!
Yeah, that's an obvious optimization. Unfortunately, due to needing to match semantics of a previous non-sql version and some pathological group specifications from clients, it can't be applied as often as I'd like. Yes, I call client specs pathological sometimes. No, they don't know that.
> And even better is
>
> SELECT *
> FROM ... contacts c1
> WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or
> groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum)
>
>
> EXISTS is almost always faster in PG.
Exists seems to be the answer. It even gives the right answer, which has been a problem for queries of this sort.
Rewriting the queries so that the subtraction clauses use exists are giving me reasonable runtimes (~5 sec) and believable answers. (instead of the other two extremes of 5 minutes and either no answers or everyone in the database) What's useful here is that I'm getting multiple exists index scans instead of nested loops or table scans. What's more, exists clauses are really easy to integrate into my query generation routine.
thanks
eric
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-sql@postgresql.org
Cc: eric-psql@soroos.net
Subject: Re: Query for filtering records
In-Reply-To: <57416401.1173202572@[4.42.179.151]>
* 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