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 452A45E46CD for ; Fri, 27 Aug 2004 14:12:01 -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 80886-07 for ; Fri, 27 Aug 2004 17:11:54 +0000 (GMT) Received: from mail.guest-tek.com (unknown [209.82.99.131]) by svr1.postgresql.org (Postfix) with ESMTP id 911535E46E7 for ; Fri, 27 Aug 2004 14:11:53 -0300 (ADT) Received: from TSDJKERKHOF ([209.82.99.130]) by mail.guest-tek.com (8.11.6/8.11.6) with SMTP id i7RHDJA06741 for ; Fri, 27 Aug 2004 11:13:20 -0600 From: "Jack Kerkhof" To: Subject: Why does a simple query not use an obvious index? Date: Fri, 27 Aug 2004 11:12:13 -0600 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0020_01C48C26.B452D5F0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_FONTCOLOR_BLUE, HTML_FONTCOLOR_GREEN, HTML_FONTCOLOR_RED, HTML_FONTCOLOR_UNSAFE, HTML_MESSAGE, HTML_TAG_EXISTS_TBODY X-Spam-Level: X-Archive-Number: 200408/421 X-Sequence-Number: 8057 This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C48C26.B452D5F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit The query: select count(*) from billing where timestamp > now()-60 should obviously use the index CREATE INDEX billing_timestamp_idx ON billing USING btree ("timestamp" timestamp_ops); on a table with 1400000 rows. But it uses a Seq Scan. If I set enable_seqscan=no, it indicates a queryplan could not be calculated. Why does this simple query not use the timestamp index, and how can I get it to? Thanks, Jack Jack Kerkhof Research & Development jack.kerkhof@guest-tek.com www.guest-tek.com 1.866.509.1010 3480 -------------------------------------------------------------------------- Guest-Tek is a leading provider of broadband technology solutions for the hospitality industry. Guest-Tek's GlobalSuite™ high-speed Internet solution enables hotels to offer their guests the convenience of wired and/or wireless broadband Internet access from guest rooms, meeting rooms and public areas. ------=_NextPart_000_0020_01C48C26.B452D5F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
The=20 query:

    select count(*) from billing where timestamp &g= t;=20 now()-60

should obvi= ously use=20 the index

    CREATE INDEX billing_timestamp_idx ON billing USING btree = ("timestamp" timestamp_ops);

on a table = with=20 1400000 rows.

But it uses= a Seq=20 Scan. If I set enable_seqscan=3Dno, it indicates a queryplan could not be= =20 calculated.

Why does this simple query not use the timestamp index, and how ca= n I get=20 it to?

Thanks, Jack

Jack=20 Kerkhof
Research & Development
jack.kerkhof@guest-tek.com
www.guest-tek.com
1.866.509= .1010=20 3480

Guest-Tek is a = leading=20 provider of broadband technology solutions for the hospitality indust= ry.=20 Guest-Tek's GlobalSuite=99 high-speed Internet solution enables hotel= s to=20 offer their guests the convenience of wired and/or wireless broadband= =20 Internet access from guest rooms, meeting rooms and public areas.=20

 
------=_NextPart_000_0020_01C48C26.B452D5F0--