From: | Guillaume Luchet <g(dot)luchet(at)bilendi(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Need explanation on index size |
Date: | 2020-09-24 13:55:00 |
Message-ID: | D33EA450-73DE-49CA-B563-1ACCF46FC383@bilendi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I’m facing of a comportement I don’t understand on indexes, here a quick example to reproduce my problem
test=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.4 (Debian 12.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
test=# create table plop (col_a int, col_b int, col_c int);
CREATE TABLE
test=# create unique index on plop (col_a);
CREATE INDEX
test=# create index on plop(col_b);
CREATE INDEX
test=# insert into plop (col_a, col_b) select generate_series(1, 10000), generate_series(1, 10000);
INSERT 0 10000
test=# SELECT schemaname, tablename,
pg_size_pretty(SIZE) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) AS SIZE,
pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) AS total_size
FROM pg_tables) AS TABLES where tablename = 'plop';
schemaname | tablename | size_pretty | total_size_pretty
------------+-----------+-------------+-------------------
public | plop | 360 kB | 864 kB
(1 row)
test=# update plop set col_c = floor(random() * 10 + 1)::int;
UPDATE 10000
test=# SELECT schemaname, tablename,
pg_size_pretty(SIZE) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) AS SIZE,
pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) AS total_size
FROM pg_tables) AS TABLES where tablename = 'plop';
schemaname | tablename | size_pretty | total_size_pretty
------------+-----------+-------------+-------------------
public | plop | 792 kB | 2160 kB
(1 row)
test=# reindex table plop;
REINDEX
test=# SELECT schemaname, tablename,
pg_size_pretty(SIZE) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) AS SIZE,
pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) AS total_size
FROM pg_tables) AS TABLES where tablename = 'plop';
schemaname | tablename | size_pretty | total_size_pretty
------------+-----------+-------------+-------------------
public | plop | 792 kB | 1304 kB
(1 row)
I don’t understand why after the update where I only update a non indexed column the indexes size is growing. Is it something someone can explain ?
Regards,
Guillaume
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2020-09-24 14:08:00 | Re: Need explanation on index size |
Previous Message | Олег Самойлов | 2020-09-24 12:22:46 | Re: BUG? Slave don't reconnect to the master |