Mastering PostgreSQL 9.6

Making the process fail

So far you have seen what PostgreSQL can do for you and how the optimizer helps to speedup queries. PostgreSQL is pretty smart but it needs smart users. There are some cases in which the end user cripples the entire optimization process by doing stupid things. Let us drop the view:

test=# DROP VIEW v; DROP VIEW 

Now the view is recreated. Note that OFFSET 0 has been added to the end of the view:

test=# CREATE VIEW v AS                                                                                                                                SELECT  *                                                                                                                                      FROM    a, b                                                                                                                                   WHERE   aid = bid OFFSET 0; CREATE VIEW 

While this view is logically equivalent to the example shown previously, the optimizer has to treat things differently. Every OFFSET other than 0 will change the result and therefore the view has to be calculated. The entire optimization process ...