X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0B859D1B4D7 for ; Mon, 5 Jan 2004 11:37:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 48120-09 for ; Mon, 5 Jan 2004 07:36:59 -0400 (AST) Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [192.108.102.143]) by svr1.postgresql.org (Postfix) with ESMTP id 5D230D1B48E for ; Mon, 5 Jan 2004 07:36:56 -0400 (AST) Received: from daithan.intranet.pspl.co.in shridhar_daithankar@smtp-send.myrealbox.com [202.54.11.72] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 3.47 $ on Novell NetWare via secured & encrypted transport (TLS); Mon, 05 Jan 2004 04:36:53 -0700 From: Shridhar Daithankar To: pgsql-performance@postgresql.org Subject: Re: optimizing Postgres queries Date: Mon, 5 Jan 2004 17:05:55 +0530 User-Agent: KMail/1.5.4 References: <4B0023AC-3F72-11D8-B3EB-000A95A6F0DC@cluster9.com> In-Reply-To: <4B0023AC-3F72-11D8-B3EB-000A95A6F0DC@cluster9.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200401051705.55120.shridhar_daithankar@myrealbox.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/27 X-Sequence-Number: 5202 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