Re: Slow query due to slow I/O

On Thu, Dec 12, 2013 at 3:04 PM, Bryce Covert <
bryce(at)brycecovertoperations(dot)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