Date: Sun, 29 Apr 2018 09:18:11 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Kaushal Shriyan <kaushalshriyan@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Performance issues while running select sql query
Message-ID: <20180429141811.GP20071@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAD7Ssm-1LsD2yNUCbL3hMFyD4GKo9frx80GK9cOgDmQtXsn-aw@mail.gmail.com>
 <CAD7Ssm94X3zzdxjaSLUiAN-rTtYP5auyBLb+911XMpQscLCLXg@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Sun, Apr 29, 2018 at 10:05:23AM +0530, Kaushal Shriyan wrote:
> # SELECT serorgid,appname,sum(message_count) AS mtrc0,date_trunc('day',
> client_received_start_timestamp at time zone '+5:30:0')::timestamp without
> time zone AS time_unit FROM analytics."test.prod.fact" WHERE
> client_received_start_timestamp >= '2018-3-28 18:30:0' AND
> client_received_start_timestamp < '2018-4-11 18:30:0' AND  ((apiproxy in
> ('test-service' )  ) and (exchangeinstance != '(not set)'  ) and (devemail
> != 'test@example.com'  ) and (devemail != 'srk@example.com'  ) and
> (devemail != 'abc@example.com'  ) and (devemail != 'xyz@example.com'  ) and
> (apistatus = 'Success'  ) and (apiaction not in
> ('LRN','finder','ManuallySelect' )  ) and (appname not in ('Mobile Connect
> Developer Portal (Int(', 'MinskHBM', 'LondonHBM', 'SeoulHBM', 'MumbaiHBM',
> 'NVirginiaHBM','SPauloHBM', 'Mobile Connect HeartBeat Monitor',
> 'PDMAOpenSDKTest1', 'PDMAOpenSDKTest2', 'PDMASDKTest', 'APIHealth',
> 'A1qaDemoApp','test', 'dublin o2o test tool', 'Test from John do not
> provision' )  ) and (serorgid = 'aircel'  ))  GROUP BY
> serorgid,appname,time_unit ORDER BY time_unit DESC LIMIT 14400 OFFSET 0;

This table has inheritence children.  Do they have constraints?  On what
column?  Is constraint_exclusion enabled and working for that?

It looks like test.prod.fact_624 is being read using index in under 1sec, and
the rest using seq scan, taking 5-10sec.

So what are the table+index definitions of the parent and childs (say fact_624
and 631).

Have the child tables been recently ANALYZE ?
Also, have you manually ANALYZE the parent table?

On Sun, Apr 29, 2018 at 10:48:48AM +0530, Kaushal Shriyan wrote:
> >  QUERY PLAN
> >  Limit  (cost=2568002.26..2568038.26 rows=14400 width=35) (actual time=127357.296..127357.543 rows=231 loops=1)
> >    Buffers: shared hit=28019 read=1954681
...

> >                            ->  Index Scan using "test.prod.fact_624_client_received_start_timestamp_idx" on "test.prod.fact_624"  (cost=0.42..10948.27 rows=1002 width=34) (actual time=3.034..278.320 rows=1231 loops=1)
> >                                  Index Cond: ((client_received_start_timestamp >= '2018-03-28 18:30:00'::timestamp without time zone) AND (client_received_start_timestamp < '2018-04-11 18:30:00'::timestamp without time zone))
> >                                  Rows Removed by Filter: 42629
> >                                  Buffers: shared hit=27966 read=498
> >                            ->  Seq Scan on "test.prod.fact_631" (cost=0.00..171447.63 rows=16464 width=34) (actual time=0.070..7565.812 rows=20609 loops=1)
> >                                  Rows Removed by Filter: 645406
> >                                  Buffers: shared hit=2 read=132279
...