From: Andrew Lazarus <andrew@pillette.com>
To: pgsql-performance@postgresql.org
Subject: Re: Subselect query enhancement
Date: Thu, 1 Feb 2007 14:37:35 -0800
Message-ID: <373981020.20070201143735@pillette.com> (raw)
In-Reply-To: <e9c163070702011014g7c90f0f3v8bff5fcb0de27f7@mail.gmail.com>
References: <e9c163070702010842ja639378v75e8c0eb757d9b72@mail.gmail.com>
<45C22353.4020902@blackducksoftware.com>
<e9c163070702011014g7c90f0f3v8bff5fcb0de27f7@mail.gmail.com>
>> How about this option:
>>
>> SELECT distinct ip_info.* FROM ip_info RIGHT JOIN network_events USING
>> (ip) RIGHT JOIN host_events USING (ip) WHERE
>> (network_events.name='blah' OR host_events.name = 'blah') AND
>> ip_info.ip IS NOT NULL;
MA> Nah, that seems to be much much worse. The other queries usually
MA> return in 1-2 minutes, this one has been running for 30 minutes and
MA> has still not returned
I find that an OR involving two different fields (in this case even
different tables) is faster when replaced by the equivalent UNION. In this
case---
SELECT distinct ip_info.* FROM ip_info RIGHT JOIN network_events USING
(ip) WHERE
network_events.name='blah' AND ip_info.ip IS NOT NULL
UNION
SELECT distinct ip_info.* FROM ip_info RIGHT JOIN host_events USING (ip) WHERE
host_events.name = 'blah' AND ip_info.ip IS NOT NULL;
Moreover, at least through 8.1, GROUP BY is faster than DISTINCT.
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: andrew@pillette.com
Subject: Re: Subselect query enhancement
In-Reply-To: <373981020.20070201143735@pillette.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