Re: Sequencial scan instead of using index - Mailing list pgsql-performance

From Harry Hehl
Subject Re: Sequencial scan instead of using index
Date
Msg-id 6AD4F3A63B017C4FB074E2C895AD18541090C6@EXCHSRV.waterloonetworking.net
Whole thread Raw
In response to Sequencial scan instead of using index  ("Harry Hehl" <Harry.Hehl@diskstream.com>)
Responses Re: Sequencial scan instead of using index  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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@paradise.net.nz]
Sent: Tuesday, March 07, 2006 12:04 AM
To: Harry Hehl
Cc: pgsql-performance@postgresql.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

pgsql-performance by date:

Previous
From: markir@paradise.net.nz
Date:
Subject: Re: Sequencial scan instead of using index
Next
From: Tom Lane
Date:
Subject: Re: Sequencial scan instead of using index