X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0265BD1B187 for ; Tue, 29 Jun 2004 03:41:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50734-06 for ; Tue, 29 Jun 2004 06:41:33 +0000 (GMT) Received: from pc7.berlin.powerweb.de (pc7.berlin.powerweb.de [62.67.228.13]) by svr1.postgresql.org (Postfix) with ESMTP id 16BD7D1B1C8 for ; Tue, 29 Jun 2004 03:41:26 -0300 (ADT) Received: from spock (p508409B6.dip0.t-ipconnect.de [80.132.9.182]) by pc7.berlin.powerweb.de (8.9.3p3/8.9.3) with SMTP id IAA09141 for ; Tue, 29 Jun 2004 08:41:23 +0200 Message-ID: <007d01c45da4$3726dcd0$6602a8c0@spock> From: "Harald Lau (Sector-X)" To: Subject: no index-usage on aggregate-functions? Date: Tue, 29 Jun 2004 08:42:14 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=RCVD_IN_NJABL, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/281 X-Sequence-Number: 7339 Hi, I've experienced that PG up to current release does not make use of an inde= x when aggregating. Which of course may result in unacceptable answering ti= mes This behaviour is reproducable on any table with any aggregat function in a= ll of my databases on every machine (PostgreSQL 7.4.2 on i386-redhat-linux-= gnu and PostgreSQL 7.2.1 on i686-pc-linux-gnu) f.e. querying against a 2.8-mio-records (2.800.000) table the_table SELECT count(*) FROM the_table =3D> Seq scan -> takes about 12 sec SELECT Avg(num_found) AS NumFound FROM the_table --(index on num_found) =3D> Seq scan -> takes about 10 sec SELECT Sum(num_found) AS TotalFound FROM the_table --(index on num_found) =3D> Seq scan -> takes about 11 sec SELECT Max(date_) AS LatestDate FROM the_table --(index on date_) =3D> Seq scan -> takes about 14 sec But SELECT date_ AS LatestDate FROM the_table ORDER BY date_ DESC LIMIT 1; =3D> Index scan -> takes 0.18 msec MS SQLServer 2000: Use of an appropriate index _whenever_ aggregating. Am I doing something wrong? Greetings Harald