pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy isn't this index being used?
4+ messages / 3 participants
[nested] [flat]
* Why isn't this index being used?
@ 2004-10-19 15:14 Knutsen, Mark <Mark.Knutsen@nasdaq.com>
2004-10-19 15:28 ` Re: Why isn't this index being used? Doug Y <dylists@ptd.net>
0 siblings, 1 reply; 4+ messages in thread
From: Knutsen, Mark @ 2004-10-19 15:14 UTC (permalink / raw)
To: 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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why isn't this index being used?
2004-10-19 15:14 Why isn't this index being used? Knutsen, Mark <Mark.Knutsen@nasdaq.com>
@ 2004-10-19 15:28 ` Doug Y <dylists@ptd.net>
0 siblings, 0 replies; 4+ messages in thread
From: Doug Y @ 2004-10-19 15:28 UTC (permalink / raw)
To: Knutsen, Mark <Mark.Knutsen@nasdaq.com>; +Cc: pgsql-performance
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)
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why isn't this index being used?
@ 2004-10-19 15:33 Knutsen, Mark <Mark.Knutsen@nasdaq.com>
2004-10-19 15:47 ` Re: Why isn't this index being used? Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 4+ messages in thread
From: Knutsen, Mark @ 2004-10-19 15:33 UTC (permalink / raw)
To: Doug Y <dylists@ptd.net>; +Cc: 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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why isn't this index being used?
2004-10-19 15:33 Re: Why isn't this index being used? Knutsen, Mark <Mark.Knutsen@nasdaq.com>
@ 2004-10-19 15:47 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2004-10-19 15:47 UTC (permalink / raw)
To: pgsql-performance
On Tue, Oct 19, 2004 at 11:33:50AM -0400, Knutsen, Mark wrote:
> (Why don't replies automatically go to the list?)
Because sometimes you don't want them to. There's been dozens of
discussions about this. BTW, mutt has a nice feature which allows
you to reply to lists -- I imagine other MUAs have such a feature
too.
> Sure enough, quoting the constants fixes the problem.
>
> Is it a best practice to always quote constants?
No, but it's very useful in these cases. The problem is I believe
this is fixed in 8.0, BTW. See the FAQ, question 4.8
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-10-19 15:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-19 15:14 Why isn't this index being used? Knutsen, Mark <Mark.Knutsen@nasdaq.com>
2004-10-19 15:28 ` Doug Y <dylists@ptd.net>
2004-10-19 15:33 Re: Why isn't this index being used? Knutsen, Mark <Mark.Knutsen@nasdaq.com>
2004-10-19 15:47 ` Andrew Sullivan <ajs@crankycanuck.ca>
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