From: Timur Irmatov <thor@sarkor.com>
To: pgsql-performance@postgresql.org
Subject: index usage
Date: Fri, 17 Jan 2003 16:48:00 +0500
Message-ID: <10597430257.20030117164800@sarkor.com> (raw)
Hi, everyone!
I have a simple query which takes almost 3 seconds to complete, but
disabling sequence scans leads to a new plan using index. This second
plan takes less than 1 millisecond to run.
So, I'd like to hear any comments and suggestions.
Details.
CREATE TABLE MediumStats (
year SMALLINT NOT NULL,
month SMALLINT NOT NULL,
day SMALLINT NOT NULL,
hour SMALLINT NOT NULL,
--- and then goes few data fields
figureId INTEGER NOT NULL,
typeId INTEGER NOT NULL
PRIMARY KEY (figureId, typeId, year, month, day, hour)
);
CREATE FUNCTION indexHelper (INT2, INT2, INT2, INT2)
RETURNS CHARACTER(10) AS '
return sprintf("%d%02d%02d%02d", @_);
' LANGUAGE 'plperl' WITH (isCachable);
CREATE INDEX timeIndex ON MediumStats (indexHelper(year,month,day,hour));
and that is the query:
SELECT * FROM MediumStats
WHERE indexHelper(year,month,day,hour) < '2002121500'
LIMIT 1;
First, original plan:
Limit (cost=0.00..0.09 rows=1 width=22) (actual time=2969.30..2969.30 rows=0 loops=1)
-> Seq Scan on mediumstats (cost=0.00..1332.33 rows=15185 width=22) (actual time=2969.29..2969.29 rows=0 loops=1)
Total runtime: 2969.39 msec
Second plan, seq scans disabled:
Limit (cost=0.00..0.19 rows=1 width=6) (actual time=0.43..0.43 rows=0 loops=1)
-> Index Scan using timeindex on mediumstats (cost=0.00..2898.96 rows=15185 width=6) (actual time=0.42..0.42 rows=0 loops=1)
Total runtime: 0.54 msec
Table MediumStats currently has 45000 rows, all rows belong to this
month.
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: thor@sarkor.com
Subject: Re: index usage
In-Reply-To: <10597430257.20030117164800@sarkor.com>
* 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