PostgreSQL Complex indexing types and ordering

Question

I have been doing some heavy reading the last couple days on indexing and I'm trying to figure out the right way to index a query I have with a lot of constraints. I am using the postgres_ext gem to support array datatypes and GIN and GIST index types.

I have a two queries

.where("a_id IN (?) and b = ? and active = ? and ? != ALL(c) and ? = ANY(d)")
.where("a_id =? and active =? and ? != ALL(c)")

c and d are integer arrays

The indexes I plan on adding:

 add_index :deals, [:a, :b], :where => "active = true"
 add_index :deals [:c, :d], :index_type => :gin, :where => "active = true"

Will postgres use both of these multicolumn indexes in the first query?

Should array datatypes always be in "gin" index types? or can you also put them in a b-tree index?

and finally will the first index be used for 'a' in both of the queries?

Additional Information:

I am using PostgreSQL 9.1.3

create_table "table", :force => true do |t|
 t.integer  "a_id"    ##foreign key
 t.string   "title"
 t.text     "description",    :default => ""
 t.boolean  "active",           :default => true
 t.datetime "created_at",      :null => false
 t.datetime "updated_at",    :null => false
 t.integer  "b",
 t.integer  "c", :limit => 8,   :array => true
 t.integer  "d",  :array => true
end

Please show your table definitions or equivalent Rails model and PostgreSQL version. — Craig Ringer, Mar 09 '13 at 00:33
Re arrays and GIN, you can have a b-tree index of arrays but it isn't useful for operations like "array contains element". You need GIN for that, or to use the `intarray` extension and its `GiST` index type for integer arrays that will perform better under write load but worse under read load. — Craig Ringer, Mar 09 '13 at 01:21
Thank you for your response. One more question, will the first query use both of the indexes? — Kyle C, Mar 09 '13 at 01:37

score 4 · Accepted Answer · answered Mar 09 '13 at 01:58

Regarding arrays and GIN, you can have a b-tree index of arrays but it isn't useful for operations like "array contains element". You need GIN or GiST for that and only GIN is supported as a built-in index for all array types.

You can also use the intarray extension and its GiST index type for integer arrays that will perform better under write load but worse under read load.

As for determining whether Pg will use both indexes, the best way to tell is to use EXPLAIN ANALYZE and see. Get the statement Rails executes from the PostgreSQL log by enabling log_statement or from the Rails logs with SQL logging on. Then run it in psql with explain analyze. Alternately, use the auto_explain extension to capture performance reports on the queries as they run.

I have the feeling that you'll find that Pg can't combine a GiST or GIN and a b-tree index in the same filter. Combining indexes requires a bitmap index scan and that's IIRC only available for two b-tree indexes. You'd probably need to add the extra columns to the GiST or GIN index, but that'll increase the index size quite dramatically and may not be worth it.

You really need to use explain analyze to see how it works in the real world on sample or production data.

When working with multicolumn indexes, keep in mind that at least for b-tree indexes Pg can use an index on (a,b) for queries that filter on a or on both a and b, but not for queries that filter only on b. Indexes are usable left-to-right, you can't use an index to search for a value on the right side of the index unless you're also searching on all values to the left of it.

THank you very much for your answer Craig, one follow up question. You said you can have arrays in b-tree indexes but not for "contains" operations, what operations can you use? "=" ? — Kyle C, Mar 09 '13 at 18:27
@KyleC B-tree indexes support `=`, `<>` (not equals), `>`, `<`, `>=` and `<=`, ie equality and simple ordering. Support for ordering means they can be used for `BETWEEN` and for prefix searches like `LIKE 'abcd%'` but not for infix searches like `LIKE '%ab%'` or array `@>` (contains). More detail can be found in the PostgreSQL documentation. — Craig Ringer, Mar 10 '13 at 01:28

PostgreSQL Complex indexing types and ordering

1 Answers1