PostgreSQL Indexes

Index creation time

• Hans-Jürgen Schönig, Cybetec, 2025-05-06: CREATE INDEX: Data types matter

SQL> SELECT * FROM t_demo LIMIT 10;
SQL> \timing
SQL> SHOW maintenance_work_mem;

SQL> SELECT tablename, indexname, indexdef
  FROM pg_indexes
 WHERE schemaname = 'public'
ORDER BY tablename, indexname;

SQL> VACUUM ANALYZE;
SQL> CHECKPOINT;

SQL> CREATE INDEX ON t_demo (v1);
SQL> DROP INDEX t_demo_v1_idx;

SQL> select version();

PostgreSQL CLI prompt for psql

• https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PROMPTING
• Prompt 1 (PROMPT1): The normal prompt.
• Prompt 2 (PROMPT1): Prompt when more input is expected during command.
• Prompt 3 (PROMPT3): SQL COPY command and you are expected to type in values on the terminal.

#
# $HOME/.psqlrc
#
\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x\> '

psql --set=PROMPT1='SQL> ' --no-psqlrc

PROMPT1="PROMPT1=%[%033[1;31;40m%]%/%R%[%033[0m%]> "
psql -v "${PROMPT1}" --no-psqlrc