From: | Chris Sterritt <chris(dot)sterritt(at)yobota(dot)xyz> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Need explanation on index size |
Date: | 2020-09-24 14:22:39 |
Message-ID: | 9d3eeeb0-f76e-d0b8-1f78-c7bb2083e9b2@yobota.xyz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 24/09/2020 15:08, Guillaume Lelarge wrote:
> Hi,
>
> Le jeu. 24 sept. 2020 à 15:55, Guillaume Luchet <g(dot)luchet(at)bilendi(dot)com
> <mailto:g(dot)luchet(at)bilendi(dot)com>> a écrit :
>
> 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 ?
>
>
> Every tuple is now on a different location on the table (remember that
> update in PostgreSQL is more something like delete+insert). So even if
> the value of the column doesn't change, its tuple location changes, so
> the index needs to be updated to reflect that change.
>
>
> --
> Guillaume.
If you execute
vacuum full plop;
you will see the size shrink back as the dead tuples will have been removed.
Chris
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2020-09-24 15:41:31 | Re: Need explanation on index size |
Previous Message | Guillaume Lelarge | 2020-09-24 14:08:00 | Re: Need explanation on index size |