cancel
Showing results for 
Search instead for 
Did you mean: 

edb partition pruning problem (edbspl function)

SOLVED
Adventurer

edb partition pruning problem (edbspl function)

Why does not partition-pruning occur in a function written in edbspl? bug?
 
sample
 
1. PPAS ENVIRONMENT
[enterprisedb@KSSDB01:/data/PKSS]
$ psql
 
Current Host Server Date Time : Thu Sep 13 17:36:07 KST 2018
 
psql.bin (9.6.6.11)
Type "help" for help.
 
enterprisedb@[local]:5444:edb(17:36:07)
SQL> show edb_enable_pruning ;
+--------------------+
| edb_enable_pruning |
+--------------------+
| on                 |
+--------------------+
(1 row)
 
Time: 0.208 ms
enterprisedb@[local]:5444:edb(17:36:12)
SQL> show constraint_exclusion ;
+----------------------+
| constraint_exclusion |
+----------------------+
| partition            |
+----------------------+
(1 row)
 
Time: 0.176 ms
enterprisedb@[local]:5444:edb(17:36:18)
SQL> select * from version();
+---------------------------------------------------------------------------------------------------------------+
|                                                    version                                                    |
+---------------------------------------------------------------------------------------------------------------+
| EnterpriseDB 9.6.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit |
+---------------------------------------------------------------------------------------------------------------+
(1 row)
 
Time: 1.205 ms
enterprisedb@[local]:5444:edb(17:36:24)
SQL>
 
2.PARTITION CREATION
CREATE TABLE test (
event_cd number NOT NULL,
dt varchar2(8)
)
PARTITION BY RANGE(dt)
 
(
PARTITION pt_201806 VALUES LESS THAN ('20180101'),
PARTITION pt_201812 VALUES LESS THAN ('20200101'),
PARTITION pt_max VALUES LESS THAN (MAXVALUE)
);
 
insert into test values(1,'20171231');
insert into test values(2,'20191231');
 
commit;
 
select * from test;
 
3.FUNCIONT CREATION IN TWO STYLE
// plpgsql
CREATE  or replace FUNCTION zzz_test(
    a_dt varchar)
  RETURNS varchar AS
$BODY$
declare
l_event_cd number;
BEGIN
   select event_cd into l_event_cd from test where dt=a_dt;
   
  RETURN 'test';
 
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
    END$BODY$
  LANGUAGE plpgsql
  COST 100;
 
 
 
// edbspl
CREATE  or replace FUNCTION zzz_test_edb(
    a_dt varchar)
  RETURNS varchar AS
$BODY$
declare
l_event_cd number;
BEGIN
   select event_cd into l_event_cd from test where dt=a_dt;
   
  RETURN 'test';
 
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
    END$BODY$
  LANGUAGE edbspl
  COST 100;
 
 
4.PRUNING RESULT 
postgresql.conf
.....
auto_explain.log_min_duration=0
auto_explain.log_nested_statements=true
....
 
// plpgsql function
select /* superman */ * from zzz_test('20171231');   -- table partition pruning good!!
2018-09-13 15:55:37 KST [22161]: [26-1] db=dhbsc,user=mig,app=[Postgres Enterprise Manager - Query Tool],host=[192.168.0.1] LOG:  duration: 0.011 ms  plan:
        Query Text: select event_cd                 from test where dt=a_dt
        Append  (cost=0.00..20.75 rows=5 width=32) (actual time=0.008..0.008 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on mig.test  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                Output: test.event_cd
                Filter: ((test.dt)::text = '20171231'::text)
          ->  Seq Scan on mig.test_pt_201806  (cost=0.00..20.75 rows=4 width=32) (actual time=0.006..0.006 rows=1 loops=1)
                Output: test_pt_201806.event_cd
                Filter: ((test_pt_201806.dt)::text = '20171231'::text)
                Buffers: shared hit=1
2018-09-13 15:55:37 KST [22161]: [27-1] db=dhbsc,user=mig,app=[Postgres Enterprise Manager - Query Tool],host=[192.168.0.1] CONTEXT:  SQL statement "select event_cd                 from test where dt=a_dt"
        PL/pgSQL function zzz_test(character varying) line 5 at SQL statement
2018-09-13 15:55:37 KST [22161]: [28-1] db=dhbsc,user=mig,app=[Postgres Enterprise Manager - Query Tool],host=[192.168.0.1] LOG:  duration: 0.281 ms  plan:
        Query Text: ^M
        select /* superman */ from zzz_test('20171231');
        Function Scan on mig.zzz_test  (cost=0.25..0.26 rows=1 width=0) (actual time=0.277..0.277 rows=1 loops=1)
          Function Call: zzz_test('20171231'::character varying)
          Buffers: shared hit=1
 
// edbspl function
select /* spiderman */ * from zzz_test_edb('20171231');  -- table partition pruning not working !!!!
2018-09-13 15:57:16 KST [22161]: [35-1] db=dhbsc,user=mig,app=[Postgres Enterprise Manager - Query Tool],host=[192.168.0.1] LOG:  duration: 0.020 ms  plan:
        Query Text: select event_cd                 from test where dt=a_dt
        Append  (cost=0.00..62.25 rows=13 width=32) (actual time=0.011..0.016 rows=1 loops=1)
          Buffers: shared hit=2
          ->  Seq Scan on mig.test  (cost=0.00..0.00 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)
                Output: test.event_cd
                Filter: ((test.dt)::text = ($1)::text)
          ->  Seq Scan on mig.test_pt_201806  (cost=0.00..20.75 rows=4 width=32) (actual time=0.009..0.010 rows=1 loops=1)
                Output: test_pt_201806.event_cd
                Filter: ((test_pt_201806.dt)::text = ($1)::text)
                Buffers: shared hit=1
          ->  Seq Scan on mig.test_pt_201812  (cost=0.00..20.75 rows=4 width=32) (actual time=0.003..0.003 rows=0 loops=1)
                Output: test_pt_201812.event_cd
                Filter: ((test_pt_201812.dt)::text = ($1)::text)
                Rows Removed by Filter: 1
                Buffers: shared hit=1
          ->  Seq Scan on mig.test_pt_max  (cost=0.00..20.75 rows=4 width=32) (actual time=0.000..0.000 rows=0 loops=1)
                Output: test_pt_max.event_cd
                Filter: ((test_pt_max.dt)::text = ($1)::text)
2018-09-13 15:57:16 KST [22161]: [36-1] db=dhbsc,user=mig,app=[Postgres Enterprise Manager - Query Tool],host=[192.168.0.1] CONTEXT:  SQL statement "select event_cd                 from test where dt=a_dt"
        edb-spl function zzz_test_edb(character varying) line 5 at SQL statement
2018-09-13 15:57:16 KST [22161]: [37-1] db=dhbsc,user=mig,app=[Postgres Enterprise Manager - Query Tool],host=[192.168.0.1] LOG:  duration: 0.681 ms  plan:
        Query Text: select /* spiderman */ * from zzz_test_edb('20171231');
        Function Scan on mig.zzz_test_edb  (cost=0.25..0.26 rows=1 width=32) (actual time=0.670..0.670 rows=1 loops=1)
          Output: zzz_test_edb
          Function Call: zzz_test_edb('20171231'::character varying)
          Buffers: shared hit=9
2 ACCEPTED SOLUTIONS

Accepted Solutions
EDB Team Member

Re: edb partition pruning problem (edbspl function)

Hello,

 

We have created Redmine case for this issue.

We will update you once we get fix. 

Thank you.

 

Regards

Siva.

EDB Team Member

Re: edb partition pruning problem (edbspl function)

Hello, 

 

Hope you are doing good. 

 

This is regarding partition pruning which you reported. Below is further update on this issue. 

 

About Fix, "Since there is small behavior change in the area of immutable function, We have not back-ported these changes beyond v11. due to some customer applications already dependent on existing behaviour. So, We are releasing this fix along with major coming version v11."

 

Thanks & Regards,

Siva.

3 REPLIES
EDB Team Member

Re: edb partition pruning problem (edbspl function)

Hello, 

 

We can able to reproduce this issue from our end as well. Currently we are escalating this issue to our engineering team,

Will update you their feedback very soon.

 

Regards

Siva. 

 

 

EDB Team Member

Re: edb partition pruning problem (edbspl function)

Hello,

 

We have created Redmine case for this issue.

We will update you once we get fix. 

Thank you.

 

Regards

Siva.

EDB Team Member

Re: edb partition pruning problem (edbspl function)

Hello, 

 

Hope you are doing good. 

 

This is regarding partition pruning which you reported. Below is further update on this issue. 

 

About Fix, "Since there is small behavior change in the area of immutable function, We have not back-ported these changes beyond v11. due to some customer applications already dependent on existing behaviour. So, We are releasing this fix along with major coming version v11."

 

Thanks & Regards,

Siva.