2

PostgreSQL - 13.15. I have a table that is 7 TB in size. There is an index -

CREATE INDEX mytable_cmp_ts ON mytable USING btree (campaign_id, created_at);

I can't try explain analyze for the following query because it may take a long time. An EXPLAIN says the index will be used. Is that possible, considering that created_at is the second column in the index?

   explain select min(created_at) from mytable;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=244894227.70..244894227.71 rows=1 width=8)
   ->  Gather  (cost=244894227.48..244894227.69 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=244893227.48..244893227.49 rows=1 width=8)
               ->  Parallel Index Only Scan using mytable_cmp_ts on mytable  (cost=0.71..238551221.08 rows=2536802560 width=8)

1 Answer 1

6

PostgreSQL can use an index if the first column is not used, but usually that is not very efficient. In the case at hand, the optimizer decided that it would be faster to scan the whole index than the whole table to get all the created_at values, because the index is smaller and most pages are marked all-visible in the visibility map.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.