Search code examples
performancepostgresqlexplainsql-execution-plan

Do PostgreSQL query plans depend on table row count?

My users table doesn't have many rows... yet. 😏

Might the query plan of the same query change as the table grows?

I.e., to see how my application will scale, should I seed my users table with BILLIONS 🤑 of rows before using EXPLAIN?

Solution

  • Estimated row counts are probably the most important factor that influence which query plan is chosen.

    Two examples that support this:

    • If you use a WHERE condition on an indexed column of a table, three things can happen:

      1. If the table is very small or a high percentage of the rows match the condition, a sequential scan will be used to read the whole table and filter out the rows that match the condition.

      2. If the table is large and a low percentage of the rows match the condition, an index scan will be used.

      3. If the table is large and a medium percentage of rows match the condition, a bitmap index scan will be used.

    • If you join two tables, the estimated row counts on the tables will determine if a nested loop join is chosen or not.