From: | "Knutsen, Mark" <Mark(dot)Knutsen(at)nasdaq(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Why isn't this index being used? |
Date: | 2004-10-19 15:14:55 |
Message-ID: | C6317ED2939D684C9FBE85D574CC5E6208969499@mer-exch1.corp.nasdaq.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Doug Y | 2004-10-19 15:28:16 | Re: Why isn't this index being used? |
Previous Message | Alban Medici (NetCentrex) | 2004-10-19 07:25:08 | Re: Queries slow using stored procedures |