Re: Sequencial scan instead of using index

Mark,

>If you can upgrade to 8.1.(3), then the planner can consider paths that

>use *both* the indexes on srcobj and dstobj (which would probably be
the
>business!).

Yes, 8.1.3 resolved this issue. Thanks.

However I am still getting seq scans on indexes for other queries

For example:

select * from omfile where ( objectid in ( select distinct(ref_oid) from
ts ) );
objectid & ref_oid are non-unique indexes
omimagefile & omclipfile inherit from omfile

------------------------------------------------------------------------
--------

Nested Loop IN Join (cost=21432.32..951981.42 rows=204910 width=217)
Join Filter: ("outer".objectid = "inner".ref_oid)
-> Append (cost=0.00..8454.10 rows=204910 width=217)
-> Seq Scan on omfile (cost=0.00..8428.20 rows=204320
width=217)
-> Seq Scan on omimagefile omfile (cost=0.00..12.70 rows=270
width=217)
-> Seq Scan on omclipfile omfile (cost=0.00..13.20 rows=320
width=217)
-> Materialize (cost=21432.32..21434.32 rows=200 width=16)
-> Unique (cost=20614.91..21430.12 rows=200 width=16)
-> Sort (cost=20614.91..21022.52 rows=163041 width=16)
Sort Key: ts.ref_oid
-> Seq Scan on ts (cost=0.00..3739.41 rows=163041
width=16)

(11 rows)
Time: 164.232 ms

BTW set enable_seqscan=off has no affect i.e still uses seq scans.

If I do a simple query, it is very quick, no sequencial scans.
So how can I get index scans to work consistently with joins?

explain select * from omfile where
objectid='65ef0be3-bf02-46b6-bae9-5bd015ffdb79';

------------------------------------------------------------------------
--------

Result (cost=2.00..7723.30 rows=102903 width=217)
-> Append (cost=2.00..7723.30 rows=102903 width=217)
-> Bitmap Heap Scan on omfile (cost=2.00..7697.60 rows=102608
width=217)
Recheck Cond: (objectid =
'65ef0be3-bf02-46b6-bae9-5bd015ffdb79'::capsa_sys.uuid)
-> Bitmap Index Scan on omfile_objectid_idx
(cost=0.00..2.00 rows=102608 width=0)
Index Cond: (objectid =
'65ef0be3-bf02-46b6-bae9-5bd015ffdb79'::capsa_sys.uuid)
-> Bitmap Heap Scan on omimagefile omfile (cost=1.00..12.69
rows=135 width=217)
Recheck Cond: (objectid =
'65ef0be3-bf02-46b6-bae9-5bd015ffdb79'::capsa_sys.uuid)
-> Bitmap Index Scan on omimagefile_objectid_idx
(cost=0.00..1.00 rows=135 width=0)
Index Cond: (objectid =
'65ef0be3-bf02-46b6-bae9-5bd015ffdb79'::capsa_sys.uuid)
-> Bitmap Heap Scan on omclipfile omfile (cost=1.00..13.00
rows=160 width=217)
Recheck Cond: (objectid =
'65ef0be3-bf02-46b6-bae9-5bd015ffdb79'::capsa_sys.uuid)
-> Bitmap Index Scan on omclipfile_objectid_idx
(cost=0.00..1.00 rows=160 width=0)
Index Cond: (objectid =
'65ef0be3-bf02-46b6-bae9-5bd015ffdb79'::capsa_sys.uuid)

(14 rows)
Time: 5.164

-----Original Message-----
From: Mark Kirkwood [mailto:markir(at)paradise(dot)net(dot)nz]
Sent: Tuesday, March 07, 2006 12:04 AM
To: Harry Hehl
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Sequencial scan instead of using index

Harry Hehl wrote:
> There seems to be many posts on this issue but I not yet found an
answer to the seq scan issue.
>
> I am having an issue with a joins. I am using 8.0.3 on FC4
>
> Query: select * from ommemberrelation where srcobj='somevalue' and
> dstobj in (select objectid from omfilesysentry where
> name='dir15_file80');
>
> Columns srcobj, dstobj & name are all indexed.
>
>

The planner is over-estimating the number of rows here (33989 vs 100):

-> Seq Scan on ommemberrelation (cost=0.00..2394.72 rows=33989
width=177) (actual time=0.078..70.887 rows=100 loops=1)

The usual way to attack this is to up the sample size for ANALYZE:

ALTER TABLE ommemberrelation ALTER COLUMN srcobj SET STATISTICS 100;
ALTER TABLE ommemberrelation ALTER COLUMN dstobj SET STATISTICS 100;
-- or even 1000.
ANALYZE ommemberrelation;

Then try EXPLAIN ANALYZE again.

If you can upgrade to 8.1.(3), then the planner can consider paths that
use *both* the indexes on srcobj and dstobj (which would probably be the

business!).

Cheers

Mark