'Postgres queries takes time while using current_settings()

I am facing a bit weird problem where i am using current_settings session variable in a query which takes quite a long time where as if i replace the same query with direct values it runs very fast.

I checked the plan as well but not getting much of it. I tried to take help of any blog but could not find as such related to it.

Please guide with any blog or some thing so that i could try that.

Query details:

explain(analyze)
select count(*) from customer where start_date < '2022-01-01'
and end_date > '2022-01-01'
and start_date < end_date
and request_id = 0

"Finalize Aggregate  (cost=238870.46..238870.47 rows=1 width=8) (actual time=796.329..798.182 rows=1 loops=1)"
"  ->  Gather  (cost=238870.24..238870.45 rows=2 width=8) (actual time=796.049..798.177 rows=3 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Partial Aggregate  (cost=237870.24..237870.25 rows=1 width=8) (actual time=788.871..788.872 rows=1 loops=3)"
"              ->  Parallel Seq Scan on customer  (cost=0.00..234660.79 rows=1283780 width=0) (actual time=0.015..600.074 rows=3081072 loops=3)"
"                    Filter: ((start_date < '2022-01-01'::date) AND (end_date > '2022-01-01'::date) AND (start_date < end_date) AND (request_id = 0))"
"Planning Time: 0.103 ms"
"Execution Time: 798.215 ms"



explain(analyze) 
select count(*) from customer where start_date <= current_setting('spm.endDate')::date
and end_date > current_setting('spm.effectiveDate')::date
and start_date < end_date
and request_id = 0

"Finalize Aggregate  (cost=296640.55..296640.56 rows=1 width=8) (actual time=4275.817..4277.555 rows=1 loops=1)"
"  ->  Gather  (cost=296640.34..296640.55 rows=2 width=8) (actual time=4259.947..4277.549 rows=3 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Partial Aggregate  (cost=295640.34..295640.35 rows=1 width=8) (actual time=4265.524..4265.524 rows=1 loops=3)"
"              ->  Parallel Seq Scan on customer  (cost=0.00..292430.89 rows=1283780 width=0) (actual time=0.019..4071.333 rows=3081072 loops=3)"
"                    Filter: ((start_date < end_date) AND (request_id = 0) AND (start_date <= (current_setting('spm.endDate'::text))::date) AND (end_date > (current_setting('spm.effectiveDate'::text))::date))"
"Planning Time: 0.132 ms"
"Execution Time: 4277.588 ms"

Thanks in Advance



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source