X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A4E84D1B520; Sat, 25 Oct 2003 17:49:48 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 61448-08; Sat, 25 Oct 2003 14:49:17 -0300 (ADT) Received: from hertz.gene.com (fw-open-virt.gene.com [192.12.78.250]) by svr1.postgresql.org (Postfix) with ESMTP id 38461D1B517; Sat, 25 Oct 2003 14:49:16 -0300 (ADT) Received: from tallac.gene.com (tallac.gene.com [128.137.116.213]) by hertz.gene.com (Switch-3.1.0/Switch-3.1.0) with ESMTP id h9PHn1tB013862; Sat, 25 Oct 2003 10:49:01 -0700 (PDT) Received: by tallac.gene.com (Postfix, from userid 13922) id 5B4C14B9BF; Sat, 25 Oct 2003 10:49:00 -0700 (PDT) Subject: explicit casting required for index use From: Reece Hart To: "pgsql-performance@postgresql.org" , pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="=-oBODLV6E1MTxTYCCWzJA" Message-Id: <1067104140.16297.125.camel@tallac> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Sat, 25 Oct 2003 10:49:00 -0700 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/1226 X-Sequence-Number: 51299 --=-oBODLV6E1MTxTYCCWzJA Content-Type: text/plain Content-Transfer-Encoding: 7bit Here's the basic issue: PostgreSQL doesn't use indexes unless a query criterion is of exactly the same type as the index type. This occurs even when a cast would enable the use of an index and greatly improve performance. I understand that casting is needed to use an index and will therefore affect performance -- the part I don't understand is why postgresql doesn't automatically cast query arguments to the column type, thereby enabling indexes on that column. I have a table that looks like this (extra cols, indexes, and fk constraints removed): unison@csb=# \d paprospect2 Table "unison.paprospect2" Column | Type | Modifiers -------------+---------+------------------------------------------------------------------- pseq_id | integer | not null run_id | integer | not null pmodel_id | integer | not null svm | real | Indexes: paprospect2_search1 btree (pmodel_id, run_id, svm), I often search for pseq_ids based on all of pmodel_id, run_id, and svm threshold as below, hence the multi-column index. Without an explicit cast of the svm criterion: unison@csb=# explain select pseq_id from paprospect2 where pmodel_id=8210 and run_id=1 and svm>=11; Index Scan using paprospect2_search2 on paprospect2 (cost=0.00..43268.93 rows=2 width=4) Index Cond: ((pmodel_id = 8210) AND (run_id = 1)) Filter: (svm >= 11::double precision) And with an explicit cast to real (the same as the column type and indexed type): unison@csb=# explain select pseq_id from paprospect2 where pmodel_id=8210 and run_id=1 and svm>=11::real; Index Scan using paprospect2_search1 on paprospect2 (cost=0.00..6.34 rows=2 width=4) Index Cond: ((pmodel_id = 8210) AND (run_id = 1) AND (svm >= 11::real)) Note two things above: 1) The explicit cast greatly reduces the predicted (and actual) cost. 2) The uncasted query eventually casts svm to double precision, which seems odd since the column itself is real (that is, it eventually does cast, but to the "wrong" type). For small queries (returning ~10 rows), this is worth 100x in speed (9ms v. 990ms... in absolute terms, no big deal). For larger result sets (~200 rows), I've seen more like 1000x speed increases by using an explicit cast. For the larger queries, this can mean seconds versus many minutes. Having to explicitly cast criterion is very non-intuitive. Moreover, it seems quite straightforward that PostgreSQL might incorporate casts (and perhaps even function calls like upper() for functional indexes) into its query strategy optimization. (I suppose functional indexes would apply only to immutable fx only, but that's fine.) Thanks, Reece -- Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/ Genentech, Inc. 650/225-6133 (voice), -5389 (fax) Bioinformatics and Protein Engineering 1 DNA Way, MS-93 http://www.in-machina.com/~reece/ South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0 --=-oBODLV6E1MTxTYCCWzJA Content-Type: text/html; charset=utf-8 Here's the basic issue: PostgreSQL doesn't use indexes unless a query criterion is of exactly the same type as the index type. This occurs even when a cast would enable the use of an index and greatly improve performance. I understand that casting is needed to use an index and will therefore affect performance -- the part I don't understand is why postgresql doesn't automatically cast query arguments to the column type, thereby enabling indexes on that column.


I have a table that looks like this (extra cols, indexes, and fk constraints removed):
unison@csb=# \d paprospect2
                                Table "unison.paprospect2"
   Column    |  Type   |                             Modifiers
-------------+---------+-------------------------------------------------------------------
 pseq_id     | integer | not null
 run_id      | integer | not null
 pmodel_id   | integer | not null
 svm         | real    |
Indexes: paprospect2_search1 btree (pmodel_id, run_id, svm),

I often search for pseq_ids based on all of pmodel_id, run_id, and svm threshold as below, hence the multi-column index.

Without an explicit cast of the svm criterion:
unison@csb=# explain select pseq_id from paprospect2 where pmodel_id=8210 and run_id=1 and svm>=11;
 Index Scan using paprospect2_search2 on paprospect2  (cost=0.00..43268.93 rows=2 width=4)
   Index Cond: ((pmodel_id = 8210) AND (run_id = 1))
   Filter: (svm >= 11::double precision)
And with an explicit cast to real (the same as the column type and indexed type):
unison@csb=# explain select pseq_id from paprospect2 where pmodel_id=8210 and run_id=1 and svm>=11::real;
 Index Scan using paprospect2_search1 on paprospect2  (cost=0.00..6.34 rows=2 width=4)
   Index Cond: ((pmodel_id = 8210) AND (run_id = 1) AND (svm >= 11::real))

Note two things above: 1) The explicit cast greatly reduces the predicted (and actual) cost. 2) The uncasted query eventually casts svm to double precision, which seems odd since the column itself is real (that is, it eventually does cast, but to the "wrong" type).

For small queries (returning ~10 rows), this is worth 100x in speed (9ms v. 990ms... in absolute terms, no big deal). For larger result sets (~200 rows), I've seen more like 1000x speed increases by using an explicit cast. For the larger queries, this can mean seconds versus many minutes.

Having to explicitly cast criterion is very non-intuitive. Moreover, it seems quite straightforward that PostgreSQL might incorporate casts (and perhaps even function calls like upper() for functional indexes) into its query strategy optimization. (I suppose functional indexes would apply only to immutable fx only, but that's fine.)

Thanks,
Reece

-- 
Reece Hart, Ph.D.                       rkh@gene.com, http://www.gene.com/
Genentech, Inc.                         650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                        http://www.in-machina.com/~reece/
South San Francisco, CA  94080-4990     reece@in-machina.com, GPG: 0x25EC91A0
--=-oBODLV6E1MTxTYCCWzJA--