Postgres index creation time on temp table

Question

I have a statement trigger that executes a function after some rows are updated. With the transition table of the trigger (joined with some other tables) I need to create a temporary table, which I will use to query later.

The thing is, the query on this temp table is pretty fast but when the number of rows increase it gets slower. So I was trying to add an index. My question is how can I measure the impact of the index creation? If the number of rows is not high, the index is not being used, so I would need to know if the index creation is expensive.

Without showing us the code this is nearly impossible to answer. I would question the need to create a temp table to begin with. Or even a statement level trigger. — user330315
– user330315, Commented Nov 3, 2022 at 20:39
Okey, actually the question is about the impact of the creation of an temporary index. The trigger is constantly executing, so I don't know if creating the index each time would be expensive. — dssof
– dssof, Commented Nov 3, 2022 at 20:59
Build a test case by generating 500K rows or something like that. Then run your process with and without creating the index and evaluate the results. Besides just deciding to build the index or not you can use the results as a guideline for at what number of rows creating the index becomes beneficial. If you have specific issues building and testing then ask as a different question. — Belayer
– Belayer, Commented Nov 3, 2022 at 22:18

Laurenz Albe · Accepted Answer · 2022-11-04 07:12:12Z

1

The cost of creating a b-tree index is dominated by the cost of sorting for bigger tables: O(n * log(n))

For small tables, the cost is low. So I wouldn't worry about creating the index even for small tables where it is not used.

You shouldn't forget to ANALYZE the table, however, because autovacuum doesn't do that for temporary tables.

answered Nov 4, 2022 at 7:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres index creation time on temp table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related