Re: Fixing GIN for empty/null/full-scan cases

I wrote:
> No, I see no reason to think that has much to do with it. I'm wondering
> if your table is itself a bit bloated ...

Actually ... I notice you did not show EXPLAIN ANALYZE output for your
tests. Now I'm wondering whether you tested the right thing at all.
I got burnt that way too. Observe:

regression=# create index idx_gin_features on listings using gin(features) WHERE deleted_at IS NULL AND status = 1;
CREATE INDEX
regression=# explain analyze SELECT count(*) FROM listings
WHERE features @@ '(1368799&1368800&1369043)'::query_int
AND deleted_at IS NULL AND status = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=9158.24..9158.25 rows=1 width=0) (actual time=153.633..153.634 rows=1 loops=1)
-> Seq Scan on listings (cost=0.00..9157.22 rows=406 width=0) (actual time=0.048..153.493 rows=772 loops=1)
Filter: ((deleted_at IS NULL) AND (features @@ '1368799 & 1368800 & 1369043'::query_int) AND (status = 1))
Total runtime: 153.713 ms
(4 rows)

regression=# set enable_seqscan TO 0;
SET
regression=# explain analyze SELECT count(*) FROM listings
WHERE features @@ '(1368799&1368800&1369043)'::query_int
AND deleted_at IS NULL AND status = 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=13253.42..13253.43 rows=1 width=0) (actual time=331.990..331.990 rows=1 loops=1)
-> Bitmap Heap Scan on listings (cost=4095.18..13252.40 rows=406 width=0) (actual time=164.785..331.858 rows=772 loops=1)
Recheck Cond: ((deleted_at IS NULL) AND (status = 1))
Filter: (features @@ '1368799 & 1368800 & 1369043'::query_int)
-> Bitmap Index Scan on idx_gin_features (cost=0.00..4095.07 rows=406215 width=0) (actual time=164.045..164.045 rows=406215 loops=1)
Total runtime: 332.169 ms
(6 rows)

The above is "using" the index, but only as a guide to where the rows
satisfying the partial-index predicate are --- note the lack of any
index condition in the indexscan node. That's because the query_int
query is not in fact compatible with the core-provided index opclass.
We get much better results using intarray's gin__int_ops opclass:

regression=# drop index idx_gin_features;
DROP INDEX
regression=# create index idx_gin_features on listings using gin(features gin__int_ops) WHERE deleted_at IS NULL AND status = 1;
CREATE INDEX
regression=# explain analyze SELECT count(*) FROM listings
WHERE features @@ '(1368799&1368800&1369043)'::query_int
AND deleted_at IS NULL AND status = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1159.20..1159.21 rows=1 width=0) (actual time=23.896..23.896 rows=1 loops=1)
-> Bitmap Heap Scan on listings (cost=31.15..1158.18 rows=406 width=0) (actual time=22.912..23.813 rows=772 loops=1)
Recheck Cond: ((features @@ '1368799 & 1368800 & 1369043'::query_int) AND (deleted_at IS NULL) AND (status = 1))
-> Bitmap Index Scan on idx_gin_features (cost=0.00..31.05 rows=406 width=0) (actual time=22.811..22.811 rows=772 loops=1)
Index Cond: (features @@ '1368799 & 1368800 & 1369043'::query_int)
Total runtime: 24.036 ms
(6 rows)

regards, tom lane