X-Original-To: pgsql-hackers@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0F9AB475E4A for ; Sat, 1 Feb 2003 12:05:40 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 1C2C44760F8 for ; Sat, 1 Feb 2003 12:03:27 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h11H3W5u007648; Sat, 1 Feb 2003 12:03:32 -0500 (EST) To: Kevin Brown Cc: "'pgsql-hackers@postgresql.org'" Subject: Re: [PERFORM] not using index for select min(...) In-reply-to: <20030201164336.GP12957@filer> References: <200301311531.12605.josh@agliodbs.com> <20030201040954.GK15936@perrin.int.nxad.com> <4278.1044074132@sss.pgh.pa.us> <20030201164336.GP12957@filer> Comments: In-reply-to Kevin Brown message dated "Sat, 01 Feb 2003 08:43:37 -0800" Date: Sat, 01 Feb 2003 12:03:32 -0500 Message-ID: <7647.1044119012@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/29 X-Sequence-Number: 34971 Kevin Brown writes: > Hmm...any chance, then, of giving aggregate functions a means of > asking which table(s) and column(s) the original query referred to so > that it could do proper optimization on its own? You can't usefully do that without altering the aggregate paradigm. It won't help for min() to intuit the answer quickly if the query plan is going to insist on feeding every row to it anyway. > For instance, for a > "SELECT min(x) FROM mytable" query, the min() function would be told > upon asking that it's operating on column x of mytable, whereas it > would be told "undefined" for the column if the query were "SELECT > min(x+y) FROM mytable". In the former case, it would be able to do a > "SELECT x FROM mytable ORDER BY x LIMIT 1" on its own, Don't forget that it would also need to be aware of whether there were any WHERE clauses, joins, GROUP BY, perhaps other things I'm not thinking of. In the end, the only reasonable way to handle this kind of thing is to teach the query planner about it. Considering the small number of cases that are usefully optimizable (basically only MIN and MAX on a single table without any WHERE or GROUP clauses), and the ready availability of a SQL-level workaround, it strikes me as a very low-priority TODO item. regards, tom lane