2

Let's say I have GIN index on column A that allows me to filter table fast on that column by appropriate WHERE condition which would be slow without that index. However I would like also to order filtered rows by using ORDER BY statement on, let's say column B. Ordering without index is slow, so I use BTREE index on column B. The problem is that pg don't want to use both indexes - if both WHERE cond on column A and ORDER BY B are present in query, pg uses only GIN index. When I remove WHERE cond from query, then it uses BTREE index on col B.

What I can do to force pg to use both indexes, one to filter rows and second one to order them?

| improve this question | |
  • 1
    How many rows will the query return? If an index (GIN or whatever) helps Postgres to efficently select 1000 rows among millions, then the sorting should not be a problem, with or without an index. – ypercubeᵀᴹ Nov 5 '15 at 19:59
  • 5
    Please edit your question and add the output of explain (analyze, verbose) for your queries. I think you are overestimating the cost of sorting. For a few thousand rows it's most probably faster to sort them in memory than to read the sort key from an index and then do a lookup based on that index. – a_horse_with_no_name Nov 5 '15 at 20:13
3

To answer your title question, PostgreSQL can make use of multiple indexes for a "bitmap" scan, but only with Boolean logic. It can't use one to order and the other to filter within the same table scan. It is an interesting idea, though.

| improve this answer | |
  • That's what I was afraid of :(. So it means that when my WHERE filter uses GIN index, but still returning significant number of rows that I want to order/limit, then it always will be slow since it can't use another index (BTREE) to sort them right? Is there any workaround? – user606521 Nov 6 '15 at 14:40
  • I can't think of other options (other than to go implement the feature--it is an open source database, after all, but it would not be trivial to implement this). But seeing the query and the explain (analyze, buffers) output might lead to some inspiration. Is there a LIMIT ? Why is the sorting so slow? – jjanes Nov 6 '15 at 16:36
  • For example after WHERE filter query returns 500k rows, and then I am sorting on TEXT field for example. Sorting 500k rows is slow without index. The only option I can imagine is to use WITH (SELECT ... WHERE ...) SELECT ... ORDER BY ... LIMIT ..., then there will be two separate queries and I hope PG will use separate index for each of them - I haven't tried it though. – user606521 Nov 6 '15 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.