pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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)



Message-ID: <19991108181946.A6730@oskar>
Permalink:  ../19991108181946.A6730@oskar/
Also on:    postgresql.org/message-id/19991108181946.A6730@oskar

 · 

reply

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