Re: optimizing Postgres queries

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