BUG #14087: btree_gin index doesn't work on INT with POSITIVE constraint

Поиск
Список
Период
Сортировка
От joris.vandyck@promani.be
Тема BUG #14087: btree_gin index doesn't work on INT with POSITIVE constraint
Дата
Msg-id 20160415185902.22924.77993@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #14087: btree_gin index doesn't work on INT with POSITIVE constraint  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      14087
Logged by:          Joris Van Dyck
Email address:      joris.vandyck@promani.be
PostgreSQL version: 9.4.6
Operating system:   Debian Jessie 64-bit
Description:

Multi-column GIN/BTREE index
-----------------------------
CREATE INDEX MyIndex
  ON articles
  USING gin
  (to_tsvector('simple'::regconfig, tags), magazine_id, random_id);

Query
-----
SELECT "articles"."id", "articles"."magazine_id", "articles"."thumb_url",
"articles"."date", "articles"."lancode", "articles"."title",
"articles"."description", "articles"."slug", "articles"."tags" FROM
"articles" WHERE (( (to_tsvector('pg_catalog.simple', "articles"."tags")) @@
(plainto_tsquery('pg_catalog.simple', 'housing'))) AND
"articles"."magazine_id" = 16 AND "articles"."random_id" > 11422) ORDER BY
"articles"."random_id" ASC LIMIT 49


Execution plan with random_id defined as integer
------------------------------------------------
Limit  (cost=36.12..36.14 rows=8 width=334) (actual time=23.650..23.662
rows=49 loops=1)
  ->  Sort  (cost=36.12..36.14 rows=8 width=334) (actual time=23.649..23.653
rows=49 loops=1)
        Sort Key: random_id
        Sort Method: top-N heapsort  Memory: 51kB
        ->  Bitmap Heap Scan on articles  (cost=20.10..36.00 rows=8
width=334) (actual time=21.488..22.849 rows=629 loops=1)
              Recheck Cond: ((to_tsvector('simple'::regconfig, tags) @@
'''housing'''::tsquery) AND (magazine_id = 16) AND (random_id > 11422))
              Heap Blocks: exact=378
              ->  Bitmap Index Scan on MyIndex  (cost=0.00..20.10 rows=8
width=0) (actual time=21.370..21.370 rows=629 loops=1)
                    Index Cond: ((to_tsvector('simple'::regconfig, tags) @@
'''housing'''::tsquery) AND (magazine_id = 16) AND (random_id > 11422))
Planning time: 2.668 ms
Execution time: 24.150 ms


Execution plan with random_id defined as smallint
-------------------------------------------------
Limit  (cost=55.57..55.59 rows=8 width=332) (actual time=4.253..4.266
rows=49 loops=1)
  ->  Sort  (cost=55.57..55.59 rows=8 width=332) (actual time=4.250..4.256
rows=49 loops=1)
        Sort Key: random_id
        Sort Method: top-N heapsort  Memory: 51kB
        ->  Bitmap Heap Scan on articles  (cost=10.23..55.45 rows=8
width=332) (actual time=1.407..3.492 rows=629 loops=1)
              Recheck Cond: ((to_tsvector('simple'::regconfig, tags) @@
'''housing'''::tsquery) AND (magazine_id = 16))
              Filter: (random_id > 11422)
              Rows Removed by Filter: 337
              Heap Blocks: exact=476
              ->  Bitmap Index Scan on MyIndex  (cost=0.00..10.23 rows=23
width=0) (actual time=1.246..1.246 rows=966 loops=1)
                    Index Cond: ((to_tsvector('simple'::regconfig, tags) @@
'''housing'''::tsquery) AND (magazine_id = 16))
Planning time: 2.771 ms
Execution time: 4.509 ms


The problem
-----------
It seems like the choice between using the full index (first case with
integer) or using the index partly and then filtering manually (second case
with smallint) should not depend on whether the random_id is defined as
integer or smallint. It is not clear to me whether this is because the
implementation doesn't support the multi-column GIN index with the smallint,
or because the planner decides to do it this way.
If this is not supported, then probably the documentation should be
updated:
http://www.postgresql.org/docs/9.4/static/btree-gin.html

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Christoph Berg
Дата:
Сообщение: Re: BUG #14033: cross-compilation to ARM fails
Следующее
От: psi-jack@linux-help.org
Дата:
Сообщение: BUG #14088: pg_restore, --schema, causes TOC entries from even executing.