Re: [PERFORM] Query Performance Problem

[Felipe Santos]

2014-10-21 10:57 GMT-02:00 j...@jpm-cola.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

Re: [PERFORM] Query performance problem

[Tom Lane]

Danilo Mota [EMAIL PROTECTED] writes:
 SELECT
  rc.pkcliente
 FROM r_cliente AS rc
 INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = sc.cpfcnpj;

Surely you meant
  INNER JOIN sav_cliente_lg AS sc ON rc.cpfcnpj = sc.cpfcnpj;

I would also venture that your statistics are desperately out of date,
because if the planner's estimates are close to reality, even this
unconstrained-cross-product join shouldn't have taken that long.
 
regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]