How do I speed up counting rows in a PostgreSQL table?

For a very quick estimate: SELECT reltuples FROM pg_class WHERE relname=”my_table”; There are several caveats, though. For one, relname is not necessarily unique in pg_class. There can be multiple tables with the same relname in multiple schemas of the database. To be unambiguous: SELECT reltuples::bigint FROM pg_class WHERE oid = ‘my_schema.my_table’::regclass; If you do not … Read more

How does PostgreSQL perform ORDER BY with a b-tree index on the field?

For a simple query like this Postgres will use an index scan and retrieve readily sorted tuples from the index in order. Due to its MVCC model Postgres had to always visit the “heap” (data pages) additionally to verify entries are actually visible to the current transaction. Quoting the Postgres Wiki on index-only scans: PostgreSQL … Read more

Function executes faster without STRICT modifier?

Maybe an overhead from the repeated function call that is streamlined away by inlining the function? That’s what I’d guess. You’ve got a very simple expression there. An actual function-call presumably involves stack setup, passing parameters etc. The test below gives run-times of 5ms for inlined and 50ms for strict. BEGIN; CREATE SCHEMA f; SET … Read more

Multicolumn index on 3 fields with heterogenous data types

Single-column index Postgres can combine multiple indexes very efficiently in a single query with bitmap index scans. Most of the time, the most selective index is picked (or two, combined with bitmap index scans) and the rest is filtered. Once the result set is narrow enough, it’s not efficient to scan more indexes. Multicolumn index … Read more

Postgres query optimization (forcing an index scan)

For testing purposes you can force the use of the index by “disabling” sequential scans – best in your current session only: SET enable_seqscan = OFF; Do not use this on a productive server. Details in the manual here. I quoted “disabling”, because you cannot actually disable sequential table scans. But any other available option … Read more

Optimize Postgres query on timestamp range

CLUSTER If you intend to use CLUSTER, the displayed syntax is invalid. create CLUSTER ticket USING ticket_1_idx; Run once: CLUSTER ticket USING ticket_1_idx; This can help a lot with bigger result sets. Not so much for a single or few rows returned. Postgres remembers which index to use for subsequent calls. If your table isn’t … Read more