Re: Strange left outer join performance issue - Mailing list pgsql-performance
From | Noah M. Daniels |
---|---|
Subject | Re: Strange left outer join performance issue |
Date | |
Msg-id | 84DE2EAB-8235-402A-9097-1F3D8219D3FD@mac.com Whole thread Raw |
In response to | Re: Strange left outer join performance issue ("Daniel Cristian Cruz" <danielcristian@gmail.com>) |
List | pgsql-performance |
Not much of a difference, unfortunately... I still wonder why it's doing the 'supplier' (slow) query using the merge right join. the 'fast' query: Nested Loop Left Join (cost=0.00..423342.71 rows=2481 width=410) (actual time=100.076..6380.865 rows=1355 loops=1) -> Index Scan using index_aliases_company_type_company_id on aliases a (cost=0.00..462.33 rows=118 width=46) (actual time=24.811..143.690 rows=388 loops=1) Index Cond: ((company_type)::text = 'Buyer'::text) Filter: (company_id IS NULL) -> Index Scan using index_customs_records_on_buyer_alias_id on customs_records cr (cost=0.00..3572.61 rows=890 width=368) (actual time=5.526..16.042 rows=3 loops=388) Index Cond: (cr.buyer_alias_id = "outer".id) Total runtime: 6382.940 ms (7 rows) the 'slow' one: Merge Right Join (cost=842.53..479378.17 rows=2281 width=410) (actual time=554713.506..555584.825 rows=39 loops=1) Merge Cond: ("outer".supplier_alias_id = "inner".id) -> Index Scan using index_customs_records_on_supplier_alias_id on customs_records cr (cost=0.00..6673133.76 rows=1704859 width=368) (actual time=42.327..555225.588 rows=117424 loops=1) -> Sort (cost=842.53..843.07 rows=218 width=46) (actual time=0.109..0.164 rows=39 loops=1) Sort Key: a.id -> Index Scan using index_aliases_company_type_company_id on aliases a (cost=0.00..834.06 rows=218 width=46) (actual time=0.033..0.074 rows=10 loops=1) Index Cond: ((company_type)::text = 'Supplier'::text) Filter: (company_id IS NULL) Total runtime: 555584.978 ms (9 rows) On Mar 23, 2007, at 4:04 PM, Daniel Cristian Cruz wrote: > Run VACUUM ANALYZE and see if the cost estimates became close to > the effective rows. This could make it faster. > > 2007/3/23, Noah M. Daniels <ndaniels@mac.com>: > > SLOW: > > Merge Right Join (cost=1138.78..460482.84 rows=2993 width=405) > > (actual time=1244745.427..1245714.571 rows=39 loops=1) > > Merge Cond: ("outer".supplier_alias_id = "inner".id) > > -> Index Scan using > index_customs_records_on_supplier_alias_id on > > customs_records cr (cost=0.00..6717806.37 rows=1704859 width=363) > > (actual time=54.567..1245210.707 rows=117424 loops=1) > > -> Sort (cost=1138.78..1139.53 rows=300 width=46) (actual > > time=24.093..24.161 rows=39 loops=1) > > Sort Key: a.id > > -> Index Scan using index_aliases_company_type_company_id > > on aliases a (cost=0.00..1126.44 rows=300 width=46) (actual > > time=22.400..23.959 rows=10 loops=1) > > Index Cond: ((company_type)::text = 'Supplier'::text) > > Filter: (company_id IS NULL) > > Total runtime: 1245714.752 ms > > > > FAST: > > > > Nested Loop Left Join (cost=0.00..603052.46 rows=3244 width=405) > > (actual time=68.526..3115.407 rows=1355 loops=1) > > -> Index Scan using index_aliases_company_type_company_id on > > aliases a (cost= 0.00..639.56 rows=165 width=46) (actual > > time=32.419..132.286 rows=388 loops=1) > > Index Cond: ((company_type)::text = 'Buyer'::text) > > Filter: (company_id IS NULL) > > -> Index Scan using index_customs_records_on_buyer_alias_id on > > customs_records cr (cost=0.00..3639.55 rows=915 width=363) (actual > > time=2.133..7.649 rows=3 loops=388) > > Index Cond: (cr.buyer_alias_id = "outer".id) > > Total runtime: 3117.713 ms > > (7 rows) > > > -- > Daniel Cristian Cruz > Analista de Sistemas
pgsql-performance by date: