pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mark Kirkwood <markir@hnz.co.nz>
Subject: slow sort for simple join
Date: Wed, 1 Dec 1999 12:14:41 +1300 (NZDT)
Message-ID: <199911302314.MAA05232@hudev0.hnz.co.nz> (raw)

Dear List,

( I had submitted this in pg-general but it is more appropriate here ... 
  - well, hopefully anyway)

I have been attempting to get this simple query to execute in less 
than 12 secs :

select d0.d0f1,
       count(f.f1)
from dim0 d0,
     fact0 f
where d0.d0key = f.d0key
and   d0.d0f1 between '1996-05-01' and '1996-05-31'
group by d0.d0f1 ;

this scans 100000 rows from fact0 ( 3000000 row table indexed on d0key)
and  31 rows from dim0 ( 900 rows  indexed on d0key as well ) 
- see my posting on "4 databases" in "pgsql-general" if you want more detail on the data 
in these tables....

It gives the result :

d0f1                         | count
-----------------------------+------
Thu May 30 00:00:00 1996 NZST|100000

( Note that only 1 of the 31 dim0 rows actually have corrosponding fact0 ones )

The query plan for this guy is :

Aggregate  (cost=134804.38 rows=2289334 width=20)
  ->  Group  (cost=134804.38 rows=2289334 width=20)
        ->  Sort  (cost=134804.38 rows=2289334 width=20)
              ->  Nested Loop  (cost=134804.38 rows=2289334 width=20)
                    ->  Seq Scan on dim0 d0  (cost=36.70 rows=101 width=12)
                    ->  Index Scan using fact0_q1 on fact0 f  (cost=1334.33 rows=3000000 width=8)


After some fooling about I tried this query :


select max(d0.d0f1),
       count(f.f1)
from dim0 d0,
     fact0 f
where d0.d0key = f.d0key
and   d0.d0f1 between '1996-05-01' and '1996-05-31' ;

which executes in 2 sec and gives the same result.

The query plan for this one is :

Aggregate  (cost=134804.38 rows=2289334 width=20)
  ->  Nested Loop  (cost=134804.38 rows=2289334 width=20)
        ->  Seq Scan on dim0 d0  (cost=36.70 rows=101 width=12)
        ->  Index Scan using fact0_q1 on fact0 f  (cost=1334.33 rows=3000000 width=8)


therefore nested loop evaluation of the original query probably takes about
2 secs, and it is the sort / group by that takes the remaining 10 secs.

Is this the expected level of performance for sort / group ?

I suspect that it is possible to perform this sort etc more quickly...
( in part because Oracle and Sqlserver can do this same query in 1-2 sec...
  and I dont really see why Postgresql needs to be slower )
		     

Cheers

Mark




Message-ID: <199911302314.MAA05232@hudev0.hnz.co.nz>
Permalink:  ../199911302314.MAA05232@hudev0.hnz.co.nz/
Also on:    postgresql.org/message-id/199911302314.MAA05232@hudev0.hnz.co.nz

 · 

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: markir@hnz.co.nz
  Subject: Re: slow sort for simple join
  In-Reply-To: <199911302314.MAA05232@hudev0.hnz.co.nz>
  References: <199911302314.MAA05232@hudev0.hnz.co.nz>

* 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