On Thu, Dec 12, 2013 at 3:04 PM, Bryce Covert < br...@brycecovertoperations.com> 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) > > Filter: ((sum(electricusage.usage) >= 3284::numeric) AND > (sum(electricusage.usage) <= 3769::numeric)) > Rows Removed by Filter: 1476 > 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
