Linked Questions

137 votes
5 answers
161k views

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
Joe's user avatar
  • 1,635
50 votes
5 answers
54k views

Aggressive Autovacuum on PostgreSQL

I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows: autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum ...
CadentOrange's user avatar
11 votes
1 answer
5k views

Unexpected Seq Scan when doing query against boolean with value NULL

I have a database column called auto_review where column type is boolean. There is an index for that field, created using the ActiveRecord ORM. CREATE INDEX index_table_on_auto_renew ON table USING ...
Simone Carletti's user avatar
5 votes
3 answers
2k views

PostgreSQL ignores index, runs seq scan

My table contains an index for column total_balance: \d balances_snapshots Table "public.balances_snapshots" Column | Type ...
Andrey Kuznetsov's user avatar
3 votes
3 answers
6k views

Get count estimates from pg_class.reltuples for given conditions

Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'? Currently on my bigger tables the SELECT count(*) query takes long and I ...
Christian Schmitt's user avatar
10 votes
1 answer
4k views

Best index for column where values are mostly the same

We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to ...
whoasked's user avatar
  • 251
7 votes
1 answer
1k views

PostgreSQL partial index unused when created on a table with existing data

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: https://stackoverflow.com/a/...
Jake Feasel's user avatar
5 votes
1 answer
1k views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
tomka's user avatar
  • 937
4 votes
1 answer
687 views

Index on expression (regular expression pattern matching) is not used in query

In my PostgreSQL 12.8 database, I have a relatively simple table the_table with a column value with type varchar: CREATE TABLE public.the_table ( id uuid DEFAULT gen_random_uuid() NOT NULL, ...
Fabian Jäger's user avatar
0 votes
0 answers
58 views

Are unique indexes used to verifiy unicity on insert?

PostgreSQL 13.5, PostGIS 3.2, Windows server 2012 Here is the table I have created CREATE TABLE IF NOT EXISTS activite.unobs_rest ( gid integer NOT NULL, numunobs character varying(20) COLLATE ...
Leehan's user avatar
  • 207