Postgresql EXPLAIN command

Question

I have a query that has several filter conditions after WHERE clause.

Also, most of the columns involved have indexes on them.

When I run EXPLAIN command, I see:

->  Bitmap Index Scan on feature_expr_idx  (cost=0.00..8.10 rows=14 width=0)

feature_expr_idx is an index on one of the columns in WHERE clause.

But indexes for other columns are not shown. Instead, they are shown in FILTER row:

  Filter: ((NOT is_deleted) AND (vehicle_type = 'car'::text) AND (source_type = 'NONE'::text))

Why only a single Index is shown in the result, while other columns also having index are instead part of Filter?

Please edit your question and add the complete execution plan generated using explain (analyze, buffers, format text) (not just a "simple" explain) — user330315
– user330315, Commented Aug 21, 2020 at 5:35

Blue Star · Accepted Answer · 2020-08-21 05:17:26Z

2

Postgresql has a clever engine which tries to plan the best way to run your query. Often, this involves reading as little as possible from disk, because disk operations are slow. One of the reasons why indexes are so helpful is that by reading from the index, we can find a small number of rows in the table that need to be read in order to satisfy the query, and thus we can avoid reading through the entire table. Note, however, that the index is also on disk, and so reading the index also takes some time.

Now, imagine your query has two filters, one over column A and one over column B, both of which are indexed. According to the statistics postgresql has collected, there are about 5 rows that satisfy the filter on column A, and about 1000 rows that satisfy the filter on column B. In that case, it makes sense to read only the index on column A, then read all the matching 5 (or so) rows, and filter out any of them which don't match the filter on column B. Reading the index on column B would probably be more expensive than just reading the 5 rows!

The actual reason may be different than my example, but the point is that postgresql is simply trying to be as efficient as possible.

answered Aug 21, 2020 at 5:17

Blue Star

1,9621 gold badge13 silver badges11 bronze badges

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

1 Comment

Laurenz Albe Over a year ago

That is the correct answer - observe the estimate of 14 rows.

Collectives™ on Stack Overflow

Postgresql EXPLAIN command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related