Re: View with and without ::text casting performs differently.

On 09/05/2013 05:50 PM, Tom Lane wrote:
> Brian Fehrle <brianf(at)consistentstate(dot)com> writes:
>> I have a view, that when created with our create statement works
>> wonderfully, a query on the view with a standard where clause that
>> narrows the result to a single row performs in under a single ms.
>> However, when we export this view and re-import it (dump and restore of
>> the database, which happens often), the exported version of the view has
>> been modified by Postgres to include various typecasting of some columns
>> to text.
> This is normal (varchar doesn't actually have any operations of its own).
>
>> All columns that it typecasts to text are varchar(20), so there is
>> nothing wrong in what it's doing there. However, with the view
>> definition including the ::text casting, the query planner changes and
>> it goes into a nested loop, taking a query from <1ms to over ten minutes.
> I rather doubt that the now-explicit-instead-of-implicit casts have much
> to do with that. It seems more likely that you forgot to re-ANALYZE in
> the new database, or there are some different planner settings, or
> something along that line.
I have two versions of the view in place on the same server, one with
the typecasting and one without, and this is where I see the differences
(no ::text runs in 0.5ms and with ::text runs in 13 or so minutes with
nested loop), so it's all running off the same statistics on the data.

Running an analyse on all tables involved did not change the query plan
on the 'bad' version of the view (default_statistics_target = 400)

- Brian F
>
> regards, tom lane