Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Apr 25th, 2012  |  syntax: None  |  size: 5.08 KB  |  hits: 13  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. PostgreSQL query not using index in production
  2. Limit  (cost=41638.15..41638.20 rows=20 width=154) (actual time=159.576..159.581 rows=20 loops=1)
  3.   ->  Sort  (cost=41638.15..41675.10 rows=14779 width=154) (actual time=159.575..159.577 rows=20 loops=1)
  4.         Sort Key: (sum(scenario_ad_group_performances.clicks))
  5.         Sort Method: top-N heapsort  Memory: 35kB
  6.         ->  GroupAggregate  (cost=0.00..41244.89 rows=14779 width=154) (actual time=0.040..151.535 rows=14197 loops=1)
  7.               ->  Nested Loop Left Join  (cost=0.00..31843.75 rows=93800 width=154) (actual time=0.022..82.509 rows=50059 loops=1)
  8.                     ->  Merge Left Join  (cost=0.00..4203.46 rows=14779 width=118) (actual time=0.017..27.103 rows=14197 loops=1)
  9.                           Merge Cond: (scenario_ad_groups.id = scenario_ad_group_vendor_instances.ad_group_id)
  10.                           ->  Index Scan using scenario_ad_groups_pkey on scenario_ad_groups  (cost=0.00..2227.06 rows=14779 width=114) (actual time=0.009..12.085 rows=14197 loops=1)
  11.                                 Filter: (scenario_id = 22)
  12.                           ->  Index Scan using index_scenario_ad_group_vendor_instances_on_ad_group_id on scenario_ad_group_vendor_instances  (cost=0.00..1737.02 rows=27447 width=8) (actual time=0.007..7.021 rows=16528 loops=1)
  13.                                 Filter: (ad_vendor_id = ANY ('{1,2,3}'::integer[]))
  14.                     ->  Index Scan using index_ad_group_performances_on_vendor_instance_id_and_date on scenario_ad_group_performances  (cost=0.00..1.73 rows=11 width=44) (actual time=0.002..0.003 rows=3 loops=14197)
  15.                           Index Cond: ((vendor_instance_id = scenario_ad_group_vendor_instances.id) AND (date >= '2012-02-01'::date) AND (date <= '2012-02-28'::date))
  16. Total runtime: 159.710 ms
  17.        
  18. Limit  (cost=822401.35..822401.40 rows=20 width=179) (actual time=21279.547..21279.591 rows=20 loops=1)
  19.   ->  Sort  (cost=822401.35..822488.42 rows=34828 width=179) (actual time=21279.543..21279.560 rows=20 loops=1)
  20.         Sort Key: (sum(scenario_ad_group_performances.clicks))
  21.         Sort Method: top-N heapsort  Memory: 33kB
  22.         ->  GroupAggregate  (cost=775502.60..821474.59 rows=34828 width=179) (actual time=19126.783..21226.772 rows=34495 loops=1)
  23.               ->  Sort  (cost=775502.60..776739.48 rows=494751 width=179) (actual time=19125.902..19884.164 rows=675253 loops=1)
  24.                     Sort Key: scenario_ad_groups.id
  25.                     Sort Method: external merge  Disk: 94200kB
  26.                     ->  Hash Right Join  (cost=25743.86..596796.70 rows=494751 width=179) (actual time=1155.491..16720.460 rows=675253 loops=1)
  27.                           Hash Cond: (scenario_ad_group_performances.vendor_instance_id = scenario_ad_group_vendor_instances.id)
  28.                           ->  Seq Scan on scenario_ad_group_performances  (cost=0.00..476354.29 rows=4158678 width=44) (actual time=0.043..8949.640 rows=4307019 loops=1)
  29.                                 Filter: ((date >= '2012-02-01'::date) AND (date <= '2012-02-28'::date))
  30.                           ->  Hash  (cost=24047.72..24047.72 rows=51371 width=143) (actual time=1123.896..1123.896 rows=34495 loops=1)
  31.                                 Buckets: 1024  Batches: 16  Memory Usage: 392kB
  32.                                 ->  Hash Right Join  (cost=6625.90..24047.72 rows=51371 width=143) (actual time=92.257..1070.786 rows=34495 loops=1)
  33.                                       Hash Cond: (scenario_ad_group_vendor_instances.ad_group_id = scenario_ad_groups.id)
  34.                                       ->  Seq Scan on scenario_ad_group_vendor_instances  (cost=0.00..11336.31 rows=317174 width=8) (actual time=0.020..451.496 rows=431770 loops=1)
  35.                                             Filter: (ad_vendor_id = ANY ('{1,2,3}'::integer[]))
  36.                                       ->  Hash  (cost=5475.55..5475.55 rows=34828 width=139) (actual time=88.311..88.311 rows=34495 loops=1)
  37.                                             Buckets: 1024  Batches: 8  Memory Usage: 726kB
  38.                                             ->  Bitmap Heap Scan on scenario_ad_groups  (cost=798.20..5475.55 rows=34828 width=139) (actual time=4.451..44.065 rows=34495 loops=1)
  39.                                                   Recheck Cond: (scenario_id = 276)
  40.                                                   ->  Bitmap Index Scan on index_scenario_ad_groups_on_scenario_id  (cost=0.00..789.49 rows=34828 width=0) (actual time=4.232..4.232 rows=37006 loops=1)
  41.                                                         Index Cond: (scenario_id = 276)
  42. Total runtime: 21306.697 ms
  43.        
  44. SELECT A, B
  45. FROM someTable
  46. WHERE A = 'SOME VALUE'
  47.        
  48. ->  Index Scan using index_scenario_ad_group_vendor_instances_on_ad_group_id on scenario_ad_group_vendor_instances  
  49. (cost=0.00..1737.02 rows=27447 width=8)
  50. (actual time=0.007..7.021 rows=16528 loops=1)
  51. Filter: (ad_vendor_id = ANY ('{1,2,3}'::integer[]))
  52.        
  53. ->  Seq Scan on scenario_ad_group_vendor_instances  
  54. (cost=0.00..11336.31 rows=317174 width=8)
  55. (actual time=0.020..451.496 rows=431770 loops=1)
  56. Filter: (ad_vendor_id = ANY ('{1,2,3}'::integer[]))
  57.        
  58. SET enable_seqscan TO 'off'
create a new version of this paste RAW Paste Data