Re: Why isn't this index being used? - Mailing list pgsql-performance

(Why don't replies automatically go to the list?)

Sure enough, quoting the constants fixes the problem.

Is it a best practice to always quote constants?

> -----Original Message-----
> From: Doug Y [mailto:dylists@ptd.net]
> Sent: Tuesday, October 19, 2004 11:28 AM
> To: Knutsen, Mark
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Why isn't this index being used?
>
> 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)