Re: Query Performance Problem

2014-10-21 10:57 GMT-02:00 <john(at)jpm-cola(dot)com>:

>
>
> Hi all,
>
> I'm experimenting with table partitioning though inheritance. I'm testing
> a query as follows:
>
> explain (analyze, buffers)
> select response.id
> from claim.response
> where response.account_id = 4766
> and response.expire_timestamp is null
> and response.create_timestamp >= DATE '2014-08-01'
> order by create_timestamp;
>
> The response table looks like this:
> "account_id";"integer"
> "file_type_id";"integer"
> "receiver_inbound_detail_id";"integer"
> "processing_status_id";"integer"
> "processing";"boolean"
> "expire_timestamp";"timestamp without time zone"
> "last_mod_timestamp";"timestamp without time zone"
> "create_timestamp";"timestamp without time zone"
> "response_trace_nbr";"character varying"
> "posted_timestamp";"timestamp without time zone"
> "need_to_post";"boolean"
> "response_message";"text"
> "worked";"boolean"
> "response_status_id";"integer"
> "response_type_id";"integer"
> "outbound_claim_detail_id";"bigint"
> "id";"bigint"
>
> Here are some rowcounts:
>
> SELECT count(*) from claim_response.response_201408;
> count
> ---------
> 4585746
> (1 row)
>
> Time: 7271.054 ms
> SELECT count(*) from claim_response.response_201409;
> count
> ---------
> 3523370
> (1 row)
>
> Time: 4341.116 ms
> SELECT count(*) from claim_response.response_201410;
> count
> -------
> 154
> (1 row)
>
> Time: 0.258 ms
>
> The entire table has 225,665,512 rows. I read that a partitioning rule of
> thumb is that benefits of partitioning occur starting around 100 million
> rows.
>
> SELECT count(*) from claim.response;
> count
> -----------
> 225665512
> (1 row)
>
> Time: 685064.637 ms
>
>
> The partitioning is on the create_timestamp field.
>
> The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a
> VM machine - 8 GB RAM with 2 CPUs:
>
> Architecture: x86_64
> CPU op-mode(s): 32-bit, 64-bit
> Byte Order: Little Endian
> CPU(s): 2
> On-line CPU(s) list: 0,1
> Thread(s) per core: 1
> Core(s) per socket: 2
> CPU socket(s): 1
> NUMA node(s): 1
> Vendor ID: GenuineIntel
> CPU family: 6
> Model: 44
> Stepping: 2
> CPU MHz: 2660.000
> BogoMIPS: 5320.00
> L1d cache: 32K
> L1i cache: 32K
> L2 cache: 256K
> L3 cache: 12288K
> NUMA node0 CPU(s): 0,1
>
>
>
> 2 users, load average: 0.00, 0.12, 0.37
>
>
> Please see the following for the explain analysis :
>
> http://explain.depesz.com/s/I3SL
>
> I'm trying to understand why I'm getting the yellow, orange, and red on
> the inclusive, and the yellow on the exclusive. (referring to the
> explain.depesz.com/s/I3SL page.)
> I'm relatively new to PostgreSQL, but I've been an Oracle DBA for some
> time. I suspect the I/O may be dragging but I don't know how to dig that
> information out from here. Please point out anything else you can decipher
> from this.
>
> Thanks,
>
> John
>

Hi John,

Dont know about the colors, but the Stats tab looks fine. You've got
yourself 5 Index Scans, which are a very fast way to dig data.

I noticed you've also cast your filter field "(create_timestamp >=
'2014-08-01'::date)". As far as I know, Postgresql doesn't need this kind
of explicit conversion. You would be fine with just "(create_timestamp >=
'2014-08-01')".

Regards,

Felipe