On 09/05/2013 05:50 PM, Tom Lane wrote:
Brian Fehrle <brianf@xxxxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
