[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Query that does not use indexes
From:       Denis Gasparin <denis () edistar ! com>
Date:       2007-03-27 10:54:49
Message-ID: 4608F7F9.9020706 () edistar ! com
[Download RAW message or body]

I have a query that performs a multiple join between four tables and
that doesn't use the defined indexes.

If I set enable_seqscan to off, the query obviously uses the indexes and
it is considerable faster than normal planned execution with
enable_seqscan=true.

Can you give me a reason why Postgresql is using seqscan when it should not?

I tryed also to vacuum analyze and reindex all the database but it
didn't change anything.

Thank you in advance,
Denis

---- Database and query infos ----

The database is made of four tables. Here it is an extract of the
definitition:

table order (70 records)
    order_id serial not null primary key,
    order_date timestamp not null

table order_part (233 records)
    part_id serial not null primary key,
    order_id integer references order(order_id)

table component (350000 records)
    serial_number serial not null primary key,
    part_id integer not null references order_part(part_id)

table component_part (50000 records)
    serial_number integer not null references component(serial_number),
    component_part_serial serial unique

Index "component_part_1" on serial_number of component_part
Index "component_part_id" on part_id of component


Here it is the query:

select to_char(ORDER.ORDER_DATE::date,'DD-MM-YYYY') as ORDER_DATE ,
count(component_part_serial) as COMPONENTS_PARTS_WITH_SERIAL,
count(*) as TOTAL_COMPONENTS_PARTS
from ORDER inner join ORDER_PART using(ORDER_ID)
inner join COMPONENT using(PART_ID)
inner join COMPONENT_PART using(SERIAL_NUMBER)
where ORDER.ORDER_DATE::date between  '2007-03-01' and '2007-03-27'
group by ORDER.ORDER_DATE::date order by ORDER.ORDER_DATE::date


Here it is the explain analyze with seqscan to on:

 Sort  (cost697.04..12697.04 rows=1 width$) (actual
time29.983..1929.991 rows=7 loops=1)
   Sort Key: (order.order_date)::date
   ->  HashAggregate  (cost697.00..12697.03 rows=1 width$) (actual
time29.898..1929.949 rows=7 loops=1)
         ->  Hash Join  (cost”62.76..12692.00 rowsf7 width$)
(actual time55.807..1823.750 rowsP125 loops=1)
               Hash Cond: ("outer".serial_number = "inner".serial_number)
               ->  Seq Scan on component_part  (cost=0.00..2463.76
rowsP476 width) (actual time=0.011..93.194 rowsP476 loops=1)
               ->  Hash  (cost”51.14..9451.14 rowsF49 width$)
(actual time33.016..1333.016 rowsP145 loops=1)
                     ->  Hash Join  (cost4.84..9451.14 rowsF49
width$) (actual time=1.350..1202.466 rowsP145 loops=1)
                           Hash Cond: ("outer".part_id = "inner".part_id)
                           ->  Seq Scan on component
(cost=0.00..7610.87 rows51787 width ) (actual time=0.004..603.470
rows51787 loops=1)
                           ->  Hash  (cost4.84..34.84 rows=3 width)
(actual time=1.313..1.313 rowsD loops=1)
                                 ->  Hash Join  (cost=7.40..34.84 rows=3
width) (actual time=0.943..1.221 rowsD loops=1)
                                       Hash Cond: ("outer".order_id "inner".order_id)
                                       ->  Seq Scan on order_part
(cost=0.00..26.27 rows"7 width=8) (actual time=0.005..0.465 rows#3
loops=1)
                                       ->  Hash  (cost=7.40..7.40 rows=1
width) (actual time=0.301..0.301 rows( loops=1)
                                             ->  Seq Scan on order
(cost=0.00..7.40 rows=1 width) (actual time=0.108..0.226 rows( loops=1)
                                                   Filter:
(((order_date)::date >= '2007-03-01'::date) AND ((order_date)::date \
<'2007-03-27'::date))  Total runtime: 1930.309 ms

Here it is the explain analyze with seqscan to off:

 Sort  (cost949.51..19949.51 rows=1 width$) (actual
time65.948..1165.955 rows=7 loops=1)
   Sort Key: (order.order_date)::date
   ->  HashAggregate  (cost949.47..19949.50 rows=1 width$) (actual
time65.865..1165.916 rows=7 loops=1)
         ->  Merge Join  (cost205.84..19944.47 rowsf7 width$)
(actual timeT1.778..1051.830 rowsP125 loops=1)
               Merge Cond: ("outer".serial_number = "inner".serial_number)
               ->  Sort  (cost205.84..15217.47 rowsF49 width$)
(actual timeT0.331..630.632 rowsP145 loops=1)
                     Sort Key: component.serial_number
                     ->  Nested Loop  (costc6.36..14922.66 rowsF49
width$) (actual time=0.896..277.778 rowsP145 loops=1)
                           ->  Nested Loop  (cost=0.00..72.73 rows=3
width) (actual time=0.861..24.820 rowsD loops=1)
                                 Join Filter: ("outer".order_id "inner".order_id)
                                 ->  Index Scan using order_pkey on
order  (cost=0.00..27.47 rows=1 width) (actual time=0.142..0.307
rows( loops=1)
                                       Filter: (((order_date)::date \
                >'2007-03-01'::date) AND ((order_date)::date <= '2007-03-27'::date))
                                 ->  Index Scan using order_part_pkey on
order_part  (cost=0.00..42.42 rows"7 width=8) (actual
time=0.006..0.524 rows#3 loops()
                           ->  Bitmap Heap Scan on component
(costc6.36..4852.26 rowsx17 width ) (actual time=0.259..2.324
rows40 loopsD)
                                 Recheck Cond: ("outer".part_id component.part_id)
                                 ->  Bitmap Index Scan on
component_part_id  (cost=0.00..636.36 rowsx17 width=0) (actual
time=0.250..0.250 rows40 loopsD)
                                       Index Cond: ("outer".part_id \
                component.part_id)
               ->  Index Scan using component_part_1 on component_part
(cost=0.00..4580.90 rowsP476 width) (actual time=0.155..117.566
rowsP476 loops=1)
 Total runtime: 1168.291 ms




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


[prev in list] [next in list] [prev in thread] [next in thread]