pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Markus Schaber <schabios@logi-track.com>
To: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Multiple Uniques
Date: Thu, 2 Sep 2004 15:43:40 +0200
Message-ID: <20040902154340.5b1a669a@kingfisher.intern.logi-track.com> (raw)

Hello,

Today, we stumbled about the following query plan on PostGreSQL 7.4.1:

logigis=# explain select count(id) from (select distinct id from (select distinct ref_in_id as id from streets union select distinct nref_in_id as id from streets) as blubb) as blabb; 
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=33246086.32..33246086.32 rows=1 width=8)
   ->  Subquery Scan blabb  (cost=32970061.50..33246085.82 rows=200 width=8)
         ->  Unique  (cost=32970061.50..33246083.82 rows=200 width=8)
               ->  Sort  (cost=32970061.50..33108072.66 rows=55204464 width=8)
                     Sort Key: id
                     ->  Subquery Scan blubb  (cost=23697404.79..24525471.75 rows=55204464 width=8)
                           ->  Unique  (cost=23697404.79..23973427.11 rows=55204464 width=8)
                                 ->  Sort  (cost=23697404.79..23835415.95 rows=55204464 width=8)
                                       Sort Key: id
                                       ->  Append  (cost=7212374.04..15252815.03 rows=55204464 width=8)
                                             ->  Subquery Scan "*SELECT* 1"  (cost=7212374.04..7626407.52 rows=27602232 width=8)
                                                   ->  Unique  (cost=7212374.04..7350385.20 rows=27602232 width=8)
                                                         ->  Sort  (cost=7212374.04..7281379.62 rows=27602232 width=8)
                                                               Sort Key: ref_in_id
                                                               ->  Seq Scan on streets  (cost=0.00..3129090.32 rows=27602232 width=8)
                                             ->  Subquery Scan "*SELECT* 2"  (cost=7212374.04..7626407.52 rows=27602232 width=8)
                                                   ->  Unique  (cost=7212374.04..7350385.20 rows=27602232 width=8)
                                                         ->  Sort  (cost=7212374.04..7281379.62 rows=27602232 width=8)
                                                               Sort Key: nref_in_id
                                                               ->  Seq Scan on streets  (cost=0.00..3129090.32 rows=27602232 width=8)
(20 rows)

I might have to add that this query is not an every-day query, it's
merely part of some statistics that a colleague needs for some
estimations as he has to create a tool that works on this data.

Both ref_in_id and nref_in_id are non-indexed columns with type int8.

I was somehow irritated by the fact that the Query Plan contains 4 Uniques.

Then, I tried the following query:

logigis=# explain select count(id) from (select distinct id from (select  ref_in_id as id from streets union select  nref_in_id as id from streets) as blubb) as blabb;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=24803496.57..24803496.57 rows=1 width=8)
   ->  Subquery Scan blabb  (cost=24527471.75..24803496.07 rows=200 width=8)
         ->  Unique  (cost=24527471.75..24803494.07 rows=200 width=8)
               ->  Sort  (cost=24527471.75..24665482.91 rows=55204464 width=8)
                     Sort Key: id
                     ->  Subquery Scan blubb  (cost=15254815.03..16082881.99 rows=55204464 width=8)
                           ->  Unique  (cost=15254815.03..15530837.35 rows=55204464 width=8)
                                 ->  Sort  (cost=15254815.03..15392826.19 rows=55204464 width=8)
                                       Sort Key: id
                                       ->  Append  (cost=0.00..6810225.28 rows=55204464 width=8)
                                             ->  Subquery Scan "*SELECT* 1"  (cost=0.00..3405112.64 rows=27602232 width=8)
                                                   ->  Seq Scan on streets  (cost=0.00..3129090.32 rows=27602232 width=8)
                                             ->  Subquery Scan "*SELECT* 2"  (cost=0.00..3405112.64 rows=27602232 width=8)
                                                   ->  Seq Scan on streets  (cost=0.00..3129090.32 rows=27602232 width=8)
(14 rows)

And after re-parsing the documentation about UNION, the following one:

logigis=# explain select count(id) from (select  ref_in_id as id from streets union select  nref_in_id as id from streets) as blubb;
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16220893.16..16220893.16 rows=1 width=8)
   ->  Subquery Scan blubb  (cost=15254815.03..16082881.99 rows=55204464 width=8)
         ->  Unique  (cost=15254815.03..15530837.35 rows=55204464 width=8)
               ->  Sort  (cost=15254815.03..15392826.19 rows=55204464 width=8)
                     Sort Key: id
                     ->  Append  (cost=0.00..6810225.28 rows=55204464 width=8)
                           ->  Subquery Scan "*SELECT* 1"  (cost=0.00..3405112.64 rows=27602232 width=8)
                                 ->  Seq Scan on streets  (cost=0.00..3129090.32 rows=27602232 width=8)
                           ->  Subquery Scan "*SELECT* 2"  (cost=0.00..3405112.64 rows=27602232 width=8)
                                 ->  Seq Scan on streets  (cost=0.00..3129090.32 rows=27602232 width=8)
(10 rows)

Those queries should give the same result.

So, now my question is, why does the query optimizer not recognize that
it can throw away those "non-unique" Sort/Unique passes?

Is PostGreSQL 8 capable of this optimization?


Thanks,
Markus


-- 
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com



view thread (7+ messages)  latest in thread

Message-ID: <20040902154340.5b1a669a@kingfisher.intern.logi-track.com>
Permalink:  ../20040902154340.5b1a669a@kingfisher.intern.logi-track.com/
Also on:    postgresql.org/message-id/20040902154340.5b1a669a@kingfisher.intern.logi-track.com

 · 

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-performance@postgresql.org
  Cc: schabios@logi-track.com
  Subject: Re: Multiple Uniques
  In-Reply-To: <20040902154340.5b1a669a@kingfisher.intern.logi-track.com>
  References: <20040902154340.5b1a669a@kingfisher.intern.logi-track.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