Re: Slow query due to slow I/O

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

         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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux