PostgresQL -- GIN index and intersection with B-trees?

Question

I have two indexes:

CREATE INDEX table_a_b ON table (a, b);
CREATE INDEX table_c_gin ON table USING GIN(c);

My queries look like this:

SELECT * FROM table WHERE a = 'test' and b = 1 and c @> '{"test1", "test2"}'::text[];

The query planner prints out this:

 Index Scan using table_a_b on table  (cost=0.13..8.15 rows=1 width=52)
 Index Cond: (((a)::text = 'test'::text) AND (b = 1))
 Filter: (c @> '{test1, test2}'::text[])

So is there any way I could make the GIN index work, too? Maybe there's a way to create a composite index with two different index types?

Appreciate any help.

I don't see a problem here. The query planner expects to get back a single row using the index on a and b. Why would it use a more complicated (and I suspect more costly) GIN index? Is the planner's estimate wildly wrong? That would be the thing to look into if so. — jpmc26
– jpmc26, Commented Nov 2, 2016 at 0:56
@jpmc26 Nothing wrong with it. I'm just trying to figure out if an index intersection can be used in this instance, because I suspect the more costly operation to be filtering through the arrays in column c. — nainy
– nainy, Commented Nov 2, 2016 at 6:04
Indexes are chosen based on how many rows they filter out. If the database estimates that one index will filter out more rows than another, it will prefer that one (assuming the table is big enough for avoiding a full scan). It's not a question of, "Which is more costly?" It's a question of which one gives back fewer rows. Do you think the array check will filter out more rows than the others? If so, you need to work with data that has those properties. Note that PG actually does have a way to combine the results of two index scans, if the planner estimates that it's worth it. — jpmc26
– jpmc26, Commented Nov 2, 2016 at 7:47
Related dba.stackexchange.com/questions/321308/… | stackoverflow.com/questions/65082916/… — Ciro Santilli OurBigBook.com
– Ciro Santilli OurBigBook.com, Commented Mar 1 at 15:22

Laurenz Albe · Accepted Answer · 2016-11-06 04:51:39Z

2

If the planner would think that it is worth doing, it could use a bitmap index scan on table_a_b and combile the two results. You'd have to look at the output of EXPLAIN to understand why it doesn't choose to do that.

If you want to create a combined index, you have to install the btree_gin extension. Then you can use text and integer columns in a GIN index.

answered Nov 6, 2016 at 4:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgresQL -- GIN index and intersection with B-trees?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related