From: Manfred Koizar <mkoi-pg@aon.at>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: Hannu Krosing <hannu@tm.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: An unresolved performance problem.
Date: Wed, 07 May 2003 20:42:46 +0200
Message-ID: <1akibvk37pfk85muvsko20egsirhvsgm2g@4ax.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0305071700530.28446-100000@matrix.gatewaynet.com>
References: <1052314806.2205.6.camel@fuji.krosing.net>
<Pine.LNX.4.44.0305071700530.28446-100000@matrix.gatewaynet.com>
On Wed, 7 May 2003 17:09:17 -0200 (GMT+2), Achilleus Mantzios
<achill@matrix.gatewaynet.com> 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
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-sql@postgresql.org
Cc: mkoi-pg@aon.at, achill@matrix.gatewaynet.com, hannu@tm.ee, pgsql-performance@postgresql.org
Subject: Re: An unresolved performance problem.
In-Reply-To: <1akibvk37pfk85muvsko20egsirhvsgm2g@4ax.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