[SQL] ROW_NUMBER alias
Hi,
I needed ROW_NUMBER() in PostGresql and I did find the 'temporary sequence'
method as a workaround and i think it at least gets the job done relatively
well, ... so no problems there.
Its just that from a usability point of view, isn't it better that we
provide some kind of an aliasing mechanism here that allows a new user to
(unknowingly but) implicitly use a temporary sequence rather than make him
use SubQuery with a COUNT(*) and a comparison operator (with disastrous
performance) instead ??
So for a new user :
A query such as this :
SELECT ROW_NUMBER() AS row_number , a, b, c
FROM table
WHERE table_id = 973
ORDER BY record_date;
is internally interpreted by the planner as :
CREATE TEMP SEQUENCE rownum;
SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
FROM (
SELECT a, b, c
FROM table
WHERE table_id = 973
ORDER BY record_date
) t;
DROP SEQUENCE rownum;
Any ideas ?
(Of what I remember, I think till recently PostgreSql internally replaced
'MAX(x)' queries with a 'ORDER BY x DESC LIMIT 1' implicitly)
--
Robins
Re: [SQL] Sequence vs. Index Scan
On 5/5/07, Jaime Casanova <[EMAIL PROTECTED]> wrote:
On 5/5/07, Aaron Bono <[EMAIL PROTECTED]> wrote:
> On 5/5/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> > "Aaron Bono" <[EMAIL PROTECTED]> writes:
> > > 9. -> Seq Scan on branch (cost=
0.00..4.72
> rows=1
> > > width=1281) (actual time= 130129.988..157492.057 rows=1 loops=1)
> > > 10.Filter: ((start_day
> <= now()) AND
> > > ((end_day IS NULL) OR (end_day >= now())) AND (branch_id =
> > > get_branch_for_zip('22151'::character varying)))
> >
> > There is something *awfully* wacko about that entry --- the fact that
> > the cost estimate is less than 5 units means that the planner thinks
> > there's 4 or fewer pages; either that's way wrong or the
> > get_branch_for_zip function is taking enormous amounts of time per
row.
> > Have you tried timing that function on its own?
> >
> > One possible reason for the performance difference is if you have
> > get_branch_for_zip marked as stable in one database and volatile in
the
> > other --- volatile would prevent it from being used in an indexqual as
> > you'd like.
> >
>
> I verified it by putting a RAISE NOTICE in the function. The fast
schema
> runs the function twice (odd, I would think it would run only
once). The
> slow schema runs it 30 times (the number of records returned + 1). I
know I
> put the functions into both schemas as stable and even dropped and
recreated
> the function. Then I verified with EMS Manager and it tells me the DDL
for
> the function in the database is set to stable. Is there something I can
do
> to tell PostgreSQL that I really did mean stable?
>
maybe this is silly but you can verify what the database thinks of the
function selecting from pg_proc
select pronamespace, provolatile
from pg_proc where proname = 'get_branch_for_zip'
select pronamespace, provolatile, proname
from pg_proc where proname = 'get_branch_for_zip';
pronamespace | provolatile | proname
--+-+
26644852 | s | get_branch_for_zip
26644856 | s | get_branch_for_zip
The select is using the function on the slow schema as if it were volatile
but as stable on the fast schema.
I did a restart of the service and that didn't help.
Then I inserted 150 more records in the slow schema and pow - it started
working like the fast schema.
So my conclusion is that the function is being treated as volatile even
though it is stable because the number of records is small. Is there any
way to tell PostgreSQL that when I say stable I really mean stable?
I am getting close to a solution. Thanks again for the help!
-Aaron
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
Re: [SQL] ROW_NUMBER alias
I might be told off by some better SQL-User here on the list -
still here is my 2 Cents worth
> I needed ROW_NUMBER() in PostGresql and I did find the 'temporary sequence'
> method as a workaround and i think it at least gets the job done relatively
you use:
> CREATE TEMP SEQUENCE rownum;
> SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
> FROM (
> SELECT a, b, c
> FROM table
> WHERE table_id = 973
> ORDER BY record_date
> ) t;
Doesn't this just return the 973th single record for the current
sequence order in your table?
I believe:
SELECT a, b, c FROM table
offset 973 limit 1
will accomplish the same result.
Stefan
--
email: [EMAIL PROTECTED]
tel : +49 (0)6232-497631
http://www.yukonho.de
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] hi
Penchalaiah P. wrote: Information transmitted by this e-mail is proprietary to Infinite Computer Solutions It may be proprietary, but it shore ain't confidential! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] ROW_NUMBER alias
Hi Stefan,
Well that was just an example. That table had a primary key on (id, date).
By bad then, coz I guess I should have clarified that earlier.
But what I meant from the example was that it is trivial for the parser to
automatically put an enveloping SELECT to add a ROW_NUMBER() field to any
user given query.
Regards,
Robins Tharakan
On 5/7/07, Stefan Becker <[EMAIL PROTECTED]> wrote:
I might be told off by some better SQL-User here on the list -
still here is my 2 Cents worth
> I needed ROW_NUMBER() in PostGresql and I did find the 'temporary
sequence'
> method as a workaround and i think it at least gets the job done
relatively
you use:
> CREATE TEMP SEQUENCE rownum;
> SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
> FROM (
> SELECT a, b, c
> FROM table
> WHERE table_id = 973
> ORDER BY record_date
> ) t;
Doesn't this just return the 973th single record for the current
sequence order in your table?
I believe:
SELECT a, b, c FROM table
offset 973 limit 1
will accomplish the same result.
Stefan
