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

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

Quoting "markir(at)paradise(dot)net(dot)nz" <markir(at)paradise(dot)net(dot)nz>:

> Hmm - that first query needs to do a sort, so you might want to
> experiment with
> the sort_mem parameter

Oops - I mean work_mem...

"Harry Hehl" <Harry(dot)Hehl(at)diskstream(dot)com> writes:
> 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

> So how can I get index scans to work consistently with joins?

It's not the join that's the problem, it's the inheritance. I recently
improved the planner so that it can consider appended indexscans for an
inheritance tree on the inside of a join, but no pre-8.2 release can do
it.

regards, tom lane