How "trustworthy" is the cost information returned by EXPLAIN in Postgres?

Question

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:

SELECT *
  FROM my_table
 WHERE verified
  AND status = 5
  AND col3 IS NOT NULL

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?

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.