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

Re: optimizing Postgres queries

From: Shridhar Daithankar <shridhar_daithankar(at)myrealbox(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: optimizing Postgres queries
Date: 2004-01-05 11:35:55
Message-ID: 200401051705.55120.shridhar_daithankar@myrealbox.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Monday 05 January 2004 16:58, David Teran wrote:
> We have some tests to check the performance and FrontBase is about 10
> times faster than Postgres. We already played around with explain
> analyse select. It seems that for large tables Postgres does not use an
> index. We often see the scan message in the query plan. Were can we
> find more hints about tuning the performance? The database is about 350
> MB large, without BLOB's. We tried to define every important index for
> the selects but it seems that something still goes wrong: FrontBase
> needs about 23 seconds for about 4300 selects and Postgres needs 4
> minutes, 34 seconds.

Check 
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html

Are you sure you are using correct data types on indexes?

e.g. if field1 is an int2 field, then following query would not use an index.

select * from table where field1=2;

However following will

select * from table where field1=2::int2;

It is called as typecasting and postgresql is rather strict about it when it 
comes to making a decision of index usage.

I am sure above two tips could take care of some of the problems. 

Such kind of query needs more specific information. Can you post explain 
analyze output for queries and database schema.

 HTH

 Shridhar


In response to

Responses

pgsql-performance by date

Next:From: David TeranDate: 2004-01-05 12:05:03
Subject: Re: optimizing Postgres queries
Previous:From: David TeranDate: 2004-01-05 11:28:32
Subject: optimizing Postgres queries

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