We used these phrases when using Oracle. Predicate Complete Queries: These are queries where the WHERE clauses are covered by indexes. Query Complete Queries: Where every part of the Query is covered by indexes. Allowing for an Index-Only scan to happen, and be very efficient. When moving to PostgreSQL, we found that there were more variables, like making sure the Visibility Map and statics were good so the Optimizer would choose the index. The problem in PG is that even if it uses the index. It often has to refer back to the table to see if the record is visible. (Again, the Visibility Map helps). Regardless, the value of indexing is huge. But also knowing when it is a waste of time. My smallest client has a TINY database. Recently moved to PG from SQLite as they grew to multiple users in geographically distant offices. Not even his Customer table will use an index... Because it's like 200 records. This table is likely cached. The query runs in a fraction of the planning time. Expecting an index to be used in those cases... Kinda crazy. But we all have these tiny lookup tables. No need to index those to death...
We used these phrases when using Oracle.
Predicate Complete Queries: These are queries where the WHERE clauses are covered by indexes.
Query Complete Queries: Where every part of the Query is covered by indexes. Allowing for an Index-Only scan to happen, and be very efficient.
When moving to PostgreSQL, we found that there were more variables, like making sure the Visibility Map and statics were good so the Optimizer would choose the index.
The problem in PG is that even if it uses the index. It often has to refer back to the table to see if the record is visible. (Again, the Visibility Map helps).
Regardless, the value of indexing is huge. But also knowing when it is a waste of time. My smallest client has a TINY database. Recently moved to PG from SQLite as they grew to multiple users in geographically distant offices. Not even his Customer table will use an index... Because it's like 200 records. This table is likely cached. The query runs in a fraction of the planning time.
Expecting an index to be used in those cases... Kinda crazy. But we all have these tiny lookup tables. No need to index those to death...