473,287 Members | 1,613 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

explicit casting required for index use

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. rk*@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 re***@in-machina.com, GPG: 0x25EC91A0

Nov 12 '05 #1
0 1757

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

231
by: Brian Blais | last post by:
Hello, I saw on a couple of recent posts people saying that casting the return value of malloc is bad, like: d=(double *) malloc(50*sizeof(double)); why is this bad? I had always thought...
11
by: Steven Bartley | last post by:
Error Message on execution: An unhandled exception of type 'System.InvalidCastException' occurred in CastObjectToDecimal.exe Additional information: Specified cast is not valid. The code in...
2
by: rajivpopat | last post by:
I've been reading a discussion thread at http://groups.google.com/group/microsoft.public.dotnet.languages.csharp/browse_frm/thread/119f8362a9f5ff52 regarding typecasting generic collections to...
2
by: John Richardson | last post by:
How does a custom casting operator work? I have a class that defines one, but doesn't work as expected (or perhaps, the way I want it to). I have the following example classes:...
8
by: mfc | last post by:
Suppose I have a Cookie class and a Factory Class. There are many types of Cookies and maybe one or more Factories with a ProcessCookie Method. Suppose all the PutInBox method does is decide what...
4
by: Xavier Roche | last post by:
Hi folks, I have a probably rather silly question: is casting a char array in a char* a potential source of aliasing bug ? Example: a fonction returning a buffer taken in a circular buffer ...
1
by: recherche | last post by:
Hola! I tried the following public implementation of interface indexer by struct (Code Snippet 1) in private and explicit implementation by struct (Code Snippet 2) but in vain. Please help! ...
17
by: sophia.agnes | last post by:
Hi , I was going through peter van der linden's book Expert C programming, in this book there is a section named "How and why to cast" the author then says as follows (float) 3 - it's a...
9
by: Taras_96 | last post by:
Hi everyone, I was experimenting with static_cast and reinterpret cast #include <iostream> struct A1 { int a; }; struct A2 { double d; }; struct B : public A1, A2
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.