From: Brian Fehrle <brianf@consistentstate.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: View with and without ::text casting performs differently.
Date: Fri, 06 Sep 2013 10:42:09 -0600
Message-ID: <522A05E1.1070103@consistentstate.com> (raw)
In-Reply-To: <1960.1378425018@sss.pgh.pa.us>
References: <5229097C.4070401@consistentstate.com>
<1960.1378425018@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 09/05/2013 05:50 PM, Tom Lane wrote:
> Brian Fehrle <brianf@consistentstate.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: brianf@consistentstate.com, tgl@sss.pgh.pa.us
Subject: Re: View with and without ::text casting performs differently.
In-Reply-To: <522A05E1.1070103@consistentstate.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox