Message-ID: <522A05E1.1070103@consistentstate.com>
Date: Fri, 06 Sep 2013 10:42:09 -0600
From: Brian Fehrle <brianf@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130311 Thunderbird/17.0.4
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: View with and without ::text casting performs differently.
References: <5229097C.4070401@consistentstate.com>
 <1960.1378425018@sss.pgh.pa.us>
In-Reply-To: <1960.1378425018@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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