Re: Slow index performance - Mailing list pgsql-general

> -----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