Re: Sequencial scan instead of using index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread 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@xxxxxxxxxxxxxxx] 
Sent: Tuesday, March 07, 2006 12:04 AM
To: Harry Hehl
Cc: pgsql-performance@xxxxxxxxxxxxxx
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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux