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