Learning PostgreSQL 11 - Third Edition

Dynamic SQL and the caching effect

As we mentioned earlier, PL/pgSQL caches execution plans. This is quite good if the generated plan is expected to be static. For example, the following statement is expected to use an index scan, due to selectivity. In this case, caching the plan saves some time and increases performance:

SELECT * FROM account WHERE account_id =<INT>

In other scenarios, however, this is not true. For example, let's suppose that we have an index on the advertisement_date column, and we would like to get the number of advertisements since a certain date, as follows:

SELECT count (*) FROM car_portal_app.advertisement WHERE advertisement_date >= <certain_date>;

In the preceding query, the entries from the advertisement table ...