Slow index performance - Mailing list pgsql-general
From | Christian Schröder |
---|---|
Subject | Slow index performance |
Date | |
Msg-id | zarafa.55961f3e.39a1.563ba9754a289e24@zarafa.deriva.de Whole thread Raw |
Responses |
Re: Slow index performance
|
List | pgsql-general |
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
pgsql-general by date: