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"
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
