Re: [PERFORM] plan difference between set-returning function with ROWS within IN() and a plain join
On Tue, May 6, 2008 at 11:27 AM, Frank van Vugt <[EMAIL PROTECTED]> wrote: >> > db=# explain analyse >> > select sum(base_total_val) >> > from sales_invoice >> > where id in (select id from si_credit_tree(8057)); >> >> Did you check whether this query even gives the right answer? > > You knew the right answer to that already ;) > >> I think you forgot the alias foo(id) in the subselect and it's >> actually reducing to "where id in (id)", ie, TRUE. > > Tricky, but completely obvious once pointed out, that's _exactly_ what was > happening. This is one of the reasons why, for a table named 'foo', I name the columns 'foo_id', not 'id'. Also, if you prefix the id column with the table name, you can usually use JOIN USING which is a little bit tighter and easier than JOIN ON. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Re: Query Optimization with Kruskal’s Algorithm
On May 8, 2:09 am, [EMAIL PROTECTED] ("Alexander Staubo") wrote:
> On 5/7/08, Tarcizio Bini <[EMAIL PROTECTED]> wrote:
>
> > I'm working on optimizing queries using the Kruskal algorithm
> > (http://ieeexplore.ieee.org/xpls/abs_all.jsp?arnumber=4318118).
>
> That paper looks very interesting. I would love to hear what the
> PostgreSQL committers think of this algorithm.
>
> Alexander.
>
> --
> Sent via pgsql-performance mailing list ([EMAIL PROTECTED])
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-performance
I also would like to hear from them. But seems like the thread is
loosed in tonn of other threads.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Re: Query Optimization with Kruskal’s Algorithm
Repost to -hackers, you're more likely to get a response on this topic.
On Sat, May 10, 2008 at 1:31 PM, Rauan Maemirov <[EMAIL PROTECTED]> wrote:
> On May 8, 2:09 am, [EMAIL PROTECTED] ("Alexander Staubo") wrote:
>> On 5/7/08, Tarcizio Bini <[EMAIL PROTECTED]> wrote:
>>
>> > I'm working on optimizing queries using the Kruskal algorithm
>> > (http://ieeexplore.ieee.org/xpls/abs_all.jsp?arnumber=4318118).
>>
>> That paper looks very interesting. I would love to hear what the
>> PostgreSQL committers think of this algorithm.
>>
>> Alexander.
>>
>> --
>> Sent via pgsql-performance mailing list ([EMAIL PROTECTED])
>> To make changes to your
>> subscription:http://www.postgresql.org/mailpref/pgsql-performance
>
> I also would like to hear from them. But seems like the thread is
> loosed in tonn of other threads.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | [EMAIL PROTECTED]
Edison, NJ 08837 | http://www.enterprisedb.com/
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: Re: [PERFORM] Re: Query Optimization with Kruskal’s Algorithm
"Jonah H. Harris" <[EMAIL PROTECTED]> writes: > Repost to -hackers, you're more likely to get a response on this topic. Probably not, unless you cite a more readily available reference. (I dropped my IEEE membership maybe fifteen years ago ...) 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
Re: Re: [PERFORM] Re: Query Optimization with Kruskal’s Algorithm
On Sat, May 10, 2008 at 5:12 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Jonah H. Harris" <[EMAIL PROTECTED]> writes: >> Repost to -hackers, you're more likely to get a response on this topic. > > Probably not, unless you cite a more readily available reference. > (I dropped my IEEE membership maybe fifteen years ago ...) Yeah, I don't have one either. Similarly, I couldn't find anything applicable to the PG implementation except references to the paper. Wikipedia has the algorithm itself (http://en.wikipedia.org/wiki/Kruskal's_algorithm), but I was more interested in the actual applicability to PG and any issues they ran into. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: Re: Re: [PERFORM] Re: Query Optimization with Kruskal’s Algorithm
"Jonah H. Harris" <[EMAIL PROTECTED]> writes: > Wikipedia has the algorithm itself > (http://en.wikipedia.org/wiki/Kruskal's_algorithm), but I was more > interested in the actual applicability to PG and any issues they ran > into. Hmm ... minimum spanning tree of a graph, eh? Right offhand I'd say this is a pretty terrible model of the join order planning problem. The difficulty with trying to represent join order as a weighted graph is that it assumes the cost to join two relations (ie, the weight on the arc between them) is independent of what else you have joined first. Which is clearly utterly wrong for join planning. Our GEQO optimizer has a similar issue --- it uses a search algorithm that is designed to solve traveling-salesman, which is almost the same thing as minimum spanning tree. The saving grace for GEQO is that its TSP orientation is only driving a heuristic; when it considers a given overall join order it is at least capable of computing the right cost. It looks to me like Kruskal's algorithm is entirely dependent on the assumption that minimizing the sum of some predetermined pairwise costs gives the correct plan. In short, I'm sure it's pretty fast compared to either of our current join planning methods, but I'll bet a lot that it often picks a much worse plan. Color me unexcited, unless they've found some novel way of defining the graph representation that avoids this problem. 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
Re: [PERFORM] Re: Query Optimization with Krusk al’s Algorithm
On May 10, 2008, at 1:31 PM, Rauan Maemirov wrote: I also would like to hear from them. But seems like the thread is loosed in tonn of other threads. It's also the middle of a commit fest, when a lot of the developers are focussed on processing the current patches in the queue, rather than actively exploring new, potential features. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
