Thread: Slow index performance
Hi all,
we have a strange performance issue in one of our databases (using PostgreSQL 9.1.18). Maybe you can help me understand
what’sgoing on.
We have two identical tables (rec_isins_current, rec_isins_archive) with the following structure:
Table "ts_frontend.rec_isins_current"
Column | Type | Modifiers
------------+---------+-----------
attachment | integer | not null
isin | isin | not null
Indexes:
"rec_isins_current_pkey" PRIMARY KEY, btree (attachment, isin), tablespace "extra"
"rec_isins_current_attachment" btree (attachment), tablespace "extra"
Foreign-key constraints:
"rec_isins_attachment_fkey" FOREIGN KEY (attachment) REFERENCES ts_frontend.attachments(id) ON UPDATE RESTRICT ON
DELETECASCADE
Inherits: ts_frontend.rec_isins
The isin type is a domain type which has char(12) as its base type.
Both tables inherit from ts_frontend.rec_isins, which is empty and is only used to search both tables in a single
query.
When we search for an isin in both tables (using the parent table, but the behavior is the same if we directly search
inone of the tables), the primary key index is used. However, while the archive table is pretty fast, the current table
ismuch slower:
# explain analyze select * from ts_frontend.rec_isins where isin = 'foo';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..565831.43 rows=501 width=17) (actual time=6080.778..6080.778 rows=0 loops=1)
-> Append (cost=0.00..565831.43 rows=501 width=17) (actual time=6080.777..6080.777 rows=0 loops=1)
-> Seq Scan on rec_isins (cost=0.00..0.00 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((isin)::bpchar = 'foo'::bpchar)
-> Index Scan using rec_isins_archive_pkey on rec_isins_archive rec_isins (cost=0.00..621.61 rows=405
width=17)(actual time=10.335..10.335 rows=0 loops=1)
Index Cond: ((isin)::bpchar = 'foo'::bpchar)
-> Index Scan using rec_isins_current_pkey on rec_isins_current rec_isins (cost=0.00..565209.82 rows=95
width=17)(actual time=6070.440..6070.440 rows=0 loops=1)
Index Cond: ((isin)::bpchar = 'foo'::bpchar)
Total runtime: 6080.824 ms
This is strange, because the archive table is four times larger than the current table and the archive index is also
fourtimes larger than the current index:
relname | relfilenode | reltablespace | pg_table_size
------------------------------+-------------+---------------+---------------
rec_isins | 514533886 | 0 | 8192
rec_isins_pkey | 514533892 | 0 | 8192
rec_isins_attachment | 514533899 | 0 | 8192
------------------------------+-------------+---------------+---------------
rec_isins_archive | 507194804 | 0 | 10923393024
rec_isins_archive_pkey | 507197615 | 139300915 | 9048784896
rec_isins_archive_attachment | 507197692 | 139300915 | 4706050048
------------------------------+-------------+---------------+---------------
rec_isins_current | 631621090 | 0 | 2696216576
rec_isins_current_pkey | 631621096 | 139300915 | 2098552832
rec_isins_current_attachment | 631621107 | 139300915 | 1160683520
Both tables are in the same tablespace (and thus on the same disk) and both indexes are also in the same tablespace
(butin another than the tables).
The current table has been vacuumed full and reindexed.
Can anybody explain the difference? Why is the current table so slow? And what can we do to improve performance?
Thanks for your help,
Christian
------------------------------------------------------------
Deriva GmbH Financial IT and Consulting
Christian Schröder
Geschäftsführer
Hans-Böckler-Straße 2 | D-37079 Göttingen
Tel: +49 (0)551 489 500-42
Fax: +49 (0)551 489 500-91
http://www.deriva.de
Amtsgericht Göttingen | HRB 3240
Geschäftsführer: Christian Schröder
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Christian Schröder > Sent: Freitag, 3. Juli 2015 07:36 > To: pgsql-general@postgresql.org > Subject: [GENERAL] Slow index performance > > Hi all, > we have a strange performance issue in one of our databases (using > PostgreSQL 9.1.18). Maybe you can help me understand what’s going on. > > We have two identical tables (rec_isins_current, rec_isins_archive) > with the following structure: > > Table "ts_frontend.rec_isins_current" > Column | Type | Modifiers > ------------+---------+----------- > attachment | integer | not null > isin | isin | not null > Indexes: > "rec_isins_current_pkey" PRIMARY KEY, btree (attachment, isin), > tablespace "extra" > "rec_isins_current_attachment" btree (attachment), tablespace > "extra" Hello, Are you sure that the column order of the PKs is the same in both tables? (attachment, isin) or (isin, attachment). When isin is at the second place, Postgres will read the whole index to find matching records. regards, Marc Mamin > Foreign-key constraints: > "rec_isins_attachment_fkey" FOREIGN KEY (attachment) REFERENCES > ts_frontend.attachments(id) ON UPDATE RESTRICT ON DELETE CASCADE > Inherits: ts_frontend.rec_isins > > The isin type is a domain type which has char(12) as its base type. > Both tables inherit from ts_frontend.rec_isins, which is empty and is > only used to search both tables in a single query. > > When we search for an isin in both tables (using the parent table, but > the behavior is the same if we directly search in one of the tables), > the primary key index is used. However, while the archive table is > pretty fast, the current table is much slower: > > # explain analyze select * from ts_frontend.rec_isins where isin = > 'foo'; > > QUERY PLAN > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ------------------------------- > Result (cost=0.00..565831.43 rows=501 width=17) (actual > time=6080.778..6080.778 rows=0 loops=1) > -> Append (cost=0.00..565831.43 rows=501 width=17) (actual > time=6080.777..6080.777 rows=0 loops=1) > -> Seq Scan on rec_isins (cost=0.00..0.00 rows=1 width=36) > (actual time=0.001..0.001 rows=0 loops=1) > Filter: ((isin)::bpchar = 'foo'::bpchar) > -> Index Scan using rec_isins_archive_pkey on > rec_isins_archive rec_isins (cost=0.00..621.61 rows=405 width=17) > (actual time=10.335..10.335 rows=0 loops=1) > Index Cond: ((isin)::bpchar = 'foo'::bpchar) > -> Index Scan using rec_isins_current_pkey on > rec_isins_current rec_isins (cost=0.00..565209.82 rows=95 width=17) > (actual time=6070.440..6070.440 rows=0 loops=1) > Index Cond: ((isin)::bpchar = 'foo'::bpchar) Total > runtime: 6080.824 ms > > This is strange, because the archive table is four times larger than > the current table and the archive index is also four times larger than > the current index: > > relname | relfilenode | reltablespace | > pg_table_size > ------------------------------+-------------+---------------+---------- > - > ------------------------------+-------------+---------------+---- > rec_isins | 514533886 | 0 | > 8192 > rec_isins_pkey | 514533892 | 0 | > 8192 > rec_isins_attachment | 514533899 | 0 | > 8192 > ------------------------------+-------------+---------------+---------- > - > ------------------------------+-------------+---------------+---- > rec_isins_archive | 507194804 | 0 | > 10923393024 > rec_isins_archive_pkey | 507197615 | 139300915 | > 9048784896 > rec_isins_archive_attachment | 507197692 | 139300915 | > 4706050048 > ------------------------------+-------------+---------------+---------- > - > ------------------------------+-------------+---------------+---- > rec_isins_current | 631621090 | 0 | > 2696216576 > rec_isins_current_pkey | 631621096 | 139300915 | > 2098552832 > rec_isins_current_attachment | 631621107 | 139300915 | > 1160683520 > > Both tables are in the same tablespace (and thus on the same disk) and > both indexes are also in the same tablespace (but in another than the > tables). > The current table has been vacuumed full and reindexed. > > Can anybody explain the difference? Why is the current table so slow? > And what can we do to improve performance? > > Thanks for your help, > Christian > > ------------------------------------------------------------ > Deriva GmbH Financial IT and Consulting > Christian Schröder > Geschäftsführer > Hans-Böckler-Straße 2 | D-37079 Göttingen > Tel: +49 (0)551 489 500-42 > Fax: +49 (0)551 489 500-91 > http://www.deriva.de > > Amtsgericht Göttingen | HRB 3240 > Geschäftsführer: Christian Schröder > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To > make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
You are right ... How embarrassing ... Why did I not see this? I will change the index and check again. I guess that the problem should be fixed then. Thanks a lot! Christian ------------------------------------------------------------ Deriva GmbH Financial IT and Consulting Christian Schröder Geschäftsführer Hans-Böckler-Straße 2 | D-37079 Göttingen Tel: +49 (0)551 489 500-42 Fax: +49 (0)551 489 500-91 http://www.deriva.de Amtsgericht Göttingen | HRB 3240 Geschäftsführer: Christian Schröder -----Ursprüngliche Nachricht----- Von: Marc Mamin [mailto:M.Mamin@intershop.de] Gesendet: Freitag, 3. Juli 2015 08:58 An: Christian Schröder; pgsql-general@postgresql.org Betreff: RE: [GENERAL] Slow index performance > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Christian Schröder > Sent: Freitag, 3. Juli 2015 07:36 > To: pgsql-general@postgresql.org > Subject: [GENERAL] Slow index performance > > Hi all, > we have a strange performance issue in one of our databases (using > PostgreSQL 9.1.18). Maybe you can help me understand what’s going on. > > We have two identical tables (rec_isins_current, rec_isins_archive) > with the following structure: > > Table "ts_frontend.rec_isins_current" > Column | Type | Modifiers > ------------+---------+----------- > attachment | integer | not null > isin | isin | not null > Indexes: > "rec_isins_current_pkey" PRIMARY KEY, btree (attachment, isin), > tablespace "extra" > "rec_isins_current_attachment" btree (attachment), tablespace > "extra" Hello, Are you sure that the column order of the PKs is the same in both tables? (attachment, isin) or (isin, attachment). When isin is at the second place, Postgres will read the whole index to find matching records. regards, Marc Mamin > Foreign-key constraints: > "rec_isins_attachment_fkey" FOREIGN KEY (attachment) REFERENCES > ts_frontend.attachments(id) ON UPDATE RESTRICT ON DELETE CASCADE > Inherits: ts_frontend.rec_isins > > The isin type is a domain type which has char(12) as its base type. > Both tables inherit from ts_frontend.rec_isins, which is empty and is > only used to search both tables in a single query. > > When we search for an isin in both tables (using the parent table, but > the behavior is the same if we directly search in one of the tables), > the primary key index is used. However, while the archive table is > pretty fast, the current table is much slower: > > # explain analyze select * from ts_frontend.rec_isins where isin = > 'foo'; > > QUERY PLAN > ---------------------------------------------------------------------- > - > ---------------------------------------------------------------------- > - > ------------------------------- > Result (cost=0.00..565831.43 rows=501 width=17) (actual > time=6080.778..6080.778 rows=0 loops=1) > -> Append (cost=0.00..565831.43 rows=501 width=17) (actual > time=6080.777..6080.777 rows=0 loops=1) > -> Seq Scan on rec_isins (cost=0.00..0.00 rows=1 width=36) > (actual time=0.001..0.001 rows=0 loops=1) > Filter: ((isin)::bpchar = 'foo'::bpchar) > -> Index Scan using rec_isins_archive_pkey on > rec_isins_archive rec_isins (cost=0.00..621.61 rows=405 width=17) > (actual time=10.335..10.335 rows=0 loops=1) > Index Cond: ((isin)::bpchar = 'foo'::bpchar) > -> Index Scan using rec_isins_current_pkey on > rec_isins_current rec_isins (cost=0.00..565209.82 rows=95 width=17) > (actual time=6070.440..6070.440 rows=0 loops=1) > Index Cond: ((isin)::bpchar = 'foo'::bpchar) Total > runtime: 6080.824 ms > > This is strange, because the archive table is four times larger than > the current table and the archive index is also four times larger than > the current index: > > relname | relfilenode | reltablespace | > pg_table_size > ------------------------------+-------------+---------------+--------- > ------------------------------+-------------+---------------+- > - > ------------------------------+-------------+---------------+---- > rec_isins | 514533886 | 0 | > 8192 > rec_isins_pkey | 514533892 | 0 | > 8192 > rec_isins_attachment | 514533899 | 0 | > 8192 > ------------------------------+-------------+---------------+--------- > ------------------------------+-------------+---------------+- > - > ------------------------------+-------------+---------------+---- > rec_isins_archive | 507194804 | 0 | > 10923393024 > rec_isins_archive_pkey | 507197615 | 139300915 | > 9048784896 > rec_isins_archive_attachment | 507197692 | 139300915 | > 4706050048 > ------------------------------+-------------+---------------+--------- > ------------------------------+-------------+---------------+- > - > ------------------------------+-------------+---------------+---- > rec_isins_current | 631621090 | 0 | > 2696216576 > rec_isins_current_pkey | 631621096 | 139300915 | > 2098552832 > rec_isins_current_attachment | 631621107 | 139300915 | > 1160683520 > > Both tables are in the same tablespace (and thus on the same disk) and > both indexes are also in the same tablespace (but in another than the > tables). > The current table has been vacuumed full and reindexed. > > Can anybody explain the difference? Why is the current table so slow? > And what can we do to improve performance? > > Thanks for your help, > Christian > > ------------------------------------------------------------ > Deriva GmbH Financial IT and Consulting Christian Schröder > Geschäftsführer Hans-Böckler-Straße 2 | D-37079 Göttingen > Tel: +49 (0)551 489 500-42 > Fax: +49 (0)551 489 500-91 > http://www.deriva.de > > Amtsgericht Göttingen | HRB 3240 > Geschäftsführer: Christian Schröder > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To > make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general