Re: Bad plan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



In my opinion this is the Achilles heel of the postgres optimizer.  Row estimates should never return 1, unless the estimate is provably <=1.  This is particularly a problem with join estimates.  A dumb fix for this is to change clamp_join_row_est() to never return a value <2.  This fixes most of my observed poor plans.  The real fix is to track uniqueness (or provable max rows) along with the selectivity estimate.

Here's the dumb fix.

https://github.com/labkey-matthewb/postgres/commit/b1fd99f4deffbbf3db2172ccaba51a34f18d1b1a



On Tue, Jan 23, 2018 at 7:59 AM, Laurent Martelli <martellilaurent@xxxxxxxxx> wrote:
I've have a look to the plan with pgadmin, and I think the problem is
rather here :

->  Sort  (cost=4997.11..4997.11 rows=1 width=69) (actual
time=27.427..28.896 rows=7359 loops=1)
      Sort Key: amendment.id
      Sort Method: quicksort  Memory: 1227kB
      ->  Nested Loop  (cost=183.44..4997.10 rows=1 width=69) (actual
time=1.115..24.616 rows=7359 loops=1)
            ->  Nested Loop  (cost=183.15..4996.59 rows=1 width=49)
(actual time=1.107..9.091 rows=7360 loops=1)
                  ->  Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on
contact_partner businessprovider  (cost=0.42..8.44 rows=1 width=13)
(actual time=0.010..0.010 rows=1 loops=1)
                        Index Cond: ((business_provider_code)::text =
'BRZH'::text)
                  ->  Bitmap Heap Scan on contract_contract_line
contractline  (cost=182.73..4907.58 rows=8057 width=52) (actual
time=1.086..5.231 rows=7360 loops=1)
                        Recheck Cond: (business_provider_partner =
businessprovider.id)
                        Heap Blocks: exact=3586
                        ->  Bitmap Index Scan on
contract_contract_line_business_provider_partner_idx
(cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
rows=7360 loops=1)
                              Index Cond: (business_provider_partner =
businessprovider.id)
            ->  Index Scan using contract_amendment_pkey on
contract_amendment amendment  (cost=0.29..0.50 rows=1 width=28)
(actual time=0.001..0.002 rows=1 loops=7360)
                  Index Cond: (id = contractline.amendment)

The bitmap scan on contract_contract_line is good (8057 vs 7360 rows),
and so is the index scan (1 row), but the JOIN with "contact_partner
businessProvider" should give the 8057 rows from the bitmap scan,
shouldn't it ?


2018-01-23 16:38 GMT+01:00 Laurent Martelli <martellilaurent@xxxxxxxxx>:
> 2018-01-23 16:18 GMT+01:00 Justin Pryzby <pryzby@xxxxxxxxxxxxx>:
>> On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote:
>>
>>> Here is the default plan :
>>
>> Can you resend without line breaks or paste a link to explain.depesz?
>
> I hope it's better like that. I've attached it too, just in case.
>
>>
>> The problem appears to be here:
>>
>> ->  Nested Loop Left Join  (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1)
>> Join Filter: (sh.share_holder_partner = partner.id)
>> Rows Removed by Join Filter: 204915707
>>
>> Justin
>
>
>
>                                     QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Sort  (cost=39200.76..39200.76 rows=1 width=1066) (actual
> time=341273.300..341274.244 rows=7359 loops=1)
>    Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC,
> status.name, contractline.id
>    Sort Method: quicksort  Memory: 3930kB
>    ->  Nested Loop Left Join  (cost=32069.19..39200.75 rows=1
> width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
>          ->  Nested Loop Left Join  (cost=32069.05..39200.50 rows=1
> width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
>                ->  Nested Loop Left Join  (cost=32068.77..39200.20
> rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
>                      ->  Nested Loop Left Join
> (cost=32068.64..39200.04 rows=1 width=507) (actual
> time=342.776..341058.256 rows=7359 loops=1)
>                            Join Filter: (cca.address = adr_contact.id)
>                            Rows Removed by Join Filter: 2254
>                            ->  Nested Loop Left Join
> (cost=32068.22..39199.55 rows=1 width=515) (actual
> time=342.767..340997.058 rows=7359 loops=1)
>                                  ->  Nested Loop Left Join
> (cost=32067.79..39198.84 rows=1 width=447) (actual
> time=342.753..340932.286 rows=7359 loops=1)
>                                        ->  Nested Loop Left Join
> (cost=32067.65..39198.67 rows=1 width=421) (actual
> time=342.748..340896.132 rows=7359 loops=1)
>                                              ->  Nested Loop Left Join
>  (cost=32067.23..39198.01 rows=1 width=279) (actual
> time=342.739..340821.987 rows=7359 loops=1)
>                                                    ->  Nested Loop
> Left Join  (cost=32067.09..39197.85 rows=1 width=276) (actual
> time=342.725..340775.031 rows=7359 loops=1)
>                                                          Join Filter:
> (sh.share_holder_partner = partner.id)
>                                                          Rows Removed
> by Join Filter: 204915707
>                                                          ->  Nested
> Loop Left Join  (cost=28514.61..34092.46 rows=1 width=244) (actual
> time=287.323..610.192 rows=7359 loops=1)
>                                                                ->
> Nested Loop Left Join  (cost=28514.47..34092.30 rows=1 width=239)
> (actual time=287.318..573.234 rows=7359 loops=1)
>
> ->  Hash Right Join  (cost=28513.48..34090.65 rows=1 width=159)
> (actual time=287.293..379.564 rows=7359 loops=1)
>
>     Hash Cond: (ws.contract_line = contractline.id)
>
>     ->  Seq Scan on shareholder_web_subscription ws
> (cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307
> rows=52884 loops=1)
>
>     ->  Hash  (cost=28513.47..28513.47 rows=1 width=143) (actual
> time=287.243..287.243 rows=7359 loops=1)
>
>           Buckets: 8192 (originally 1024)  Batches: 1 (originally 1)
> Memory Usage: 1173kB
>
>           ->  Nested Loop Left Join  (cost=17456.16..28513.47 rows=1
> width=143) (actual time=85.005..284.689 rows=7359 loops=1)
>
>                 ->  Nested Loop  (cost=17456.03..28513.31 rows=1
> width=148) (actual time=85.000..276.599 rows=7359 loops=1)
>
>                       ->  Nested Loop Left Join
> (cost=17455.73..28512.84 rows=1 width=148) (actual
> time=84.993..261.954 rows=7359 loops=1)
>
>                             ->  Nested Loop  (cost=17455.60..28512.67
> rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
>
>                                   ->  Nested Loop
> (cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977
> rows=7359 loops=1)
>
>                                         ->  Merge Right Join
> (cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200
> rows=7359 loops=1)
>
>                                               Merge Cond:
> (subscribed_power.amendment = amendment.id)
>
>                                               ->  GroupAggregate
> (cost=12457.78..22574.03 rows=75229 width=168) (actual
> time=57.500..175.674 rows=83432 loops=1)
>
>                                                     Group Key:
> subscribed_power.amendment
>
>                                                     ->  Merge Join
> (cost=12457.78..20764.08 rows=173917 width=12) (actual
> time=57.479..129.530 rows=87938 loops=1)
>
>                                                           Merge Cond:
> (subscribed_power.amendment = amendment_1.id)
>
>                                                           ->  Index
> Scan using contract_subscribed_power_amendment_idx on
> contract_subscribed_power subscribed_power  (cost=0.42..13523.09
> rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
>
>                                                           ->  Sort
> (cost=12457.36..12666.43 rows=83629 width=8) (actual
> time=57.467..67.071 rows=88019 loops=1)
>
>                                                                 Sort
> Key: amendment_1.id
>
>                                                                 Sort
> Method: quicksort  Memory: 6988kB
>
>                                                                 ->
> Hash Join  (cost=10.21..5619.97 rows=83629 width=8) (actual
> time=0.112..40.965 rows=83532 loops=1)
>
>
> Hash Cond: (amendment_1.pricing = pricing.id)
>
>
> ->  Seq Scan on contract_amendment amendment_1  (cost=0.00..4460.29
> rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
>
>
> ->  Hash  (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095
> rows=141 loops=1)
>
>
>      Buckets: 1024  Batches: 1  Memory Usage: 14kB
>
>
>      ->  Hash Join  (cost=1.07..8.43 rows=142 width=8) (actual
> time=0.012..0.078 rows=141 loops=1)
>
>
>            Hash Cond: (pricing.elec_range = elec_range.id)
>
>
>            ->  Seq Scan on pricing_pricing pricing  (cost=0.00..5.42
> rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
>
>
>            ->  Hash  (cost=1.03..1.03 rows=3 width=8) (actual
> time=0.006..0.006 rows=3 loops=1)
>
>
>                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
>
>
>                  ->  Seq Scan on fluid_elec_range elec_range
> (cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3
> loops=1)
>
>                                               ->  Sort
> (cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896
> rows=7359 loops=1)
>
>                                                     Sort Key:
> amendment.id
>
>                                                     Sort Method:
> quicksort  Memory: 1227kB
>
>                                                     ->  Nested Loop
> (cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616
> rows=7359 loops=1)
>
>                                                           ->  Nested
> Loop  (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091
> rows=7360 loops=1)
>
>                                                                 ->
> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner
> businessprovider  (cost=0.42..8.44 rows=1 width=13) (actual
> time=0.010..0.010 rows=1 loops=1)
>
>
> Index Cond: ((business_provider_code)::text = 'BRZH'::text)
>
>                                                                 ->
> Bitmap Heap Scan on contract_contract_line contractline
> (cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231
> rows=7360 loops=1)
>
>
> Recheck Cond: (business_provider_partner = businessprovider.id)
>
>
> Heap Blocks: exact=3586
>
>
> ->  Bitmap Index Scan on
> contract_contract_line_business_provider_partner_idx
> (cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
> rows=7360 loops=1)
>
>
>      Index Cond: (business_provider_partner = businessprovider.id)
>
>                                                           ->  Index
> Scan using contract_amendment_pkey on contract_amendment amendment
> (cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1
> loops=7360)
>
>                                                                 Index
> Cond: (id = contractline.amendment)
>
>                                         ->  Index Scan using
> contract_contract_pkey on contract_contract contract  (cost=0.29..0.40
> rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
>
>                                               Index Cond: (id =
> contractline.contract)
>
>                                   ->  Index Scan using
> contact_partner_pkey on contact_partner partner  (cost=0.42..0.74
> rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
>
>                                         Index Cond: (id =
> contract.main_client_partner)
>
>                             ->  Index Scan using
> contact_client_nature_pkey on contact_client_nature clientnature
> (cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1
> loops=7359)
>
>                                   Index Cond: (id =
> partner.client_nature)
>
>                       ->  Index Scan using territory_mpt_pkey on
> territory_mpt mpt  (cost=0.29..0.46 rows=1 width=16) (actual
> time=0.001..0.001 rows=1 loops=7359)
>
>                             Index Cond: (id = contractline.mpt)
>
>                 ->  Index Scan using contract_user_segment_pkey on
> contract_user_segment usersegment  (cost=0.14..0.15 rows=1 width=11)
> (actual time=0.001..0.001 rows=1 loops=7359)
>
>                       Index Cond: (id = amendment.user_segment)
>
> ->  Nested Loop Left Join  (cost=0.99..1.64 rows=1 width=96) (actual
> time=0.021..0.025 rows=1 loops=7359)
>
>     ->  Nested Loop Left Join  (cost=0.85..1.35 rows=1 width=89)
> (actual time=0.017..0.020 rows=1 loops=7359)
>
>           ->  Nested Loop Left Join  (cost=0.71..1.18 rows=1 width=76)
> (actual time=0.013..0.014 rows=1 loops=7359)
>
>                 ->  Index Scan using contact_address_pkey on
> contact_address a  (cost=0.42..0.85 rows=1 width=84) (actual
> time=0.005..0.006 rows=1 loops=7359)
>
>                       Index Cond: (mpt.address = id)
>
>                 ->  Index Scan using territory_commune_pkey on
> territory_commune commune  (cost=0.29..0.32 rows=1 width=16) (actual
> time=0.005..0.006 rows=1 loops=7359)
>
>                       Index Cond: (a.commune = id)
>
>           ->  Index Scan using territory_department_pkey on
> territory_department dept  (cost=0.14..0.16 rows=1 width=37) (actual
> time=0.003..0.004 rows=1 loops=7359)
>
>                 Index Cond: (commune.department = id)
>
>     ->  Index Scan using territory_region_pkey on territory_region reg
>  (cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1
> loops=7359)
>
>           Index Cond: (dept.region = id)
>                                                                ->
> Index Scan using administration_status_pkey on administration_status
> status  (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003
> rows=1 loops=7359)
>
> Index Cond: (id = contractline.status)
>                                                          ->
> GroupAggregate  (cost=3552.48..4479.27 rows=27827 width=80) (actual
> time=0.006..44.205 rows=27846 loops=7359)
>                                                                Group
> Key: sh.share_holder_partner
>                                                                ->
> Sort  (cost=3552.48..3624.85 rows=28948 width=17) (actual
> time=0.003..2.913 rows=28946 loops=7359)
>
> Sort Key: sh.share_holder_partner
>
> Sort Method: quicksort  Memory: 3030kB
>
> ->  Hash Join  (cost=2.23..1407.26 rows=28948 width=17) (actual
> time=0.024..12.296 rows=28946 loops=1)
>
>     Hash Cond: (sh.company = sh_coop.id)
>
>     ->  Seq Scan on shareholder_share_holder sh  (cost=0.00..1007.00
> rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
>
>           Filter: (nb_share > 0)
>
>           Rows Removed by Filter: 1934
>
>     ->  Hash  (cost=2.10..2.10 rows=10 width=13) (actual
> time=0.009..0.009 rows=10 loops=1)
>
>           Buckets: 1024  Batches: 1  Memory Usage: 9kB
>
>           ->  Seq Scan on contact_company sh_coop  (cost=0.00..2.10
> rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
>                                                    ->  Index Scan
> using crm_origin_pkey on crm_origin co  (cost=0.14..0.16 rows=1
> width=19) (actual time=0.004..0.004 rows=1 loops=7359)
>                                                          Index Cond:
> (id = ws.how_meet_enercoop)
>                                              ->  Index Scan using
> contact_contact_pkey on contact_contact mc  (cost=0.42..0.65 rows=1
> width=150) (actual time=0.007..0.008 rows=1 loops=7359)
>                                                    Index Cond:
> (partner.main_contact = id)
>                                        ->  Index Scan using
> contact_title_pkey on contact_title title  (cost=0.14..0.16 rows=1
> width=42) (actual time=0.003..0.003 rows=1 loops=7359)
>                                              Index Cond: (mc.title = id)
>                                  ->  Index Scan using
> contact_address_pkey on contact_address adr_contact  (cost=0.43..0.70
> rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
>                                        Index Cond: (id = CASE WHEN
> (CASE WHEN ((partner.person_category_select)::text =
> 'naturalPerson'::text) THEN 'P'::text WHEN
> ((partner.person_category_select)::text = 'legalPerson'::text) THEN
> 'M'::text ELSE '?????'::text END = 'P'::text) THEN
> COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro,
> mc.address) END)
>                            ->  Index Scan using
> contact_contact_address_contact_idx on contact_contact_address cca
> (cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1
> loops=7359)
>                                  Index Cond: (contact = mc.id)
>                      ->  Index Scan using
> contact_contact_address_status_pkey on contact_contact_address_status
> npai  (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000
> rows=0 loops=7359)
>                            Index Cond: (cca.contact_address_status = id)
>                ->  Index Scan using
> crm_crm_request_original_contract_line_idx on crm_crm_request
> mesrequest  (cost=0.28..0.29 rows=1 width=16) (actual
> time=0.003..0.003 rows=0 loops=7359)
>                      Index Cond: (original_contract_line = contractline.id)
>          ->  Index Scan using sale_product_sub_family_pkey on
> sale_product_sub_family mesproductsubfamily  (cost=0.14..0.20 rows=1
> width=62) (actual time=0.000..0.000 rows=0 loops=7359)
>                Index Cond: (id = mesrequest.product_sub_family)
>                Filter: (new_contract_ok IS TRUE)
>  Planning time: 21.106 ms
>  Execution time: 341275.027 ms
> (118 lignes)
>
>
> --
> http://www.laurentmartelli.com    //    http://www.imprimart.fr



--
http://www.laurentmartelli.com    //    http://www.imprimart.fr



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

  Powered by Linux