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

From: joris(dot)vandyck(at)promani(dot)be
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14087: btree_gin index doesn't work on INT with POSITIVE constraint
Date: 2016-04-15 18:59:02
Message-ID: 20160415185902.22924.77993@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14087
Logged by: Joris Van Dyck
Email address: joris(dot)vandyck(at)promani(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message psi-jack 2016-04-15 19:12:46 BUG #14088: pg_restore, --schema, causes TOC entries from even executing.
Previous Message Robert Haas 2016-04-15 17:42:34 Re: [BUGS] Breakage with VACUUM ANALYSE + partitions