On Thu, Dec 12, 2013 at 3:04 PM, Bryce Covert <bryce@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Not sure if this is helpful, but I tried upgrading to 9.2, and here's what I got:
---------
Limit (cost=0.00..535.78 rows=50 width=8) (actual time=1037.376..135043.945 rows=50 loops=1)
Output: premiseaccount.id, (sum(electricusage.usage))
Buffers: shared hit=4851 read=18718
-> GroupAggregate (cost=0.00..198012.28 rows=18479 width=8) (actual time=1037.369..135043.700 rows=50 loops=1)
Output: premiseaccount.id, sum(electricusage.usage)Rows Removed by Filter: 1476
Filter: ((sum(electricusage.usage) >= 3284::numeric) AND (sum(electricusage.usage) <= 3769::numeric))
Buffers: shared hit=4851 read=18718
-> Nested Loop (cost=0.00..196247.46 rows=148764 width=8) (actual time=107.092..134845.231 rows=15188 loops=1)
Output: premiseaccount.id, electricusage.usage
Buffers: shared hit=4851 read=18718
-> Index Only Scan using premiseaccount_bucket_58c70392619aa36f on public.premiseaccount premiseaccount (cost=0.00..43135.13 rows=18479 width=4) (actual time=45.368..137.340 rows=1527 loops=1)
Output: premiseaccount.bucket, premiseaccount.id
Index Cond: (premiseaccount.bucket = '85375_single-family'::text)
Heap Fetches: 1527
You had to hit the heap for every row, meaning the index-only feature was useless. Are you vacuuming enough? How fast does this table change? What is relallvisible from pg_class for these tables?
-> Index Scan using electricusage_premise_account_id on public.electricusage electricusage (cost=0.00..8.20 rows=9 width=8) (actual time=22.306..88.136 rows=10 loops=1527)
Output: electricusage.id, electricusage.created, electricusage.modified, electricusage.from_date, electricusage.to_date, electricusage.usage, electricusage.demand, electricusage.bill_amount, electricusage.premise_account_id
Index Cond: (electricusage.premise_account_id = premiseaccount.id)
Filter: (electricusage.from_date >= '2012-11-20 00:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 2
Buffers: shared hit=4850 read=18033
Total runtime: 135044.256 ms
(23 rows)
Looks like it is doing an index only scan for the first table, but not for the second. I tried creating two indexes that theoretically should make it not have to go to the physical table.:
"electricusage_premise_account_id_36bc8999ced10059" btree (premise_account_id, from_date, usage)
"ix_covered_2" btree (premise_account_id, from_date DESC, usage, id)
Any idea why it's not using that?
If the other IOS in this plan is anything to go by, then your table doesn't have enough all-visible pages to make it worthwhile. So it chooses the smaller index, instead of the bigger one that could theoretically support an IOS.
Cheers,
Jeff
