1

Is there any way to optimize this query in postgres (version 14)? I have an index on entry_date and have tried to index entry_date::date but it did not seem to matter either.

SELECT
    user_name,
    extract(year from entry_date) as year,
    extract(month from entry_date) as month, 
    extract(day from entry_date) as day,
    extract(hour from entry_date) as hour,
    extract(minute from entry_date) as minute,
    count(*) as cnt
FROM pdq.q_1 
WHERE 
    entry_date > current_timestamp - interval '10' day
GROUP BY
    user_name,
    year,
    month, 
    day,
    hour,
    minute

Here is the explain analyze plan

explain plan

Steve Lloyd
  • 773
  • 2
  • 12
  • 33
  • 1
    Please add the query plan (`EXPLAIN ANALYSE`) – Jim Jones Jun 07 '22 at 13:31
  • 2
    The plan you included is not complete (it has to contain either Index Scan or Seq Scan). However the thing is that if you are selecting significant portion of your data with that WHERE condition, PostgreSQL might ignore your indexes as it assumes it needs to go through the majority of your table data and seq scan might be better for that. Also you might try to update index statistics with `ANALYZE;` if you feel that the index should be used. – Pavel Horal Jun 07 '22 at 13:39

0 Answers0