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