PostgreSQL - Will it ever use two indices on the same table?

Question

Say you have a table with some indices:

create table mail
(
    identifier serial primary key,
    member text,
    read boolean
);

create index on mail(member_identifier);
create index on mail(read);

If you now query on multiple columns which have separate indices, will it ever use both indices?

select * from mail where member = 'Jess' and read = false;

That is, can PostgreSQL decide to first use the index on member to fetch all mails for Jess and then use the index on read to fetch all unread mails and then intersect both results to construct the output set?

I know you can have an index with multiple columns (on (member, read) in this case), but what happens if you have two separate indices? Will PostgreSQL pick just one or can it use both in some cases?

This is not a question about a specific query. It is a generic question to understand the internals.

@RaymondNijland This question is not about a specific query, but more a generic question to understand the internals. — Jess Trens
– Jess Trens, Commented Oct 11, 2017 at 15:18

Dylan Brams · Accepted Answer · 2017-10-11 17:14:15Z

5

Postgres Documentation about multiple query indexes

Article says it will create an abstract representation of where both indexes apply then combine the results.

To combine multiple indexes, the system scans each needed index and prepares a bitmap in memory giving the locations of table rows that are reported as matching that index's conditions. The bitmaps are then ANDed and ORed together as needed by the query. Finally, the actual table rows are visited and returned.

edited Oct 11, 2017 at 17:14

user330315

answered Oct 11, 2017 at 15:25

Dylan Brams

2,1091 gold badge20 silver badges39 bronze badges

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

1 Comment

s.m. Over a year ago

Also see this article.

wildplasser · Accepted Answer · 2017-10-11 15:52:53Z

CREATE TABLE fifteen
        (one serial PRIMARY KEY
        , three integer not NULL
        , five integer not NULL
        );

INSERT INTO fifteen(three,five)
SELECT gs%33+5,gs%55+11
FROM generate_series(1,60000) gs
        ;

CREATE INDEX ON fifteen(three);
CREATE INDEX ON fifteen(five);
ANALYZE fifteen;

EXPLAIN ANALYZE
SELECT*
FROM fifteen
WHERE three= 7
AND five =13
        ;

Result:

CREATE TABLE
INSERT 0 60000
CREATE INDEX
CREATE INDEX
ANALYZE
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on fifteen  (cost=19.24..51.58 rows=31 width=12) (actual time=0.391..0.761 rows=364 loops=1)
   Recheck Cond: ((five = 13) AND (three = 7))
   Heap Blocks: exact=324
   ->  BitmapAnd  (cost=19.24..19.24 rows=31 width=0) (actual time=0.355..0.355 rows=0 loops=1)
         ->  Bitmap Index Scan on fifteen_five_idx  (cost=0.00..7.15 rows=1050 width=0) (actual time=0.136..0.136 rows=1091 loops=1)
               Index Cond: (five = 13)
         ->  Bitmap Index Scan on fifteen_three_idx  (cost=0.00..11.93 rows=1788 width=0) (actual time=0.194..0.194 rows=1819 loops=1)
               Index Cond: (three = 7)
 Planning time: 0.259 ms
 Execution time: 0.796 ms
(10 rows)

Changing {33,55} to {3,5} will yield an index scan over only one index, plus an addtional filter condition . (probablythe costsavings would be too little)

Collectives™ on Stack Overflow

PostgreSQL - Will it ever use two indices on the same table?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related