[PERFORM] Create language plperlu Error

[Om Prakash Jaiswal]

Sir/Madam,Plateform: RHEL6.5,  Postgresql9.4.0.
create extension plperl;
Create language plperl;
I have done following settings:
Perl version 5.10vi 
/etc/ld.so.conf.d/libperl.conf/usr/lib/5.10/multi-thread/i386.../CORE/libperl.soldconfig
ERROR: Can not load "/opt/Postgresql/9.4/lib/postgresql/plperl.so" undefined 
symbol Perl_sv_2bool_flags
ERROR:  could not load library "/opt/PostgreSQL/9.4/lib/postgresql/plperl.so": 
/opt/PostgreSQL/9.2/lib/postgresql/plperl.so: undefined symbol: 
Perl_sv_2bool_flags

How do I solve.
Kindly resolve it.

Regards
Om Prakash

Re: [PERFORM] Very poor performance with Nested Loop Anti Join

[Tom Lane]

Andreas Joseph Krogh  writes:
> This query performs terribly slow (~26 minutes, 1561346.597ms):

Seems like the key misestimation is on the inner antijoin:

>->  Hash Anti Join  (cost=654.21..4008.72 rows=1 width=8) 
> (actual time=9.016..40.672 rows=76174 loops=1)
>  Hash Cond: (il.invoice_id = creditnote.credit_against)
>  ->  Seq Scan on onp_crm_invoice_line il  
> (cost=0.00..3062.01 rows=78001 width=8) (actual time=0.005..11.259 rows=78614 
> loops=1)
>  ->  Hash  (cost=510.56..510.56 rows=11492 width=8) 
> (actual time=8.940..8.940 rows=372 loops=1)
>Buckets: 16384  Batches: 1  Memory Usage: 143kB
>->  Seq Scan on onp_crm_invoice creditnote  
> (cost=0.00..510.56 rows=11492 width=8) (actual time=0.014..7.882 rows=11507 
> loops=1)
>  Filter: ((sent_date <= '2016-06-27'::date) 
> AND ((status_key)::text = 'INVOICE_STATUS_INVOICED'::text))
>  Rows Removed by Filter: 149

If it realized that this produces 78k rows not 1, it'd likely do something
smarter at the outer antijoin.

I have no idea why that estimate's so far off though.  What PG version is
this?  Stats all up to date on these two tables?  Are the rows excluded
by the filter condition on "creditnote" significantly different from the
rest of that table?

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Very poor performance with Nested Loop Anti Join

[Andreas Joseph Krogh]

På mandag 01. august 2016 kl. 15:33:04, skrev Andreas Joseph Krogh <
andr...@visena.com >:
I have this schema:
 
CREATE TABLE onp_crm_person( id serial PRIMARY KEY, onp_user_id bigint 
referencesonp_user(id) deferrable initially deferred, is_resource boolean not 
null default false, UNIQUE(onp_user_id) ); CREATE TABLE onp_crm_activity_log( id
bigserial PRIMARY KEY, relation_id integer REFERENCES 
onp_crm_relation(entity_id), logged_forint references 
onp_crm_person(onp_user_id), durationbigint ); CREATE TABLE onp_crm_invoice( 
entity_idbigint PRIMARY KEY REFERENCES onp_crm_entity(entity_id), status_key 
VARCHAR NOT NULL, credit_against bigint REFERENCES onp_crm_invoice(entity_id), 
sent_dateDATE, UNIQUE(credit_against) deferrable INITIALLY DEFERRED -- 
invoice_print_template_id is added after creation of 
origo_invoice_print_template); CREATE TABLE onp_crm_invoice_line ( id SERIAL 
PRIMARY KEY, invoice_id INTEGER NOT NULL REFERENCES onp_crm_invoice (entity_id) 
);CREATE TABLE onp_crm_calendarentry_invoice_membership( invoice_line_id 
INTEGER NOT NULL REFERENCESonp_crm_invoice_line(id) ON DELETE CASCADE, 
calendar_entry_idINTEGER NOT NULL REFERENCES onp_crm_activity_log(id), unique
(invoice_line_id, calendar_entry_id)DEFERRABLE INITIALLY DEFERRED ); 
 
This query performs terribly slow ( ~26 minutes, 1561346.597ms):
 
explain analyze SELECT  log.relation_id as company_id , sum(log.duration) AS 
durationFROM onp_crm_activity_log log  JOIN onp_crm_person logfor ON 
logfor.onp_user_id =log.logged_for AND logfor.is_resource = FALSE WHERE 1 = 1 
-- Filter out already invoiced before 2016-06-27 AND NOT EXISTS( SELECT * FROM 
onp_crm_calendarentry_invoice_membership cemJOIN onp_crm_invoice_line il ON 
cem.invoice_line_id = il.idJOIN onp_crm_invoice inv ON il.invoice_id = 
inv.entity_idWHERE cem.calendar_entry_id = log.id AND inv.status_key = 
'INVOICE_STATUS_INVOICED' AND inv.sent_date <= '2016-06-27' AND NOT EXISTS( 
SELECT* FROM onp_crm_invoice creditnote WHERE il.invoice_id = 
creditnote.credit_againstAND creditnote.status_key = 'INVOICE_STATUS_INVOICED' 
ANDcreditnote.sent_date <= '2016-06-27' ) ) GROUP BY  log.relation_id ; 
 
Explain output:
                                                                              
                                       QUERY PLAN 

  
 
-
 HashAggregate  (cost=13778.63..13796.39 rows=1421 width=12) (actual 
time=1561343.861..1561344.042 rows=724 loops=1)
   Group Key: log.relation_id
   ->  Nested Loop Anti Join  (cost=741.35..13768.63 rows=2000 width=12) 
(actual time=471.973..1561221.929 rows=96095 loops=1)
 Join Filter: (cem.calendar_entry_id = log.id)
 Rows Removed by Join Filter: 11895758618
 ->  Hash Join  (cost=86.56..9729.03 rows=2000 width=20) (actual 
time=0.170..668.911 rows=181644 loops=1)
   Hash Cond: (log.logged_for = logfor.onp_user_id)
   ->  Seq Scan on onp_crm_activity_log log  (cost=0.00..8930.98 
rows=184398 width=24) (actual time=0.007..538.893 rows=182378 loops=1)
   ->  Hash  (cost=39.46..39.46 rows=3768 width=8) (actual 
time=0.126..0.126 rows=36 loops=1)
 Buckets: 4096  Batches: 1  Memory Usage: 34kB
 ->  Bitmap Heap Scan on onp_crm_person logfor 
 (cost=3.69..39.46 rows=3768 width=8) (actual time=0.040..0.106 rows=36 loops=1)
   Recheck Cond: (onp_user_id IS NOT NULL)
   Filter: (NOT is_resource)
   Rows Removed by Filter: 5
   Heap Blocks: exact=10
   ->  Bitmap Index Scan on onp_crm_person_onp_id_idx 
 (cost=0.00..2.75 rows=41 width=0) (actual time=0.019..0.019 rows=41 loops=1)
 ->  Materialize  (cost=654.79..4009.60 rows=1 width=4) (actual 
time=0.000..2.829 rows=65490 loops=181644)
   ->  Nested Loop  (cost=654.79..4009.59 rows=1 width=4) (actual 
time=9.056..386.835 rows=85668 loops=1)
 ->  Nested Loop  (cost=654.50..4009.27 rows=1 width=8) 
(actual time=9.046..165.280 rows=88151 loops=1)
   ->  Hash Anti Join  (cost=654.21..4008.72 rows=1 
width=8) (actual time=9.016..40.672 rows=76174 loops=1)
 Hash Cond: (il.invoice_id = 
creditnote.credit_against)
 ->  Seq Scan on onp_crm_invoice_line il 
 (cost=0.00..3062.01 rows=78001 width=8) (actual time=0.005..11.259 rows=78614 
loops=1)
 ->  Hash  (cost=510.56..510.56 rows=11492 
width=8) (actual time=8.940..8.940 rows=372 loops=1)
   

[PERFORM] Very poor performance with Nested Loop Anti Join

[Andreas Joseph Krogh]

I have this schema:
 
CREATE TABLE onp_crm_person( id serial PRIMARY KEY, onp_user_id bigint 
referencesonp_user(id) deferrable initially deferred, is_resource boolean not 
null default false, UNIQUE(onp_user_id) ); CREATE TABLE onp_crm_activity_log( id
bigserial PRIMARY KEY, relation_id integer REFERENCES 
onp_crm_relation(entity_id), logged_forint references 
onp_crm_person(onp_user_id), durationbigint ); CREATE TABLE onp_crm_invoice( 
entity_idbigint PRIMARY KEY REFERENCES onp_crm_entity(entity_id), status_key 
VARCHAR NOT NULL, credit_against bigint REFERENCES onp_crm_invoice(entity_id), 
sent_dateDATE, UNIQUE(credit_against) deferrable INITIALLY DEFERRED -- 
invoice_print_template_id is added after creation of 
origo_invoice_print_template); CREATE TABLE onp_crm_invoice_line ( id SERIAL 
PRIMARY KEY, invoice_id INTEGER NOT NULL REFERENCES onp_crm_invoice (entity_id) 
);CREATE TABLE onp_crm_calendarentry_invoice_membership( invoice_line_id 
INTEGER NOT NULL REFERENCESonp_crm_invoice_line(id) ON DELETE CASCADE, 
calendar_entry_idINTEGER NOT NULL REFERENCES onp_crm_activity_log(id), unique
(invoice_line_id, calendar_entry_id)DEFERRABLE INITIALLY DEFERRED ); 
 
This query performs terribly slow ( ~26 minutes, 1561346.597ms):
 
explain analyze SELECT  log.relation_id as company_id , sum(log.duration) AS 
durationFROM onp_crm_activity_log log  JOIN onp_crm_person logfor ON 
logfor.onp_user_id =log.logged_for AND logfor.is_resource = FALSE WHERE 1 = 1 
-- Filter out already invoiced before 2016-06-27 AND NOT EXISTS( SELECT * FROM 
onp_crm_calendarentry_invoice_membership cemJOIN onp_crm_invoice_line il ON 
cem.invoice_line_id = il.idJOIN onp_crm_invoice inv ON il.invoice_id = 
inv.entity_idWHERE cem.calendar_entry_id = log.id AND inv.status_key = 
'INVOICE_STATUS_INVOICED' AND inv.sent_date <= '2016-06-27' AND NOT EXISTS( 
SELECT* FROM onp_crm_invoice creditnote WHERE il.invoice_id = 
creditnote.credit_againstAND creditnote.status_key = 'INVOICE_STATUS_INVOICED' 
ANDcreditnote.sent_date <= '2016-06-27' ) ) GROUP BY  log.relation_id ; 
 
Explain output:
                                                                              
                                       QUERY PLAN 

  
 
-
 HashAggregate  (cost=13778.63..13796.39 rows=1421 width=12) (actual 
time=1561343.861..1561344.042 rows=724 loops=1)
   Group Key: log.relation_id
   ->  Nested Loop Anti Join  (cost=741.35..13768.63 rows=2000 width=12) 
(actual time=471.973..1561221.929 rows=96095 loops=1)
 Join Filter: (cem.calendar_entry_id = log.id)
 Rows Removed by Join Filter: 11895758618
 ->  Hash Join  (cost=86.56..9729.03 rows=2000 width=20) (actual 
time=0.170..668.911 rows=181644 loops=1)
   Hash Cond: (log.logged_for = logfor.onp_user_id)
   ->  Seq Scan on onp_crm_activity_log log  (cost=0.00..8930.98 
rows=184398 width=24) (actual time=0.007..538.893 rows=182378 loops=1)
   ->  Hash  (cost=39.46..39.46 rows=3768 width=8) (actual 
time=0.126..0.126 rows=36 loops=1)
 Buckets: 4096  Batches: 1  Memory Usage: 34kB
 ->  Bitmap Heap Scan on onp_crm_person logfor 
 (cost=3.69..39.46 rows=3768 width=8) (actual time=0.040..0.106 rows=36 loops=1)
   Recheck Cond: (onp_user_id IS NOT NULL)
   Filter: (NOT is_resource)
   Rows Removed by Filter: 5
   Heap Blocks: exact=10
   ->  Bitmap Index Scan on onp_crm_person_onp_id_idx 
 (cost=0.00..2.75 rows=41 width=0) (actual time=0.019..0.019 rows=41 loops=1)
 ->  Materialize  (cost=654.79..4009.60 rows=1 width=4) (actual 
time=0.000..2.829 rows=65490 loops=181644)
   ->  Nested Loop  (cost=654.79..4009.59 rows=1 width=4) (actual 
time=9.056..386.835 rows=85668 loops=1)
 ->  Nested Loop  (cost=654.50..4009.27 rows=1 width=8) 
(actual time=9.046..165.280 rows=88151 loops=1)
   ->  Hash Anti Join  (cost=654.21..4008.72 rows=1 
width=8) (actual time=9.016..40.672 rows=76174 loops=1)
 Hash Cond: (il.invoice_id = 
creditnote.credit_against)
 ->  Seq Scan on onp_crm_invoice_line il 
 (cost=0.00..3062.01 rows=78001 width=8) (actual time=0.005..11.259 rows=78614 
loops=1)
 ->  Hash  (cost=510.56..510.56 rows=11492 
width=8) (actual time=8.940..8.940 rows=372 loops=1)
   Buckets: 16384  Batches: 1  Memory 
Usage: 143kB