From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Josh Harrison <joshques(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Postgresql General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Recheck condition |
Date: | 2007-11-30 13:28:55 |
Message-ID: | 20071130132855.GE27784@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Nov 30, 2007 at 08:21:18AM -0500, Josh Harrison wrote:
> > > *Query1*
> > > SELECT person_id FROM person WHERE (column1=1 AND column2='62')
> > > INTERSECT
> > > SELECT person_id FROM person WHERE (column1=1 AND column2='189')
> I get the same plan(see below) with 'sort' for 'intersect all' operation
> too. Why is intersect not an effecient way? Is there any other way this
> query/index can be written/created so that I can get the intersect results
> in an efficient way?
Set operations are rather inefficient. To find the intersection of two
arbitrary sets you need to sort them and compare. A query like you
write would be better expressed as a join, something like:
SELECT a.person_id
FROM (SELECT person_id FROM person WHERE (column1=1 AND column2='62') a,
(SELECT person_id FROM person WHERE (column1=1 AND column2='189') b
WHERE a.person_id = b.person_id;
or perhaps:
SELECT a.person_id
FROM person a, person b
WHERE a.column1=1 AND a.column2='62'
AND b.column1=1 AND b.column2='189'
AND a.person_id = b.person_id;
Which will probably generate a merge join...
Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2007-11-30 13:33:23 | Re: PostgresSQL vs Ingress |
Previous Message | Peter Childs | 2007-11-30 13:25:46 | Re: PostgresSQL vs Ingress |