'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 |
|---|
