X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B3BB7475F2C for ; Wed, 7 May 2003 14:43:03 -0400 (EDT) Received: from email03.aon.at (WARSL401PIP6.highway.telekom.at [195.3.96.93]) by postgresql.org (Postfix) with SMTP id BD3FC476508 for ; Wed, 7 May 2003 14:42:57 -0400 (EDT) Received: (qmail 113116 invoked from network); 7 May 2003 18:42:56 -0000 Received: from m156p012.dipool.highway.telekom.at (HELO cantor) ([62.46.9.108]) (envelope-sender ) by qmail3rs.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 7 May 2003 18:42:56 -0000 From: Manfred Koizar To: Achilleus Mantzios Cc: Hannu Krosing , pgsql-performance@postgresql.org Subject: Re: An unresolved performance problem. Date: Wed, 07 May 2003 20:42:46 +0200 Message-ID: <1akibvk37pfk85muvsko20egsirhvsgm2g@4ax.com> References: <1052314806.2205.6.camel@fuji.krosing.net> In-Reply-To: X-Mailer: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-19.2 required=5.0 tests=BAYES_01,IN_REP_TO,REFERENCES,USER_AGENT_FORTE autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/87 X-Sequence-Number: 1866 On Wed, 7 May 2003 17:09:17 -0200 (GMT+2), Achilleus Mantzios wrote: >I have about 10 indexes on this table, and the "correct" one >is used only if i do set enable_seqscan to off; and >drop all other indexes. What we already have is |dynacom=# EXPLAIN ANALYZE |SELECT count(*) | FROM status | WHERE assettable='vessels' AND appname='ISM PMS' AND apptblname='items' AND status='warn' AND isvalid AND assetidval=57; | |QUERY PLAN (fbsd) |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Aggregate (cost=6.02..6.02 rows=1 width=0) (actual time=14.16..14.16 rows=1 loops=1) | -> Index Scan using status_all on status (cost=0.00..6.02 rows=1 width=0) (actual time=13.09..13.95 rows=75 loops=1) | Index Cond: ((assettable = 'vessels'::character varying) AND (assetidval = 57) AND (appname = 'ISM PMS'::character varying) AND (apptblname = 'items'::character varying) AND (status = 'warn'::character varying)) | Filter: isvalid | Total runtime: 14.40 msec |(5 rows) | |QUERY PLAN (lnx) |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Aggregate (cost=1346.56..1346.56 rows=1 width=0) (actual time=244.05..244.05 rows=1 loops=1) | -> Seq Scan on status (cost=0.00..1345.81 rows=300 width=0) (actual time=0.63..243.93 rows=75 loops=1) | Filter: ((assettable = 'vessels'::character varying) AND (appname = 'ISM PMS'::character varying) AND (apptblname = 'items'::character varying) AND (status = 'warn'::character varying) AND isvalid AND (assetidval = 57)) | Total runtime: 244.12 msec |(4 rows) Now set enable_seqscan to off, and show as the EXPLAIN ANALYSE output. If the wrong index is used, remove it and rerun the query. Repeat until you arrive at the correct index and show us these results, too. >Otherwise i get either a seq scan or the wrong index. | -> Seq Scan on status (cost=0.00..1345.81 rows=300 width=0) (actual time=0.63..243.93 rows=75 loops=1) ^^^^ This seems strange, given that relpages = 562. What are your config settings? And what hardware is this running on, especially how much RAM? Servus Manfred