I am trying to understand why my query takes a really long time even though I have the required columns indexed:
SELECT entity_id, id, report_date FROM own_inst_detail WHERE ( own_inst_detail.id = 'P7M7WC-S' ) AND ( own_inst_detail.report_date >= '2017-02-01T17:29:49.661Z' ) AND ( own_inst_detail.report_date <= '2018-08-01T17:29:49.663Z' )
The cached result of
EXPLAIN ANALYZE is as follows:
Bitmap Heap Scan on own_inst_detail (cost=20.18..2353.55 rows=597 width=22) (actual time=1.471..6.955 rows=4227 loops=1) Recheck Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date)) Heap Blocks: exact=4182 -> Bitmap Index Scan on own_inst_detail (cost=0.00..20.03 rows=597 width=0) (actual time=0.901..0.901 rows=4227 loops=1) Index Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date)) Planning time: 0.123 ms Execution time: 7.801 ms
This part of the query takes 4 out of 5 total seconds that my full query takes.
I have combined index for id and report_date. I also have two standalone indexes for those columns.
I've tried playing around with setting high work_mem as well as lowering the random_page_cost but nothing really helps.
Any additional suggestions are greatly appreciated.
I found similar question How to index WHERE (start_date >= '2013-12-15') which recommends adding a B-Tree index, but I already do have index for report_date.
Create table script:
CREATE TABLE IF NOT EXISTS public.own_inst_detail ( entity_id character(8) NOT NULL, id character(8) NOT NULL, report_date date NOT NULL, PRIMARY KEY(report_date) );
CREATE INDEX indx_own_inst_detail_report_date_desc ON own_inst_detail (report_date DESC NULLS LAST) CREATE INDEX indx_own_inst_detail_id_report_date_desc ON own_inst_detail (id, report_date DESC NULLS LAST)
CREATE TABLEstatement) and relevant index definitions for performance optimization. And all other relevant details. Consider instructions in the postgresql-performance tag info.
datein the query plan but you use
timestampliterals in the query. And
bpcharseems like a misunderstanding. Neither may be the root of the perf problem, but both may cause problems.
EXPLAIN (ANALYZE, BUFFERS). Better yet, turn on track_io_timing first as well.