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'::charactervarying) 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
