BUG #13817: Query planner strange choose while select/count small part of big table - complete - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      13817
Logged by:          Marcin_S
Email address:      sienkomarcin@gmail.com
PostgreSQL version: 9.4.5
Operating system:   Windows 7 x64
Description:

Hi,

First sorry for not completed last bug (browser handle it too fast :). Here
is complete version:


I've check todo list but i can't find exact problem i'm reporting. It seems
like query planner fires not needed sequence scan by all rows in table when
only a few rows were picked. I will explain on example. I have 2 tables
with
relation 1 - n. Index on foreign key exist. On n side there are a lot o
rows
(about 4 000 000 in my db). When i select/count rows form n table
joined/subselected from 1-side than planner traverse every of 4 000 000
rows
although it has only 3 in "valid from where conditions". Execution takes
2191 ms on my pc. When i turn off sequence scan it takes 12 ms.

Below i include query and explain analyze output with sequence scan on and
off. Similar situation is without subquery (with joins).

Query:

select
        *
        --count(this_.id) as y0_
    from
--
        shipment_order_sub_item this_
    left outer join
        shipment_order_item orderitem1_
            on this_.shipment_order_item_id=orderitem1_.id
    where
        orderitem1_.id in (
            select
                oi_.id as y0_
            from
                shipment_order_item oi_
            inner join
                shipment_order order1_
                    on oi_.order_id=order1_.id
            inner join
                court_department courtdepar3_
                    on
order1_.court_department_id=courtdepar3_.department_id
            inner join
                application_user user2_
                    on order1_.user_id=user2_.users_id
            where
                order1_.id = 610
                and order1_.court_department_id in (1,292,32768 )
        );

sequence scan on (default):

"Hash Join  (cost=12.88..108087.13 rows=3992515 width=177) (actual
time=2426.511..2426.515 rows=3 loops=1)"
"  Hash Cond: (this_.shipment_order_item_id = orderitem1_.id)"
"  ->  Seq Scan on shipment_order_sub_item this_  (cost=0.00..90031.15
rows=3992515 width=125) (actual time=0.022..1071.889 rows=3992110 loops=1)"
"  ->  Hash  (cost=12.87..12.87 rows=1 width=60) (actual time=0.175..0.175
rows=3 loops=1)"
"        Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"        ->  Hash Semi Join  (cost=11.69..12.87 rows=1 width=60) (actual
time=0.166..0.173 rows=3 loops=1)"
"              Hash Cond: (orderitem1_.id = oi_.id)"
"              ->  Seq Scan on shipment_order_item orderitem1_
(cost=0.00..1.13 rows=13 width=52) (actual time=0.009..0.010 rows=13
loops=1)"
"              ->  Hash  (cost=11.68..11.68 rows=1 width=8) (actual
time=0.144..0.144 rows=3 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                    ->  Nested Loop  (cost=0.28..11.68 rows=1 width=8)
(actual time=0.079..0.139 rows=3 loops=1)"
"                          Join Filter: (order1_.user_id =
user2_.users_id)"
"                          Rows Removed by Join Filter: 9"
"                          ->  Nested Loop  (cost=0.28..10.59 rows=1
width=16) (actual time=0.063..0.105 rows=3 loops=1)"
"                                ->  Nested Loop  (cost=0.00..2.29 rows=1
width=24) (actual time=0.027..0.052 rows=3 loops=1)"
"                                      ->  Seq Scan on shipment_order_item
oi_  (cost=0.00..1.16 rows=1 width=16) (actual time=0.007..0.010 rows=3
loops=1)"
"                                            Filter: (order_id = 610)"
"                                            Rows Removed by Filter: 10"
"                                      ->  Seq Scan on shipment_order
order1_  (cost=0.00..1.11 rows=1 width=24) (actual time=0.006..0.007 rows=1
loops=3)"
"                                            Filter: ((id = 610) AND
(court_department_id = ANY ('{1,292,32768}'::bigint[])))"
"                                            Rows Removed by Filter: 6"
"                                ->  Index Only Scan using
court_department_pkey on court_department courtdepar3_  (cost=0.28..8.29
rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=3)"
"                                      Index Cond: (department_id =
order1_.court_department_id)"
"                                      Heap Fetches: 3"
"                          ->  Seq Scan on application_user user2_
(cost=0.00..1.04 rows=4 width=8) (actual time=0.003..0.004 rows=4 loops=3)"
"Planning time: 1.765 ms"
"Execution time: 2426.724 ms"


sequence scan off:

"Nested Loop  (cost=1.24..138607.34 rows=3992515 width=177) (actual
time=0.151..0.168 rows=3 loops=1)"
"  ->  Nested Loop Semi Join  (cost=0.81..45.31 rows=1 width=60) (actual
time=0.139..0.143 rows=3 loops=1)"
"        Join Filter: (orderitem1_.id = oi_.id)"
"        Rows Removed by Join Filter: 33"
"        ->  Index Scan using shipment_order_item_pkey on
shipment_order_item orderitem1_  (cost=0.14..12.33 rows=13 width=52) (actual
time=0.018..0.023 rows=13 loops=1)"
"        ->  Materialize  (cost=0.67..32.79 rows=1 width=8) (actual
time=0.004..0.007 rows=3 loops=13)"
"              ->  Nested Loop  (cost=0.67..32.78 rows=1 width=8) (actual
time=0.049..0.086 rows=3 loops=1)"
"                    ->  Nested Loop  (cost=0.54..24.62 rows=1 width=16)
(actual time=0.042..0.069 rows=3 loops=1)"
"                          ->  Nested Loop  (cost=0.27..16.32 rows=1
width=24) (actual time=0.028..0.044 rows=3 loops=1)"
"                                ->  Index Scan using
fk_fk_mt3v1s9gl7rr0lo83il8gy00d_idx on shipment_order_item oi_
(cost=0.14..8.15 rows=1 width=16) (actual time=0.014..0.017 rows=3
loops=1)"
"                                      Index Cond: (order_id = 610)"
"                                ->  Index Scan using shipment_order_pkey on
shipment_order order1_  (cost=0.13..8.15 rows=1 width=24) (actual
time=0.006..0.007 rows=1 loops=3)"
"                                      Index Cond: (id = 610)"
"                                      Filter: (court_department_id = ANY
('{1,292,32768}'::bigint[]))"
"                          ->  Index Only Scan using court_department_pkey
on court_department courtdepar3_  (cost=0.28..8.29 rows=1 width=8) (actual
time=0.005..0.006 rows=1 loops=3)"
"                                Index Cond: (department_id =
order1_.court_department_id)"
"                                Heap Fetches: 3"
"                    ->  Index Only Scan using application_user_pkey on
application_user user2_  (cost=0.13..8.15 rows=1 width=8) (actual
time=0.003..0.004 rows=1 loops=3)"
"                          Index Cond: (users_id = order1_.user_id)"
"                          Heap Fetches: 3"
"  ->  Index Scan using fk_fk_rr5k2n8n892ye3uposkh3xp6v_idx on
shipment_order_sub_item this_  (cost=0.43..98636.88 rows=3992515 width=125)
(actual time=0.006..0.006 rows=1 loops=3)"
"        Index Cond: (shipment_order_item_id = orderitem1_.id)"
"Planning time: 1.552 ms"
"Execution time: 0.311 ms"