From: Oskar Liljeblad <osk@hem.passagen.se>
To: pgsql-sql@postgresql.org
Subject: about the subselect query
Date: Mon, 8 Nov 1999 18:19:46 +0100
Message-ID: <19991108181946.A6730@oskar> (raw)
Thanks to everyone who replied to my email about a
subselect query that was rather slow (1-3mins). The query is:
SELECT *
FROM items
WHERE package IN
(SELECT package
FROM items
WHERE ...user search expression...
GROUP BY package)
The fastest query I could come up with that is equivalent
to the one above is
SELECT DISTINCT i1.*
FROM items i1, items i2
WHERE i1.package = i2.package
AND ...user search expression...
which finishes in about 3s. EXPLAIN gives the following info on
the query (if "search expression" is a regexp on non-indexed
column i1.performer):
Unique (cost=1334.61 rows=2 width=151)
-> Sort (cost=1334.61 rows=2 width=151)
-> Nested Loop (cost=1334.61 rows=2 width=151)
-> Seq Scan on items i2 (cost=1332.56 rows=1 width=12)
-> Index Scan using items_packages on items i1
(cost=2.05 rows=12805 width=139)
But it is still faster if I run N+1 queries - first one SELECT
query to list packages, then another SELECT for each row in
the result.
Oskar Liljeblad (osk@hem.passagen.se)
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: osk@hem.passagen.se
Subject: Re: about the subselect query
In-Reply-To: <19991108181946.A6730@oskar>
References: <19991108181946.A6730@oskar>
* 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