Skip site navigation (1) Skip section navigation (2)

Re: optimizing Postgres queries

From: David Teran <david(dot)teran(at)cluster9(dot)com>
To: Shridhar Daithankar <shridhar_daithankar(at)myrealbox(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: optimizing Postgres queries
Date: 2004-01-05 12:05:03
Message-ID: 64C8DBBA-3F77-11D8-A528-000A95A6F0DC@cluster9.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Shridhar,

> Are you sure you are using correct data types on indexes?
>
Did not know about this...

> e.g. if field1 is an int2 field, then following query would not use an 
> index.
>
our fk have the type bigint, when i try one simple select like this:

explain analyze SELECT --columns-- FROM KEY_VALUE_META_DATA t0 WHERE 
t0.ID_FOREIGN_TABLE = 21110;

i see that no index is being used whereas when i use

explain analyze SELECT --columns-- FROM KEY_VALUE_META_DATA t0 WHERE 
t0.ID_FOREIGN_TABLE = 21110::bigint;

an index is used. Very fine, the performance is about 10 to 100 times 
faster for the single select.

I am using WebObjects with JDBC. I will now create a DB with integer 
instead of bigint and see how this performs.

regards David


In response to

Responses

pgsql-performance by date

Next:From: Shridhar DaithankarDate: 2004-01-05 12:10:05
Subject: Re: optimizing Postgres queries
Previous:From: Shridhar DaithankarDate: 2004-01-05 11:35:55
Subject: Re: optimizing Postgres queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group