Can Postgres use multiple indexes in a single query?

Question

Assume that I have a query like below:

select 
   sum(impressions) as imp, sum(taps) as taps 
   from report 
   where org_id = 1 and report_date between '2019-01-01' and '2019-10-10'
   group by country, text;

In MYSQL, there is no support for multiple indexing for a single query. Can I use multiple indexes for a single query in PostgeSQL?

Like:

For where condition: index(org_id, report_date);
For group by: index(country, text);

Explain:

"GroupAggregate  (cost=8.18..8.21 rows=1 width=604)"
"  Group Key: country, text"
"  ->  Sort  (cost=8.18..8.18 rows=1 width=556)"
"        Sort Key: country, text"
"        ->  Index Scan using idx_org_date on report  (cost=0.14..8.17 rows=1 width=556)"
"              Index Cond: ((org_id = 1) AND (date >= '2019-01-01'::date) AND (date <= '2019-02-02'::date))"

Yes, if it makes sense, Postgres will use multiple indexes for the same table. You can easily see that if you display the execution plan — user330315
– user330315, Commented Oct 6, 2019 at 14:24
@a_horse_with_no_name, is it a single table or a single query? single table is not make sense. — Sha
– Sha, Commented Oct 6, 2019 at 14:27
Postgres can use multiple indexes in a single query - even for a single table in one query. — user330315
– user330315, Commented Oct 6, 2019 at 14:28
@a_horse_with_no_name, I've just added explain results to question. According to it, there is no multiple indexes for a single query? — Sha
– Sha, Commented Oct 6, 2019 at 15:28
The optimizer only expects a single row as the result of the index scan. There is no need to use an additional index for just one row. Although Postgres can use multiple indexes, I doubt it could combine two different ones for a WHERE and a GROUP BY because the where might only select some rows from the index supporting the GROUP BY and if they are not consecutive, the second index wouldn't help at all — user330315
– user330315, Commented Oct 6, 2019 at 15:40

jjanes · Accepted Answer · 2019-10-06 18:36:58Z

3

Yes and no. It can in general, but it can't use one index to get selectivity, and another to obtain the ordering needed for an efficient GROUP BY, on the same relation.

For example, if you had separate indexes on "org_id" and "report_date", it would be able to combine them using a BitmapAnd. But that would be less efficient than having your current two-column index, so this fact probably isn't of use to you.

You might be better off with a HashAgg. You could try increasing work_mem in order to get one. But if there truly is only one row, it won't really matter.

answered Oct 6, 2019 at 18:36

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Can Postgres use multiple indexes in a single query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related