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:05:55 +0530
Message-ID: <200401051705.55120.shridhar_daithankar@myrealbox.com> (raw)
In-Reply-To: <4B0023AC-3F72-11D8-B3EB-000A95A6F0DC@cluster9.com>
References: <4B0023AC-3F72-11D8-B3EB-000A95A6F0DC@cluster9.com>

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




view thread (17+ messages)  latest in thread

Message-ID: <200401051705.55120.shridhar_daithankar@myrealbox.com>
Permalink:  ../200401051705.55120.shridhar_daithankar@myrealbox.com/
Also on:    postgresql.org/message-id/200401051705.55120.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: <200401051705.55120.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