integer[] indexing.
От | Dawid Kuroczko |
---|---|
Тема | integer[] indexing. |
Дата | |
Msg-id | 758d5e7f041008021163fb40eb@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: integer[] indexing.
Re: integer[] indexing. |
Список | pgsql-performance |
I have a large table with a column: ids integer[] not null most of these entries (over 95%) contain only one array element, some can contain up to 10 array elements. seqscan is naturally slow. GIST on int_array works nice, but GIST isn't exactly a speed daemon when it comes to updating. So I thought, why not create partial indexes? CREATE INDEX one_element_array_index ON table ((ids[1])) WHERE icount(ids) <= 1; CREATE INDEX many_element_array_index ON table USING GIST (ids) WHERE icount(ids) > 1; Now, if I select WHERE icount(ids) <= 1 AND ids[1] = 33 I get lightning fast results. If I select WHERE icount(ids) > 1 AND ids && '{33}' -- I get them even faster. But when I phrase the query: SELECT * FROM table WHERE (icount(ids) <= 1 AND ids[1] = 33) OR (icount(ids) > 1 AND ids && '{33}'); Planner insists on using seqscan. Even with enable_seqscan = off; Any hints, comments? :) [ I think thsese partial indexes take best of two worlds, only if planner wanted to take advantage of it... :) ] Regards, Dawid
В списке pgsql-performance по дате отправления: