2

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?

Improve this question
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 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.
    – Erwin Brandstetter
    Apr 29 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 at 5:37

0

Reset to default

Your Answer

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