pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Doug Y <dylists@ptd.net>
To: Knutsen, Mark <Mark.Knutsen@nasdaq.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why isn't this index being used?
Date: Tue, 19 Oct 2004 11:28:16 -0400
Message-ID: <41753290.8010709@ptd.net> (raw)
In-Reply-To: <C6317ED2939D684C9FBE85D574CC5E6208969499@mer-exch1.corp.nasdaq.com>
References: <C6317ED2939D684C9FBE85D574CC5E6208969499@mer-exch1.corp.nasdaq.com>

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)
>




view thread (4+ messages)  latest in thread

Message-ID: <41753290.8010709@ptd.net>
Permalink:  ../41753290.8010709@ptd.net/
Also on:    postgresql.org/message-id/41753290.8010709@ptd.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dylists@ptd.net, Mark.Knutsen@nasdaq.com
  Subject: Re: Why isn't this index being used?
  In-Reply-To: <41753290.8010709@ptd.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox