Re: Sequencial scan instead of using index

Quoting Harry Hehl <Harry(dot)Hehl(at)diskstream(dot)com>:

> Mark,
>
> (snippage)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
>
>

Hmm - that first query needs to do a sort, so you might want to experiment with
the sort_mem parameter. Could you show us output from explain analyze for both
the above queries?

At face value, selecting 200000 rows (assuming the estimates are accurate) may
mean that a seqscan is the best plan! But we'll know more after seeing the
explain analyze...

Cheers

Mark