Return to Answer

Data types

The data type character(n) is almost always the wrong choice. It's a "legacy" type I wouldn't use any more. It exhibits surprising behavior and does nothing text / varchar / varchar(n) couldn't do better.

• Any downsides of using data type “text” for storing strings?

And you are mixing type date in the table with timestamp literals in the query. While this works, you should at least provide explicit type declaration to defend against surprising results. Better yet, provide actual dates in the query or cast input explicitly. Like

report_date >= timestamp '2017-02-01T17:29:49.661Z' 

Or:

report_date >= date '2017-02-02' 

The manual about constants and type casts.

Index

Your index indx_own_inst_detail_id_report_date_desc on (id, report_date DESC NULLS LAST) looks good for the query, and the query plan you provided makes good use of it. Execution time: 7.801 ms does not seem too bad either.

If your "uncached" query takes 4 seconds, then you may have to work on your hardware or server configuration or both. Slow storage and not enough RAM for cache? More work_mem is not the cure for this, may even make it worse by taking away RAM from cache memory. Related:

• Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

If you have enough RAM and proper memory settings this might burn down to an issue of cold cache: Only the first invocation is slow (or the first few invocations). If that's a problem, consider pg_prewarm. See:

• PostgreSQL: Force data into memory

If your table is vacuumed enough (or mostly read-only) you may profit from index-only scans if you append the one additional column entity_id in your SELECT list to the index:

CREATE INDEX ON own_inst_detail (id, report_date DESC NULLS LAST, entity_id) 

That might be particularly useful in your situation since Postgres only accesses the index in this case and does not need to visit the table at all. (Eliminating the Bitmap Heap Scan completely.) May help with your disk / cold cache bottleneck.

Related:

• Can Postgres use an index-only scan for this query with joined tables?

Data types

The data type character(n) is almost always the wrong choice. It's a "legacy" type I wouldn't use any more. It exhibits surprising behavior and does nothing text / varchar / varchar(n) couldn't do better.

• Any downsides of using data type “text” for storing strings?

And you are mixing type date in the table with timestamp literals in the query. While this works, you should at least provide explicit type declaration to defend against surprising results. Better yet, provide actual dates in the query or cast input explicitly. Like

report_date >= timestamp '2017-02-01T17:29:49.661Z' 

Or:

report_date >= date '2017-02-02' 

The manual about constants and type casts.

Index

Your index indx_own_inst_detail_id_report_date_desc on (id, report_date DESC NULLS LAST) looks good for the query, and the query plan you provided makes good use of it. Execution time: 7.801 ms does not seem too bad either.

If your "uncached" query takes 4 seconds, then you may have to work on your hardware or server configuration or both. Slow storage and not enough RAM for cache? More work_mem is not the cure for this, may even make it worse by taking away RAM from cache memory. Related:

• Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

If you have enough RAM and proper memory settings this might burn down to an issue of cold cache: Only the first invocation is slow (or the first few invocations). If that's a problem, consider pg_prewarm. See:

• PostgreSQL: Force data into memory

If your table is vacuumed enough (or mostly read-only) you may profit from index-only scans if you append the one additional column entity_id in your SELECT list to the index:

CREATE INDEX ON own_inst_detail (id, report_date DESC NULLS LAST, entity_id) 

That might be particularly useful in your situation since Postgres only accesses the index in this case and does not need to visit the table at all. (Eliminating the Bitmap Heap Scan completely.) May help with your disk / cold cache bottleneck.

Related:

• Can Postgres use an index-only scan for this query with joined tables?

###Data types

The data type character(n) is almost always the wrong choice. It's a "legacy" type I wouldn't use any more. It exhibits surprising behavior and does nothing text / varchar / varchar(n) couldn't do better.

• Any downsides of using data type “text” for storing strings?

And you are mixing type date in the table with timestamp literals in the query. While this works, you should at least provide explicit type declaration to defend against surprising results. Better yet, provide actual dates in the query or cast input explicitly. Like

report_date >= timestamp '2017-02-01T17:29:49.661Z' 

Or:

report_date >= date '2017-02-02' 

The manual about constants and type casts. ###Index Your index indx_own_inst_detail_id_report_date_desc on (id, report_date DESC NULLS LAST) looks good for the query, and the query plan you provided makes good use of it. Execution time: 7.801 ms does not seem too bad either.

If your "uncached" query takes 4 seconds, then you may have to work on your hardware or server configuration or both. Slow storage and not enough RAM for cache? More work_mem is not the cure for this, may even make it worse by taking away RAM from cache memory. Related:

• Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

If you have enough RAM and proper memory settings this might burn down to an issue of cold cache: Only the first invocation is slow (or the first few invocations). If that's a problem, consider pg_prewarm. See:

• PostgreSQL: Force data into memory

If your table is vacuumed enough (or mostly read-only) you may profit from index-only scans if you append the one additional column entity_id in your SELECT list to the index:

CREATE INDEX ON own_inst_detail (id, report_date DESC NULLS LAST, entity_id) 

That might be particularly useful in your situation since Postgres only accesses the index in this case and does not need to visit the table at all. (Eliminating the Bitmap Heap Scan completely.) May help with your disk / cold cache bottleneck.

Related:

• Can Postgres use an index-only scan for this query with joined tables?

Data types

The data type character(n) is almost always the wrong choice. It's a "legacy" type I wouldn't use any more. It exhibits surprising behavior and does nothing text / varchar / varchar(n) couldn't do better.

• Any downsides of using data type “text” for storing strings?

And you are mixing type date in the table with timestamp literals in the query. While this works, you should at least provide explicit type declaration to defend against surprising results. Better yet, provide actual dates in the query or cast input explicitly. Like

report_date >= timestamp '2017-02-01T17:29:49.661Z' 

Or:

report_date >= date '2017-02-02' 

The manual about constants and type casts.

Index

Your index indx_own_inst_detail_id_report_date_desc on (id, report_date DESC NULLS LAST) looks good for the query, and the query plan you provided makes good use of it. Execution time: 7.801 ms does not seem too bad either.

If your "uncached" query takes 4 seconds, then you may have to work on your hardware or server configuration or both. Slow storage and not enough RAM for cache? More work_mem is not the cure for this, may even make it worse by taking away RAM from cache memory. Related:

• Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

If you have enough RAM and proper memory settings this might burn down to an issue of cold cache: Only the first invocation is slow (or the first few invocations). If that's a problem, consider pg_prewarm. See:

• PostgreSQL: Force data into memory

If your table is vacuumed enough (or mostly read-only) you may profit from index-only scans if you append the one additional column entity_id in your SELECT list to the index:

CREATE INDEX ON own_inst_detail (id, report_date DESC NULLS LAST, entity_id) 

That might be particularly useful in your situation since Postgres only accesses the index in this case and does not need to visit the table at all. (Eliminating the Bitmap Heap Scan completely.) May help with your disk / cold cache bottleneck.

Related:

• Can Postgres use an index-only scan for this query with joined tables?

###Data types

The data type character(n) is almost always the wrong choice. It's a "legacy" type I wouldn't use any more. It exhibits surprising behavior and does nothing text / varchar / varchar(n) couldn't do better.

• Any downsides of using data type “text” for storing strings?

And you are mixing type date in the table with timestamp literals in the query. While this works, you should at least provide explicit type declaration to defend against surprising results. Better yet, provide actual dates in the query or cast input explicitly. Like

report_date >= timestamp '2017-02-01T17:29:49.661Z' 

Or:

report_date >= date '2017-02-02' 

The manual about constants and type casts. ###Index Your index indx_own_inst_detail_id_report_date_desc on (id, report_date DESC NULLS LAST) looks good for the query, and the query plan you provided makes good use of it. Execution time: 7.801 ms does not seem too bad either.

If your "uncached" query takes 4 seconds, then you may have to work on your hardware or server configuration or both. Slow storage and not enough RAM for cache? More work_mem is not the cure for this, may even make it worse by taking away RAM from cache memory. Related:

• Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

If you have enough RAM and proper memory settings this might burn down to an issue of cold cache: Only the first invocation is slow (or the first few invocations). If that's a problem, consider pg_prewarm. See:

• PostgreSQL: Force data into memory

If your table is vacuumed enough (or mostly read-only) you may profit from index-only scans if you append the one additional column entity_id in your SELECT list to the index:

CREATE INDEX ON own_inst_detail (id, report_date DESC NULLS LAST, entity_id) 

That might be particularly useful in your situation since Postgres only accesses the index in this case and does not need to visit the table at all. May help with your disk / cold cache bottleneck.

Related:

• Can Postgres use an index-only scan for this query with joined tables?

###Data types

The data type character(n) is almost always the wrong choice. It's a "legacy" type I wouldn't use any more. It exhibits surprising behavior and does nothing text / varchar / varchar(n) couldn't do better.

• Any downsides of using data type “text” for storing strings?

And you are mixing type date in the table with timestamp literals in the query. While this works, you should at least provide explicit type declaration to defend against surprising results. Better yet, provide actual dates in the query or cast input explicitly. Like

report_date >= timestamp '2017-02-01T17:29:49.661Z' 

Or:

report_date >= date '2017-02-02' 

The manual about constants and type casts. ###Index Your index indx_own_inst_detail_id_report_date_desc on (id, report_date DESC NULLS LAST) looks good for the query, and the query plan you provided makes good use of it. Execution time: 7.801 ms does not seem too bad either.

If your "uncached" query takes 4 seconds, then you may have to work on your hardware or server configuration or both. Slow storage and not enough RAM for cache? More work_mem is not the cure for this, may even make it worse by taking away RAM from cache memory. Related:

• Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

If you have enough RAM and proper memory settings this might burn down to an issue of cold cache: Only the first invocation is slow (or the first few invocations). If that's a problem, consider pg_prewarm. See:

• PostgreSQL: Force data into memory

If your table is vacuumed enough (or mostly read-only) you may profit from index-only scans if you append the one additional column entity_id in your SELECT list to the index:

CREATE INDEX ON own_inst_detail (id, report_date DESC NULLS LAST, entity_id) 

That might be particularly useful in your situation since Postgres only accesses the index in this case and does not need to visit the table at all. (Eliminating the Bitmap Heap Scan completely.) May help with your disk / cold cache bottleneck.

Related:

• Can Postgres use an index-only scan for this query with joined tables?