0

I've a big partitioned table tbl_VehicleEntry. I've created a function F_GetSysDate() (for compatibility of my function with oracle)

CREATE TABLE tbl_vehicleentry (

vehicleentry_code numeric(12,0) NOT NULL,
shift_date timestamp without time zone NOT NULL,
shift_code numeric(1,0) NOT NULL,
booth_code numeric(2,0) NOT NULL,
.
.
N number of columns);

Partitions like this...

CREATE TABLE tbl_vehicleentry_2016 (

CONSTRAINT tbl_vehicleentry_2016_shift_date_check CHECK (((shift_date >= '2016-01-01'::date) AND (shift_date < '2017-01-01'::date)))

) INHERITS (tbl_vehicleentry);

ALTER TABLE tbl_vehicleentry_2016 OWNER TO tms;

CREATE TABLE tbl_vehicleentry_201701 (

CONSTRAINT tbl_vehicleentry_201701_shift_date_check CHECK (((shift_date >= '2017-01-01'::date) AND (shift_date < '2017-02-01'::date)))

)

INHERITS (tbl_vehicleentry);

ALTER TABLE tbl_vehicleentry_201701 OWNER TO tms;

CREATE TABLE tbl_vehicleentry_201702 (

CONSTRAINT tbl_vehicleentry_201702_shift_date_check CHECK (((shift_date >= '2017-02-01'::date) AND (shift_date < '2017-03-01'::date)))

)

INHERITS (tbl_vehicleentry);

ALTER TABLE tbl_vehicleentry_201702 OWNER TO tms;

CREATE TABLE tbl_vehicleentry_201703 (

CONSTRAINT tbl_vehicleentry_201703_shift_date_check CHECK (((shift_date >= '2017-03-01'::date) AND (shift_date < '2017-04-01'::date)))

)

INHERITS (tbl_vehicleentry);

ALTER TABLE tbl_vehicleentry_201703 OWNER TO tms;

..... And so on month wise partitions for year 2017 onward

-- FUNCTION: public.f_getsysdate()
-- DROP FUNCTION public.f_getsysdate();

CREATE OR REPLACE FUNCTION public.f_getsysdate(
    )
    RETURNS timestamp without time zone
    LANGUAGE 'plpgsql'

    COST 100
    STABLE SECURITY DEFINER 
AS $BODY$

DECLARE
    V_ReturnName   VARCHAR2 ;
BEGIN  
    RETURN current_timestamp::timestamp(0);
END

$BODY$;

ALTER FUNCTION public.f_getsysdate()
    OWNER TO tms;

Now, when I run a query like...

Explain analyze
SELECT MAX(Vehicleentry_Code) FROM tbl_VehicleEntry
WHERE Shift_Date >= f_getsysdate() - 30

Or

Explain analyze
SELECT MAX(Vehicleentry_Code) FROM tbl_VehicleEntry
WHERE Shift_Date >= f_getsysdate() - interval '30' day

I observe, it is scanning through every row of every partition of the table and making it very slow. Below is the explanation

Aggregate (cost=324.08..324.09 rows=1 width=32) -> Append (cost=0.68..323.88 rows=79 width=16)

    ->  Index Scan using isd_tbl_vehicleentry on tbl_vehicleentry  (cost=0.68..4.70 rows=1 width=8)
          Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))


    ->  Bitmap Heap Scan on tbl_vehicleentry_2015  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))


          ->  Bitmap Index Scan on isd_tbl_vehicleentry_2015  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_2016  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_2016  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201701  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201701  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201702  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201702  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201703  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201703  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201704  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201704  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201705  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201705  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201706  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201706  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201707  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201707  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201708  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201708  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201709  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201709  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201710  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201710  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201711  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201711  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201712  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201712  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201801  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201801  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201802  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201802  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201803  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201803  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201804  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201804  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201805  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201805  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201806  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201806  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201807  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201807  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201808  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201808  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201809  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201809  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201810  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201810  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201811  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201811  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

    ->  Bitmap Heap Scan on tbl_vehicleentry_201812  (cost=4.41..12.28 rows=3 width=16)
          Recheck Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

          ->  Bitmap Index Scan on isd_tbl_vehicleentry_201812  (cost=0.00..4.41 rows=3 width=0)
                Index Cond: (shift_date >= (f_getsysdate() - '30 days'::interval day))

**See it has scanned every row of every partition of my postgresql table and making it perform very slow. What could be wrong?

The problem is in function for sure.

Is there any alternative to make it faster? Please help.**

Improve this question
4
  • If you want any response, post the EXPLAIN output as it is and not totally mangled. Commented Jun 1, 2018 at 6:29
  • I've already posted EXPLAIN ANALYZE output.. Please see bottom of the question Commented Jun 1, 2018 at 6:39
  • The important part in my comment is not totally mangled. What you posted is unreadable. Commented Jun 1, 2018 at 6:42
  • Now please check, I've edited it again. Commented Jun 1, 2018 at 6:51

1 Answer 1

Reset to default
1

That is because the value of the function is not known at the time when the query is planned, so the optimizer doesn't know if some partitions can be excluded.

You should first query f_getsysdate(), then construct an SQL statement from the result and execute that. This way the limit will be a constant, and the PostgreSQL optimizer can use it.

PostgreSQL can do better if the function is IMMUTABLE, but based on the name I assume that that is not an option.

Improve this answer
Sign up to request clarification or add additional context in comments.

21 Comments

I did that. In my front end, I take the result of f_getsysdate() in a variable and pass it to the query and it works fine. But the question is, the same query on oracle works blazing fast without getting its result in a variable as explained above. How oracle handles it differently then and why postgres scanning every row of every partition.
Can't answer anything about Oracle, but unless the function is IMMUTABLE, PostgreSQL cannot know the result before it actually executes the function (because it might be different at execution time than at planning time). And it has to plan the query before it executes it. It might be interesting to add a feature that eliminates partitions at execution time, but nobody has written that yet.
So your question is answered.
Yes, like I explained. So don't use the function.
Be warned that PostgreSQL may now cache the result of f_getsysdate() for the life time of the database session if you are using PL/pgSQL functions or prepared statements.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.