I have a database with a table which is accessed with about 5 different SELECT commands. I tested each one of them with the EXPLAIN ... and EXPLAIN ANALYZE .... The database includes some data (about 150,000 rows).

Here is the SELECT in link with the results shown below and as you will see, it matches the index one to one:

  FROM my_table
 WHERE verified
  AND status = 5

Depending on the index I test with, I get quite different results. For example, an index based on a column with a date date:

CREATE INDEX my_table_idx ON my_table (start_date)
       WHERE verified AND status = 5 AND col3 IS NOT NULL;

Index Scan using my_table_idx on my_tables  (cost=0.28..359.01 rows=551 width=2964)

However, when I change the index to not use start_date, because I do not need my results in a specific order, I get:

CREATE INDEX my_table_idx ON my_table ((true))
       WHERE verified AND status = 5 AND col3 IS NOT NULL;

Bitmap Heap Scan on my_table_idx  (cost=21.67..2204.89 rows=551 width=2964)

Here we see that the cost is much higher: 21.67 instead of .28 (77×) and 2204.89 instead of 359.01 (6.1×).

Obviously, the realtime production environment will be completely different than my local database copy that doesn't update all the time, etc. However, if the first index is indeed faster as indicated by the "cost" information, then I think that I should be using that index.

My question here is: Is the cost=... information accurate enough that I can indeed infer that the first index is better than the second one? Will that hold in a production environment? Or is that so inaccurate that I should not even bother doing such upfront work?

Bonus question: Is using a (true) expression ever a good idea in a Postgres INDEX?

  • 1
    Not a dupe, but perhaps you'll find this Q&A useful.
    – mustaccio
    Feb 10 at 23:37
  • I guess mustaccio's anser gives 90% of what I was wondering about. The Cost: ... only represents what Postgres will use, not really how much time it's going to take using it. Although it certainly somewhat correlate, especially for smaller numbers. Feb 10 at 23:51

1 Answer 1


The costing is at least attempting to be accurate, otherwise there would be no point in doing it. But estimation is very hard, and there are many ways to get it wrong. So it is accurate, except for when it isn't.

Some comments on your example:

You should show the actual queries. I'm assuming they were just select * from my_table WHERE verified AND status = 5 AND col3 IS NOT NULL but you shouldn't make us guess.

The first numbers from each cost is useless to you. That is how much work it takes to return the first row, but since you aren't using a LIMIT (or any of the things equivalent to a LIMIT), that doesn't matter as you can't stop after obtaining the first row. So only the 2nd number matters for the case shown.

The reason the first numbers are so different is that a bitmap scan needs to build the entire bitmap up front before it can return any rows, so this severally penalizes the first-row cost of the one that uses the bitmap. But since you aren't stopping after the first row, that doesn't matter in your case.

When you look at the full-run costs, you see they are much closer together relatively, but still a considerable difference (6x). And I think this difference is unlikely to be real in the case I assumed. If you follow the partial constant expression index, all the expression values are tied, and so the index lists them in the order the rows physically occur in the table. Traversing all those rows should be quite efficient as the table is being read in order. But for the partial index over start_date, the index entries are sorted on start_date, which may have nothing to do with the physical ordering of the rows in table. so reading all of the rows in the index will involve jumping all over the table, which might not be efficient. Now the use of the bitmap scan should almost completely ameliorate this while imposing only a small overhead by re-ordering the tuples before reading them, which is why I say the effect is not actually real. However, I think the planner has a problem accounting both for correlation and for bitmaps on a very selective partial index, so that the index over the constant expression gets an unfair estimation advantage that the bitmap cannot match in costing even though it does match in reality. I would note that if I inhibit the bitmap scan, then use of your second index actually is substantially faster than use of your first one, assuming I start from a cold cache each time.

  • That makes sense and you explain many details that I was not aware of, which is great. Thank you. Feb 11 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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