3

While optimizing my query in Postgres, I noted that creating an index is always very fast, less than 1 second on a table with millions of rows.

But if there's no index, a query would take a few minutes or even more!

In my mind, when creating an index, Postgres has to scan all data of the columns those need to be indexed on at least once. So this operation should be similar to a query with a filter on the same columns that needs a sequence scan on a table.

Why is creating an index dramatically faster than a sequence scanning query?

3
  • I can assure you, index creation is not always fast. You will not to provide a reproducible example for any hope at a meaningful answer.
    – jjanes
    Apr 27, 2023 at 18:13
  • I am puzzled by the speed of creating an index, compared to queries based on a seq scan at times, too. The index has to be registered in system tables and persisted, no less. Apr 29, 2023 at 0:06
  • Perhaps it does the timeconsuming work in the background? Could you actually use the new index immediately after you "created" it? Or did a query still do a table scan?
    – Rick James
    May 23, 2023 at 5:37

1 Answer 1

-1

Usually create a index on one column is much faster than you thought it should be.

For example when you create an index on User.name, usually the column will not exceed 31bytes and row will not exceed 100bytes(20bytes for username, 31bytes for email, 4bytes for id, 40 bytes for password) and if there were 1 million records in your table, the size to read will be 100 * 1000000 / 1024 / 1024 = 95 MiB.

Which is quite small compared to the io speed (usually 100MiB/s).

3
  • Postgres reads and writes pages, not individual bytes.
    – mustaccio
    Feb 29 at 12:53
  • Thanks, I change the column to row.
    – ramwin
    Mar 1 at 3:02
  • Thanks, but the page can contain more than one row...
    – mustaccio
    Mar 1 at 3:03

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.