Received: from mail.iconz.co.nz (oldmail.iconz.co.nz [202.14.100.36]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA53891 for ; Tue, 30 Nov 1999 18:16:17 -0500 (EST) (envelope-from markir@hnz.co.nz) Received: from [192.173.20.11] (mail.hnz.co.nz [192.173.20.11]) by mail.iconz.co.nz (8.9.3/8.9.3) with SMTP id MAA277200944003808 for ; Wed, 1 Dec 1999 12:16:48 +1300 (NZDT) Received: from hiscorpxy01.hnz.co.nz by [192.173.20.11] via smtpd (for mail.iconz.co.nz [210.48.22.36]) with SMTP; 30 Nov 1999 23:11:59 UT Received: FROM hufin0.hnz.co.nz BY hiscorpxy01.hnz.co.nz ; Wed Dec 01 12:14:18 1999 +1300 Received: from hudev0.hnz.co.nz (hudev0 [10.24.10.46]) by hufin0.hnz.co.nz with ESMTP (8.8.6 (PHNE_14041)/8.7.1) id MAA03036 for ; Wed, 1 Dec 1999 12:15:32 +1300 (NZDT) Received: (from markir@localhost) by hudev0.hnz.co.nz (8.7.6/8.7.1) id MAA05232 for pgsql-sql@postgresql.org; Wed, 1 Dec 1999 12:14:41 +1300 (NZDT) Date: Wed, 1 Dec 1999 12:14:41 +1300 (NZDT) From: Mark Kirkwood Message-Id: <199911302314.MAA05232@hudev0.hnz.co.nz> subject: slow sort for simple join To: undisclosed-recipients:; 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