^^^ This is best answer; however, what is the actual query and how is it used? I assume it’s a analytical query and not actually extracting the rows. Is for a dashboard or an ad-hoc query? Here’s simple example; from 4/1 is uses the index 3/1 it does a full table scan. Depending on what you using the query for you could use a covered index or a materialized view. prod=# create index emp_idx3 on emp (contract_date); CREATE INDEX Time: 6036.030 ms (00:06.036) prod=# select sum(site_id) from emp where contract_date > '4/1/2024'; sum ----------- 927473447 (1 row) Time: 711.774 ms prod=# select sum(site_id) from emp where contract_date > '3/1/2024'; sum ------------ 1128971203 (1 row) Time: 1945.397 ms (00:01.945) prod=# select sum(site_id) from emp where contract_date > '3/1/2024' or contract_date is null; sum ------------ 3823075309 (1 row) Time: 1821.284 ms (00:01.821) prod=# explain select sum(site_id) from emp where contract_date > '4/1/2024'; QUERY PLAN -------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=754070.31..754070.32 rows=1 width=8) -> Gather (cost=754069.59..754070.30 rows=7 width=8) Workers Planned: 7 -> Partial Aggregate (cost=753069.59..753069.60 rows=1 width=8) -> Parallel Bitmap Heap Scan on emp (cost=5343.20..752867.80 rows=80715 width=4) Recheck Cond: (contract_date > '2024-04-01'::date) -> Bitmap Index Scan on emp_idx3 (cost=0.00..5201.95 rows=565002 width=0) Index Cond: (contract_date > '2024-04-01'::date) JIT: Functions: 7 Options: Inlining true, Optimization true, Expressions true, Deforming true (11 rows) Time: 1.196 ms prod=# explain select sum(site_id) from emp where contract_date > '3/1/2024'; QUERY PLAN --------------------------------------------------------------------------------------- Finalize Aggregate (cost=764566.90..764566.91 rows=1 width=8) -> Gather (cost=764566.18..764566.89 rows=7 width=8) Workers Planned: 7 -> Partial Aggregate (cost=763566.18..763566.19 rows=1 width=8) -> Parallel Seq Scan on emp (cost=0.00..763320.15 rows=98411 width=4) Filter: (contract_date > '2024-03-01'::date) JIT: Functions: 7 Options: Inlining true, Optimization true, Expressions true, Deforming true (9 rows) Time: 1.172 ms prod=# drop index emp_idx3; DROP INDEX Time: 8.663 ms prod=# create index emp_idx3 on emp (contract_date) include (site_id); CREATE INDEX Time: 7002.860 ms (00:07.003) prod=# select sum(site_id) from emp where contract_date > '4/1/2024'; sum ----------- 927473447 (1 row) Time: 56.450 ms prod=# select sum(site_id) from emp where contract_date > '3/1/2024'; sum ------------ 1128971203 (1 row) Time: 49.115 ms prod=# select sum(site_id) from emp where contract_date > '3/1/2024' or contract_date is null; sum ------------ 3823075309 (1 row) Time: 702.962 ms prod=# explain select sum(site_id) from emp where contract_date > '3/1/2024' or contract_date is null; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=216035.47..216035.48 rows=1 width=8) -> Gather (cost=216034.74..216035.45 rows=7 width=8) Workers Planned: 7 -> Partial Aggregate (cost=215034.74..215034.75 rows=1 width=8) -> Parallel Index Only Scan using emp_idx3 on emp (cost=0.44..213686.81 rows=539174 width=4) Filter: ((contract_date > '2024-03-01'::date) OR (contract_date IS NULL)) JIT: Functions: 5 Options: Inlining false, Optimization false, Expressions true, Deforming true (9 rows) Time: 0.972 ms prod=# select count(*) from emp; count ---------- 16862243 (1 row) Time: 629.995 ms |
