pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar_daithankar@myrealbox.com>
To: pgsql-performance@postgresql.org
Subject: Re: optimizing Postgres queries
Date: Mon, 5 Jan 2004 17:40:05 +0530
Message-ID: <200401051740.05006.shridhar_daithankar@myrealbox.com> (raw)
In-Reply-To: <64C8DBBA-3F77-11D8-A528-000A95A6F0DC@cluster9.com>
References: <4B0023AC-3F72-11D8-B3EB-000A95A6F0DC@cluster9.com>
	<200401051705.55120.shridhar_daithankar@myrealbox.com>
	<64C8DBBA-3F77-11D8-A528-000A95A6F0DC@cluster9.com>

On Monday 05 January 2004 17:35, David Teran wrote:
> 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.

The performance will likely to be the same. Its just that integer happens to 
be default integer type and hence it does not need an explicit typecast. ( I 
don't remember exactly which integer is default but it is either of int2,int4 
and int8...:-))

The performance diffference is likely due to use of index, which is in turn 
due to typecasting. If you need bigint, you should use them. Just remember to 
typecast whenever required.

 Shridhar




view thread (17+ messages)  latest in thread

Message-ID: <200401051740.05006.shridhar_daithankar@myrealbox.com>
Permalink:  ../200401051740.05006.shridhar_daithankar@myrealbox.com/
Also on:    postgresql.org/message-id/200401051740.05006.shridhar_daithankar@myrealbox.com

 · 

reply

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: shridhar_daithankar@myrealbox.com
  Subject: Re: optimizing Postgres queries
  In-Reply-To: <200401051740.05006.shridhar_daithankar@myrealbox.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