0

Consider the following table structure:

create table feed
(
    id          bigserial primary key,
    event_date  timestamp(0) not null,
    ... 
);

create index feed_event_date_index on feed (event_date desc);

And the following query:

explain analyze select * from feed order by event_date desc

Sort  (cost=1.02..1.02 rows=1 width=117) (actual time=0.051..0.052 rows=11 loops=1)
  Sort Key: event_date DESC
  Sort Method: quicksort  Memory: 26kB
  ->  Seq Scan on feed  (cost=0.00..1.01 rows=1 width=117) (actual time=0.032..0.035 rows=11 loops=1)
Planning Time: 0.130 ms
Execution Time: 0.161 ms

Apparently, feed_event_date_index has not been taken into the account.

P.S. table has ~1000 records

My idea is to index table by date, because this is how I'm planning to search records in it in all the queries.

Majesty
  • 2,097
  • 5
  • 24
  • 55
  • 3
    You are selecting all rows, your data is small enough to fit in memory, so it's faster to read all data into memory and sort it, compared to read the index file and then read all the data anyway. – some Aug 02 '23 at 09:50
  • 2
    rows=11, that's not even close to a 1000 records. However, both numbers are really small. Using an index would make your query slower, not faster. – Frank Heikens Aug 02 '23 at 15:20
  • 1
    By the way, have you run `VACUUM FULL ANALYZE;` recently? Analyze updates statistics used by the planner to determine the most efficient way to execute a query. – some Aug 02 '23 at 20:12
  • @FrankHeikens it's an output from the local env, it's the same on production, though. thanks for the answer – Majesty Aug 03 '23 at 06:30
  • @some I didn't, thanks for the guess, will give it a try – Majesty Aug 03 '23 at 06:30
  • If it's the same on your production environment, why do you worry about a query that takes 0.161 millisecond to execute? – Frank Heikens Aug 03 '23 at 18:11
  • @FrankHeikens I have a heavy loaded project and I expect tons of entries to be added to the table in the near future – Majesty Aug 04 '23 at 07:50
  • 1
    @Majesty create a test dataset before. You can’t test query plans on a near empty database, the plans will be different – Frank Heikens Aug 04 '23 at 14:05

0 Answers0