From: | Mark Mayo <djmornyc(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Brian Fehrle <brianf(at)consistentstate(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: View with and without ::text casting performs differently. |
Date: | 2013-09-06 19:49:42 |
Message-ID: | CAMpn5AWrTn2cXXAA-Vu40=MFpWb1Se4k3TNkj8=jPMnDtn7dSA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
*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(at)gmail(dot)com> 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Brian Fehrle <brianf(at)consistentstate(dot)com> 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(at)postgresql(dot)org
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2013-09-06 20:00:02 | Re: [PERFORM] encouraging index-only scans |
Previous Message | Mark Mayo | 2013-09-06 19:46:37 | Re: View with and without ::text casting performs differently. |