Nested Loops

2018-01-30 Thread Kumar, Virendra 
Can somebody help me avoid nested loops in below query:
--
ap_poc_db=# explain (analyze,buffers)
ap_poc_db-# select site_id, account_id FROM ap.site_exposure se
ap_poc_db-# WHERE se.portfolio_id=-1191836
ap_poc_db-# AND EXISTS (select 1 from ap.catevent_flood_sc_split sp 
where sp.migration_sourcename= 'KatRisk_SC_Flood_2015_v9' AND 
ST_Intersects(se.shape, sp.shape))
ap_poc_db-# group by site_id, account_id;

  QUERY PLAN
--
Group  (cost=23479854.04..23479880.06 rows=206 width=16) (actual 
time=1387.825..1389.134 rows=1532 loops=1)
   Group Key: se.site_id, se.account_id
   Buffers: shared hit=172041
   ->  Gather Merge  (cost=23479854.04..23479879.04 rows=205 width=16) (actual 
time=1387.823..1388.676 rows=1532 loops=1)
 Workers Planned: 5
 Workers Launched: 5
 Buffers: shared hit=172041
 ->  Group  (cost=23478853.96..23478854.27 rows=41 width=16) (actual 
time=1346.044..1346.176 rows=255 loops=6)
   Group Key: se.site_id, se.account_id
   Buffers: shared hit=864280
   ->  Sort  (cost=23478853.96..23478854.07 rows=41 width=16) 
(actual time=1346.041..1346.079 rows=255 loops=6)
 Sort Key: se.site_id, se.account_id
 Sort Method: quicksort  Memory: 37kB
 Buffers: shared hit=864280
 ->  Nested Loop Semi Join  (cost=4.53..23478852.87 rows=41 
width=16) (actual time=34.772..1345.489 rows=255 loops=6)
   Buffers: shared hit=864235
   ->  Append  (cost=0.00..156424.56 rows=123645 
width=48) (actual time=1.011..204.748 rows=102990 loops=6)
 Buffers: shared hit=154879
 ->  Parallel Seq Scan on site_exposure_1191836 
se  (cost=0.00..156424.56 rows=123645 width=48) (actual time=1.004..187.702 
rows=102990 loops=6)
   Filter: (portfolio_id = 
'-1191836'::integer)
   Buffers: shared hit=154879
   ->  Bitmap Heap Scan on catevent_flood_sc_split sp  
(cost=4.53..188.54 rows=15 width=492) (actual time=0.007..0.007 rows=0 
loops=617937)
 Recheck Cond: (se.shape && shape)
 Filter: ((migration_sourcename = 
'KatRisk_SC_Flood_2015_v9'::bpchar) AND _st_intersects(se.shape, shape))
 Rows Removed by Filter: 0
 Heap Blocks: exact=1060
 Buffers: shared hit=709356
 ->  Bitmap Index Scan on 
catevent_flood_sc_split_shape_mig_src_gix  (cost=0.00..4.52 rows=45 width=0) 
(actual time=0.005..0.005 rows=0 loops=617937)
   Index Cond: (se.shape && shape)
   Buffers: shared hit=691115
Planning time: 116.141 ms
Execution time: 1391.785 ms
(32 rows)


ap_poc_db=#

Thank you in advance!


Regards,
Virendra




This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.

Re: 8.2 Autovacuum BUG ?

2018-01-30 Thread Claudio Freire 
On Tue, Jan 30, 2018 at 10:55 AM, pavan95  wrote:
> Hello all,
>
> Will a sudden restart(stop/start) of a postgres database will generate this
> huge WAL?

Shouldn't

Re: SV: pgaudit and create postgis extension logs a lot inserts

2018-01-30 Thread Bruce Momjian 
On Fri, Jan 19, 2018 at 11:03:42AM +, Svensson Peter wrote:
> 
> A test to create postgis extension made 4 rsyslog processes run for several 
> minutes with high cpu util,
> and when you have only 8 cpu:s this take lot of resources. 
> The create command also have to wait until all the log are written so there 
> are great impact.
> Log file got 16 GB big only for this.

Uh, that seems odd.  Is rsyslog fsync'ing each write?  You should check
the docs on that.  Here is an example report:

http://kb.monitorware.com/simple-question-what-does-the-dash-t10237.html

I don't see the dash behavior mentioned in my Debian Jessie rsyslogd
manual page though.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: 8.2 Autovacuum BUG ?

2018-01-30 Thread pavan95 
Hello all,

Will a sudden restart(stop/start) of a postgres database will generate this
huge WAL?

Regards,
Pavan








--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html