From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
---|---|
To: | Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
Cc: | Hannu Krosing <hannu(at)tm(dot)ee>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: An unresolved performance problem. |
Date: | 2003-05-07 18:42:46 |
Message-ID: | 1akibvk37pfk85muvsko20egsirhvsgm2g@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-performance pgsql-sql |
On Wed, 7 May 2003 17:09:17 -0200 (GMT+2), Achilleus Mantzios
<achill(at)matrix(dot)gatewaynet(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleus Mantzios | 2003-05-07 19:09:17 | Re: An unresolved performance problem. |
Previous Message | Josh Berkus | 2003-05-07 16:57:22 | Re: Unanswered Questions WAS: An unresolved performance problem. |
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleus Mantzios | 2003-05-07 19:09:17 | Re: An unresolved performance problem. |
Previous Message | Josh Berkus | 2003-05-07 16:57:22 | Re: Unanswered Questions WAS: An unresolved performance problem. |
From | Date | Subject | |
---|---|---|---|
Next Message | Wei Weng | 2003-05-07 18:57:14 | Re: help database corruption |
Previous Message | Jon Earle | 2003-05-07 18:33:55 | Re: [GENERAL] PostgreSQL Qs |