Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D9EB6475E18
	for <pgsql-performance@postgresql.org>;
	Fri, 17 Jan 2003 06:47:54 -0500 (EST)
Received: from mail.sarkor.com (unknown [81.95.224.36])
	by postgresql.org (Postfix) with SMTP id 5BC2F475EC9
	for <pgsql-performance@postgresql.org>;
	Fri, 17 Jan 2003 06:47:52 -0500 (EST)
Received: (qmail 10337 invoked by uid 507); 17 Jan 2003 11:47:51 -0000
Received: from thor@sarkor.com by mail.sarkor.com by uid 504 with
	qmail-scanner-1.14 (clamscan: 0.51.  Clear:.
	Processed in 0.440657 secs); 17 Jan 2003 11:47:51 -0000
Received: from unknown (HELO timur) (81.95.224.66)
	by mail.sarkor.com with SMTP; 17 Jan 2003 11:47:50 -0000
Date: Fri, 17 Jan 2003 16:48:00 +0500
From: Timur Irmatov <thor@sarkor.com>
X-Mailer: The Bat! (v1.61)
Reply-To: Timur Irmatov <thor@sarkor.com>
X-Priority: 3 (Normal)
Message-ID: <10597430257.20030117164800@sarkor.com>
To: pgsql-performance@postgresql.org
Subject: index usage
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/174
X-Sequence-Number: 821

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.