代码之家  ›  专栏  ›  技术社区  ›  Michael Buen

如何解释此执行计划(在vs中)?

  •  0
  • Michael Buen  · 技术社区  · 14 年前

    从执行计划来看,IN版本比EXISTS版本快

    急切地检查 条件。这个查询虽然看起来很直观,但我觉得它似乎很晚才解决最终条件的结果;也就是说,从内到外,我感觉到,由于下一个条件 因为它的内在条件。

    下面两个查询中哪一个更快,是IN版本还是EXISTS版本?

    在版本中:

    explain analyze
    select ceil( 
            ( count(distinct company_rec_id)::numeric + 1 ) 
            / ((1)) )::int 
    from 
    parcel ord
    join company c on c.company_rec_id = ord.client_rec_id
    
    where
        (
            (((E'')) <> '' and to_tsvector(c.company) @@ plainto_tsquery(extract_words(((E'')))))
            or ((E'')) = ''
        )
    
        and
        (
            (((0)) <> 0 and ord.parcel_number = ((0)))
            or ((0)) = 0
        )
    
    
        and parcel_rec_id in
        (
            select parcel_rec_id 
            from parcel_application 
            where parcel_application_rec_id in 
            (
                select parcel_application_rec_id 
                from parcel_application_shoe 
                where               
                    (
                    (((E'')) <> '' and to_tsvector(extract_words(shoe_description)) @@ plainto_tsquery(extract_words(((E'')))))
                    or ((E'')) = ''
                    )
    
                    and 
                    (
                    (((E'')) <> '' and to_tsvector(extract_words(order_number)) @@ plainto_tsquery(extract_words(((E'')))))
                    or ((E'')) = ''
                    )
    
    
                    and 
                    (
                    (((E'')) <> '' and to_tsvector(extract_words(style_number)) @@ plainto_tsquery(extract_words(((E'')))))
                    or ((E'')) = ''
                    )               
            )
        )
    
    "Aggregate  (cost=1060.73..1060.75 rows=1 width=37) (actual time=29.028..29.028 rows=1 loops=1)"
    "  ->  Hash Join  (cost=880.43..1053.04 rows=3074 width=37) (actual time=13.261..16.365 rows=3074 loops=1)"
    "        Hash Cond: ((ord.client_rec_id)::text = (c.company_rec_id)::text)"
    "        ->  Hash Join  (cost=864.79..995.14 rows=3074 width=37) (actual time=13.115..15.027 rows=3074 loops=1)"
    "              Hash Cond: ((ord.parcel_rec_id)::text = (parcel_application.parcel_rec_id)::text)"
    "              ->  Seq Scan on parcel ord  (cost=0.00..78.87 rows=3087 width=74) (actual time=0.005..0.373 rows=3087 loops=1)"
    "              ->  Hash  (cost=826.37..826.37 rows=3074 width=37) (actual time=13.102..13.102 rows=3074 loops=1)"
    "                    ->  HashAggregate  (cost=795.63..826.37 rows=3074 width=37) (actual time=11.835..12.281 rows=3074 loops=1)"
    "                          ->  Hash Join  (cost=541.34..787.59 rows=3218 width=37) (actual time=7.076..10.286 rows=3218 loops=1)"
    "                                Hash Cond: ((parcel_application.parcel_application_rec_id)::text = (parcel_application_shoe.parcel_application_rec_id)::text)"
    "                                ->  Seq Scan on parcel_application  (cost=0.00..122.18 rows=3218 width=74) (actual time=0.004..0.710 rows=3218 loops=1)"
    "                                ->  Hash  (cost=501.12..501.12 rows=3218 width=37) (actual time=7.061..7.061 rows=3218 loops=1)"
    "                                      ->  HashAggregate  (cost=468.94..501.12 rows=3218 width=37) (actual time=5.721..6.220 rows=3218 loops=1)"
    "                                            ->  Seq Scan on parcel_application_shoe  (cost=0.00..442.95 rows=10395 width=37) (actual time=0.004..2.318 rows=10395 loops=1)"
    "        ->  Hash  (cost=11.95..11.95 rows=295 width=37) (actual time=0.136..0.136 rows=295 loops=1)"
    "              ->  Seq Scan on company c  (cost=0.00..11.95 rows=295 width=37) (actual time=0.013..0.068 rows=295 loops=1)"
    "Total runtime: 29.122 ms"
    

    explain analyze 
    select ceil( 
                ( count(distinct company_rec_id)::numeric + 1 ) 
                / ((1)) )::int from 
    
    parcel ord
    join company c on c.company_rec_id = ord.client_rec_id
    where
    
        (
            (((E'')) <> '' and to_tsvector(c.company) @@ plainto_tsquery(extract_words(((E'')))))
            or ((E'')) = ''
        )
    
        and
        (
            (((0)) <> 0 and ord.parcel_number = ((0)))
            or ((0)) = 0
        )
    
    
        and exists
        (
            select * from parcel_application pa     
            where pa.parcel_rec_id = ord.parcel_rec_id
    
                and 
                exists
                (
                    select * from parcel_application_shoe ord_item 
                    where 
                        ord_item.parcel_application_rec_id = pa.parcel_application_rec_id
    
                        and         
                        (
                        (((E'')) <> '' and to_tsvector(extract_words(ord_item.shoe_description)) @@ plainto_tsquery(extract_words(((E'')))))
                        or ((E'')) = ''
                        )
    
                        and 
                        (
                        (((E'')) <> '' and to_tsvector(extract_words(ord_item.order_number)) @@ plainto_tsquery(extract_words(((E'')))))
                        or ((E'')) = ''
                        )
    
    
                        and 
                        (
                        (((E'')) <> '' and to_tsvector(extract_words(ord_item.style_number)) @@ plainto_tsquery(extract_words(((E'')))))
                        or ((E'')) = ''
                        )
    
    
    
                )
        )
    
    
    "Aggregate  (cost=17773.13..17773.16 rows=1 width=37) (actual time=34.519..34.520 rows=1 loops=1)"
    "  ->  Hash Join  (cost=17636.59..17769.11 rows=1609 width=37) (actual time=17.174..20.426 rows=3074 loops=1)"
    "        Hash Cond: ((ord.client_rec_id)::text = (c.company_rec_id)::text)"
    "        ->  Hash Join  (cost=17620.95..17731.35 rows=1609 width=37) (actual time=16.882..18.862 rows=3074 loops=1)"
    "              Hash Cond: ((ord.parcel_rec_id)::text = (pa.parcel_rec_id)::text)"
    "              ->  Seq Scan on parcel ord  (cost=0.00..78.87 rows=3087 width=74) (actual time=0.006..0.409 rows=3087 loops=1)"
    "              ->  Hash  (cost=17601.74..17601.74 rows=1537 width=37) (actual time=16.858..16.858 rows=3074 loops=1)"
    "                    ->  HashAggregate  (cost=17586.37..17601.74 rows=1537 width=37) (actual time=15.015..15.535 rows=3074 loops=1)"
    "                          ->  Seq Scan on parcel_application pa  (cost=0.00..17582.35 rows=1609 width=37) (actual time=10.040..12.440 rows=3218 loops=1)"
    "                                Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)"
    "                                SubPlan 1"
    "                                  ->  Index Scan using fki_parcel_application_shoe__parcel_application on parcel_application_shoe ord_item  (cost=0.00..16.28 rows=3 width=0) (never executed)"
    "                                        Index Cond: ((parcel_application_rec_id)::text = ($0)::text)"
    "                                SubPlan 2"
    "                                  ->  Seq Scan on parcel_application_shoe ord_item  (cost=0.00..442.95 rows=10395 width=37) (actual time=0.005..4.482 rows=10395 loops=1)"
    "        ->  Hash  (cost=11.95..11.95 rows=295 width=37) (actual time=0.284..0.284 rows=295 loops=1)"
    "              ->  Seq Scan on company c  (cost=0.00..11.95 rows=295 width=37) (actual time=0.010..0.155 rows=295 loops=1)"
    "Total runtime: 34.645 ms"
    

    这是我最终使用的最后一个查询 版本。我只能感知到两者之间的边际绩效差异 在和存在版本中。我在考虑下一个维护人员的可读性,因此我使用了

    select ord.parcel_number, ord.received_date, c.company
    from parcel ord
    join company c on c.company_rec_id = ord.client_rec_id
    
    where
    
        (
            :_company = ''
            or
            to_tsvector(c.company) @@ plainto_tsquery(extract_words(:_company))
        )
    
        and
        (
            :_fttsc_num = 0
            or ord.parcel_number = :_fttsc_num        
        )
    
    
    
    
    and 
    (
        (:_item = '' and :_order_num = '' and :_style_num = '')
    
    
        or
    
        ord.parcel_rec_id in
        (
            select parcel_rec_id 
            from parcel_application
            where parcel_application_rec_id in
                (
    
                    select parcel_application_rec_id 
                    from parcel_application_shoe 
                    where 
    
                        (
                            :_item = ''
                            or to_tsvector(extract_words(shoe_description)) @@ plainto_tsquery(extract_words(:_item))                           
                        )
    
                        and 
                        (
                            :_order_num = ''
                            or to_tsvector(extract_words(order_number)) @@ plainto_tsquery(extract_words(:_order_num))                          
                        )
    
    
                        and 
                        (
                            :_style_num = '' 
                            or to_tsvector(extract_words(style_number)) @@ plainto_tsquery(extract_words(:_style_num))                    
                        )
    
                ) -- parcel_application_rec_id IN
        ) -- parcel_rec_id IN
    )
    
    and
    
    -- material filter...
    (
        (:_material = '')
    
        or
    
        -- implied material <> ''
        parcel_rec_id in 
        (
            select parcel_rec_id 
            from parcel_application
            where parcel_application_rec_id in
                (
                    select parcel_application_rec_id
                    from mlist
                    where mlist_rec_id in
                        (
                            select mlist_rec_id
                            from mlist_detail 
                            join material m using(material_rec_id)
                            where to_tsvector(extract_words(m.material)) @@ plainto_tsquery(extract_words(:_material)) 
                        )
                )
        )
    
    )
    -- ...material filter
    
    and
    
    -- parameter filter...
    (
        (:_parameter = '')
    
        or
    
        -- implied parameter <> ''
        parcel_rec_id in 
        (
            select parcel_rec_id 
            from parcel_application
            where parcel_application_rec_id in
                (
                    select parcel_application_rec_id
                    from mlist
                    where mlist_rec_id in
                        (
                            select mlist_rec_id
                            from mlist_detail 
                            where mlist_detail_rec_id in 
                                (
                                    select mlist_detail_rec_id
                                    from mlist_detail_parameter 
                                    join parameter p using(parameter_rec_id)
                                    where to_tsvector(extract_words(p.parameter)) @@ plainto_tsquery(extract_words(:_parameter)) 
                                )
                        )
                )
        )
    
    )
    -- ...parameter filter
    
    order by ord.received_date
    
    1 回复  |  直到 14 年前
        1
  •  1
  •   Community CDub    7 年前

    您可能需要重新检查嵌套的EXISTS()子查询集。 In the past 似乎使用这些方法的性能不如在内部连接上使用单个连接。

    所以一般来说,变换如下:

    where exists (select 1 from foo where foo.outer_id = outer.outer_id 
                  and exists (select 1 from bar where bar.foo_id = foo.foo_id))
    

    进入之内

    where exists (select 1 from foo join bar using (foo_id)
                  where foo.outer_id = outer.outer_id)