3

We can execute EXPLAIN ANALYZE on a query and on commands like CREATE TABLE but seems like we cannot on a CREATE INDEX.

What is the reason for this? Must one only get statistics about an index only elsewhere?

audlift-bit
  • 163
  • 1
  • 7
  • 1
    Because the execution plan is very simple: read all rows from the table. There isn't much to "plan" for a create index –  Apr 19 '17 at 04:53
  • @a_horse_with_no_name Then how can one measure execution time of how long it took to build an index? – audlift-bit Apr 19 '17 at 23:28
  • You just run it –  Apr 20 '17 at 05:33
  • I mean to say that that's the runtime? What if I strictly wanted execution time taken to build the index?? – audlift-bit Apr 24 '17 at 01:05
  • 1
    @a_horse_with_no_name what about a conditional index. I want to create a conditional index on a table that in production has about 240m rows but the condition will only cover < 1m rows. There is an index on the column that is the condition, will the index creation use this? e.g. CREATE UNIQUE INDEX index_name ON table_name (col1, col2) WHERE col3 = 'blah'; and col3 has an index. – DatabaseShouter Jun 21 '19 at 14:54

1 Answers1

0

To get the time to create an index, you can do the following:

postgres=# select * from now();
postgres=# create index foo on table(col);
postgres=# select * from now();

Type the last command (select * from now()) while the index is being created, be sure to hit enter and it will run this command as soon as the index creation completes. You'll end up with the timestamp just before and just after index creation.

John Major
  • 1
  • 3
    Or simply use `\timing on` in `psql` –  Mar 30 '19 at 22:31
  • Use timeofday() rather than now() if you are going to run this inside a BEGIN/ COMMIT block. Now() always gives you the time at the beginning of the transaction. – DatabaseShouter Jun 21 '19 at 15:14
  • `select clock_timestamp()` or (if not in a transaction) `select now()` or `select current_timestamp`. – RonJohn Jun 23 '23 at 12:23
  • @a_horse_with_no_name it's often handy to know when the `CREATE INDEX` started, if it's a big table. Thus, I do *both* `select current_timestamp` and `\timing`. – RonJohn Jun 23 '23 at 12:25