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 Buffers: shared hit=1 read=685 -> 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? Thanks! Bryce
|
- [PERFORM] Slow query due to slow I/O Bryce Covert
- Re: [PERFORM] Slow query due to slow I/O Claudio Freire
- Re: [PERFORM] Slow query due to slow I/O Bryce Covert
- Re: [PERFORM] Slow query due to slow I/O Claudio Freire
- Re: [PERFORM] Slow query due to slow I/O Bryce Covert
- Re: [PERFORM] Slow query due to slow ... Claudio Freire
- Re: [PERFORM] Slow query due to s... Bryce Covert
- Re: [PERFORM] Slow query due ... Claudio Freire
- Re: [PERFORM] Slow query due to s... Bryce Covert
- Re: [PERFORM] Slow query due ... Jeff Janes
- Re: [PERFORM] Slow query due ... Bryce Covert
- Re: [PERFORM] Slow query due to slow I/O Bryce Covert