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 63382329F08 for ; Tue, 19 Oct 2004 16:28:32 +0100 (BST) 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 29928-09 for ; Tue, 19 Oct 2004 15:28:22 +0000 (GMT) Received: from neomail10.traderonline.com (email.traderonline.com [65.213.231.10]) by svr1.postgresql.org (Postfix) with ESMTP id A8FC2329EC2 for ; Tue, 19 Oct 2004 16:28:21 +0100 (BST) Received: from [192.168.1.68] (64-139-89-109-ubr02b-epensb01-pa.hfc.comcastbusiness.net [64.139.89.109]) by neomail10.traderonline.com (8.12.10/8.12.8) with ESMTP id i9JFSGB1009630; Tue, 19 Oct 2004 11:28:17 -0400 Message-ID: <41753290.8010709@ptd.net> Date: Tue, 19 Oct 2004 11:28:16 -0400 From: Doug Y User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Knutsen, Mark" Cc: pgsql-performance@postgresql.org Subject: Re: Why isn't this index being used? References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/273 X-Sequence-Number: 8749 Hi, I ran into a similar problem using bigints... See: http://www.postgresql.org/docs/7.3/static/datatype.html#DATATYPE-INT small & big int have to be cast when used in querries... try: explain select * from db where type=90::smallint and subtype=70::smallint and date='7/1/2004'; or explain select * from db where type='90' and subtype='70' and date='7/1/2004'; Knutsen, Mark wrote: > The following is from a database of several hundred million rows of > real data that has been VACUUM ANALYZEd. > > > > Why isn't the index being used for a query that seems tailor-made for > it? The results (6,300 rows) take about ten minutes to retrieve with a > sequential scan. > > > > A copy of this database with "integer" in place of "smallint", a > primary key in column order (date, time, type, subtype) and a > secondary index in the required order (type, subtype, date, time) > correctly uses the secondary index to return results in under a second. > > > > Actually, the integer version is the first one I made, and the > smallint is the copy, but that shouldn't matter. > > > > Postgres is version "postgresql-server-7.3.4-3.rhl9" from Red Hat Linux 9. > > > > ===== > > > > testdb2=# \d db > > Table "public.db" > > Column | Type | Modifiers > > ---------+------------------------+----------- > > date | date | not null > > time | time without time zone | not null > > type | smallint | not null > > subtype | smallint | not null > > value | integer | > > Indexes: db_pkey primary key btree ("type", subtype, date, "time") > > > > testdb2=# set enable_seqscan to off; > > SET > > > > testdb2=# explain select * from db where type=90 and subtype=70 and > date='7/1/2004'; > > QUERY PLAN > > ------------------------------------------------------------------------------ > > Seq Scan on db (cost=100000000.00..107455603.76 rows=178 width=20) > > Filter: (("type" = 90) AND (subtype = 70) AND (date = > '2004-07-01'::date)) > > (2 rows) >