Re: [PERFORM] View with and without ::text casting performs differently.

2013-09-18 Thread Brian Fehrle
On 09/06/2013 12:35 PM, Tom Lane wrote: Brian Fehrle 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 pl

Re: [PERFORM] View with and without ::text casting performs differently.

2013-09-06 Thread Brian Fehrle
On 09/05/2013 05:50 PM, Tom Lane wrote: Brian Fehrle 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 an

Re: [PERFORM] View with and without ::text casting performs differently.

2013-09-06 Thread Mark Mayo
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 wrote: > Brian Fehrl

Re: [PERFORM] View with and without ::text casting performs differently.

2013-09-06 Thread Mark Mayo
*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 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

Re: [PERFORM] View with and without ::text casting performs differently.

2013-09-06 Thread Tom Lane
Brian Fehrle 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 a

Re: [PERFORM] View with and without ::text casting performs differently.

2013-09-05 Thread Tom Lane
Brian Fehrle 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

Re: [PERFORM] View with and without ::text casting performs differently.

2013-09-05 Thread Brian Fehrle
Apologies, forgot to include Postgres version 9.1.9 - Brian F On 09/05/2013 04:45 PM, Brian Fehrle wrote: Hi All, 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