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

[Mark Mayo <djmornyc@xxxxxxxxx>]

*Sorry correction.

I meant the Materialize disappears when the join columns are the same data type.

On Fri, Sep 6, 2013 at 3:46 PM, Mark Mayo <djmornyc@xxxxxxxxx> wrote:

Good Afternoon,

I also came across this too.

The issue goes away if you keep your join columns the same data type on both tables.

The nested loop happens when the join columns are not the same data type.

Hope this helps.

Best

-Mark

On Fri, Sep 6, 2013 at 2:35 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Brian Fehrle <brianf@xxxxxxxxxxxxxxxxxxx> writes:
> On 09/05/2013 05:50 PM, Tom Lane wrote:

>> 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.

Hm.  Can you provide a self-contained example?

                        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