Thread: Cool hack with recursive queries
So based on Graeme Job's T-SQL hack over at thedailywtf.com I adapted the
T-SQL code to Postgres and got this. Thought others might find it amusing.
WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS ( SELECT IX, IY, X::float, Y::float, X::float, Y::float, 0
FROM (select -2.2 + 0.031 * i, i from generate_series(0,101) as i) as xgen(x,ix), (select
-1.5+ 0.031 * i, i from generate_series(0,101) as i) as ygen(y,iy) UNION ALL SELECT IX, IY,
CX,CY, X * X - Y * Y + CX AS X, Y * X * 2 + CY, I + 1 FROM Z WHERE X * X + Y * Y <
16::float AND I < 100 ) SELECT array_to_string(array_agg(SUBSTRING(' .,,,-----++++%%%%@@@@####
',LEAST(GREATEST(I,1),27), 1)),'') FROM ( SELECT IX, IY, MAX(I) AS I FROM Z GROUP BY IY, IX
ORDER BY IY, IX ) AS ZT GROUP BY IY ORDER BY IY
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication
support!
On 2008-11-19, at 21:53, Gregory Stark wrote: > > So based on Graeme Job's T-SQL hack over at thedailywtf.com I > adapted the > T-SQL code to Postgres and got this. Thought others might find it > amusing. hohoho, nice. That's even better than mine "with recursive" PI generator :)
Gregory Stark wrote:
> So based on Graeme Job's T-SQL hack over at thedailywtf.com I adapted the
> T-SQL code to Postgres and got this. Thought others might find it amusing.
>
> WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS (
> SELECT IX, IY, X::float, Y::float, X::float, Y::float, 0
> FROM (select -2.2 + 0.031 * i, i from
> generate_series(0,101) as i) as xgen(x,ix),
> (select -1.5 + 0.031 * i, i from
> generate_series(0,101) as i) as ygen(y,iy)
> UNION ALL
> SELECT IX, IY, CX, CY, X * X - Y * Y + CX AS X, Y * X * 2
> + CY, I + 1
> FROM Z
> WHERE X * X + Y * Y < 16::float
> AND I < 100
> )
> SELECT array_to_string(array_agg(SUBSTRING(' .,,,-----++++%%%%@@@@####
> ', LEAST(GREATEST(I,1),27), 1)),'')
> FROM (
> SELECT IX, IY, MAX(I) AS I
> FROM Z
> GROUP BY IY, IX
> ORDER BY IY, IX
> ) AS ZT
> GROUP BY IY
> ORDER BY IY
>
That's pretty amazing.
I think we should add a regression test with that. :)
David.
> So based on Graeme Job's T-SQL hack over at thedailywtf.com I adapted the
> T-SQL code to Postgres and got this. Thought others might find it amusing.
>
> WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS (
> SELECT IX, IY, X::float, Y::float, X::float, Y::float, 0
> FROM (select -2.2 + 0.031 * i, i from generate_series(0,101) as i) as xgen(x,ix),
> (select -1.5 + 0.031 * i, i from generate_series(0,101) as i) as ygen(y,iy)
> UNION ALL
> SELECT IX, IY, CX, CY, X * X - Y * Y + CX AS X, Y * X * 2 + CY, I + 1
> FROM Z
> WHERE X * X + Y * Y < 16::float
> AND I < 100
> )
> SELECT array_to_string(array_agg(SUBSTRING(' .,,,-----++++%%%%@@@@#### ', LEAST(GREATEST(I,1),27), 1)),'')
> FROM (
> SELECT IX, IY, MAX(I) AS I
> FROM Z
> GROUP BY IY, IX
> ORDER BY IY, IX
> ) AS ZT
> GROUP BY IY
> ORDER BY IY
Is it a Mandelbrot? How nice!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
1st) it turns out PostgreSQL allows code that is more compact than MSQL: 19 lines instead of 46 lines 2nd) now there will be a really compelling reason for DBAs worldwide to upgrade to 8.4; after release everyone without Mandelbrot in SQL is just a lame noob 3rd) maybe THAT could be the final straw to argue against MySQL: "But it cannot do Mandelbrot, so it is not l33t" It's easier then to argue ACID and stuff. -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned!
On Wed, Nov 19, 2008 at 10:21:06PM -0000, David Rowley wrote: > Gregory Stark wrote: > > So based on Graeme Job's T-SQL hack over at thedailywtf.com I adapted the > > T-SQL code to Postgres and got this. Thought others might find it amusing. > > > That's pretty amazing. > > I think we should add a regression test with that. :) +1 for adding a regression test :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter escribió: > On Wed, Nov 19, 2008 at 10:21:06PM -0000, David Rowley wrote: > > Gregory Stark wrote: > > > So based on Graeme Job's T-SQL hack over at thedailywtf.com I adapted the > > > T-SQL code to Postgres and got this. Thought others might find it amusing. > > > > > That's pretty amazing. > > > > I think we should add a regression test with that. :) > > +1 for adding a regression test :) It's too slow for that :-( -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera napsal(a):
> David Fetter escribió:
>> On Wed, Nov 19, 2008 at 10:21:06PM -0000, David Rowley wrote:
>>> Gregory Stark wrote:
>>>> So based on Graeme Job's T-SQL hack over at thedailywtf.com I adapted the
>>>> T-SQL code to Postgres and got this. Thought others might find it amusing.
>>>>
>>> That's pretty amazing.
>>>
>>> I think we should add a regression test with that. :)
>> +1 for adding a regression test :)
>
> It's too slow for that :-(
>
I takes 2.6 second on my laptop. I think it is not so bad.
Zdenek
On Fri, Nov 21, 2008 at 3:06 PM, Zdenek Kotala <Zdenek.Kotala@sun.com> wrote: > > I takes 2.6 second on my laptop. I think it is not so bad. > Time: 694.512 ms :-) merlin
On Fri, Nov 21, 2008 at 09:06:13PM +0100, Zdenek Kotala wrote: > Alvaro Herrera napsal(a): >> David Fetter escribió: >>> On Wed, Nov 19, 2008 at 10:21:06PM -0000, David Rowley wrote: >>>> Gregory Stark wrote: >>>>> So based on Graeme Job's T-SQL hack over at thedailywtf.com I >>>>> adapted the T-SQL code to Postgres and got this. Thought others >>>>> might find it amusing. >>>>> >>>> That's pretty amazing. >>>> >>>> I think we should add a regression test with that. :) >>> +1 for adding a regression test :) >> >> It's too slow for that :-( > > I takes 2.6 second on my laptop. I think it is not so bad. About 2.0 on my OS/X laptop. Could this be a problem on whatever architecture/OS/compiler combo you have? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Fri, Nov 21, 2008 at 09:06:13PM +0100, Zdenek Kotala wrote:
>> I takes 2.6 second on my laptop. I think it is not so bad.
> About 2.0 on my OS/X laptop. Could this be a problem on whatever
> architecture/OS/compiler combo you have?
Not everyone is using fast new laptops.
This is a cool hack, agreed, but that doesn't make it a useful
regression test. Whatever value it might have isn't going to
repay the community-wide expenditure of cycles.
regards, tom lane
On Fri, Nov 21, 2008 at 04:11:11PM -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Fri, Nov 21, 2008 at 09:06:13PM +0100, Zdenek Kotala wrote: > >> I takes 2.6 second on my laptop. I think it is not so bad. > > > About 2.0 on my OS/X laptop. Could this be a problem on whatever > > architecture/OS/compiler combo you have? > > Not everyone is using fast new laptops. Possibly not, but this could be a way to flush out inconsistencies among floating point units or, more importantly, implementations of NUMERIC. > This is a cool hack, agreed, but that doesn't make it a useful > regression test. Whatever value it might have isn't going to repay > the community-wide expenditure of cycles. What's the slowest it runs? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter escribió: > On Fri, Nov 21, 2008 at 04:11:11PM -0500, Tom Lane wrote: > > This is a cool hack, agreed, but that doesn't make it a useful > > regression test. Whatever value it might have isn't going to repay > > the community-wide expenditure of cycles. > > What's the slowest it runs? If we want to do some advocacy with it, how about making some banners? Posters? Flyers? Or go blog about it. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > David Fetter escribi?: > > On Fri, Nov 21, 2008 at 04:11:11PM -0500, Tom Lane wrote: > > > > This is a cool hack, agreed, but that doesn't make it a useful > > > regression test. Whatever value it might have isn't going to repay > > > the community-wide expenditure of cycles. > > > > What's the slowest it runs? > > If we want to do some advocacy with it, how about making some banners? > Posters? Flyers? > > Or go blog about it. Agreed, there is great PR advantage to this, like us running on a PS2. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Tom Lane wrote: > David Fetter <david@fetter.org> writes: >> On Fri, Nov 21, 2008 at 09:06:13PM +0100, Zdenek Kotala wrote: >>> I takes 2.6 second on my laptop. I think it is not so bad. > >> About 2.0 on my OS/X laptop. Could this be a problem on whatever >> architecture/OS/compiler combo you have? > > Not everyone is using fast new laptops. > > This is a cool hack, agreed, but that doesn't make it a useful > regression test. Whatever value it might have isn't going to > repay the community-wide expenditure of cycles. FWIW: Time: 46719.632 ms on my ARM based buildfarm box ... Stefan
On Fri, Nov 21, 2008 at 04:33:16PM -0500, Bruce Momjian wrote: > Alvaro Herrera wrote: > > David Fetter escribi?: > > > On Fri, Nov 21, 2008 at 04:11:11PM -0500, Tom Lane wrote: > > > > > > This is a cool hack, agreed, but that doesn't make it a useful > > > > regression test. Whatever value it might have isn't going to repay > > > > the community-wide expenditure of cycles. > > > > > > What's the slowest it runs? > > > > If we want to do some advocacy with it, how about making some banners? > > Posters? Flyers? > > > > Or go blog about it. > > Agreed, there is great PR advantage to this, like us running on a PS2. I think our ability to make a return map is way cooler than our running on a PS2, but that's just me ;) Anyhow, I put it in my 8.4 talk, which I gave today at the first annual PGDay Argentina :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Gregory Stark wrote:
> WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS (
> SELECT IX, IY, X::float, Y::float, X::float, Y::float, 0
> FROM (select -2.2 + 0.031 * i, i from generate_series(0,101) as i) as xgen(x,ix),
> (select -1.5 + 0.031 * i, i from generate_series(0,101) as i) as ygen(y,iy)
> UNION ALL
> SELECT IX, IY, CX, CY, X * X - Y * Y + CX AS X, Y * X * 2 + CY, I + 1
> FROM Z
> WHERE X * X + Y * Y < 16::float
> AND I < 100
> )
> SELECT array_to_string(array_agg(SUBSTRING(' .,,,-----++++%%%%@@@@#### ', LEAST(GREATEST(I,1),27), 1)),'')
> FROM (
> SELECT IX, IY, MAX(I) AS I
> FROM Z
> GROUP BY IY, IX
> ORDER BY IY, IX
> ) AS ZT
> GROUP BY IY
> ORDER BY IY
FWIW you can halve the running time by restricting I to 27 instead of
100 in the recursive term, and obtain the same result.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On Sun, Nov 23, 2008 at 12:34:21AM -0300, Alvaro Herrera wrote:
> Gregory Stark wrote:
>
> > WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS (
> > [elided]
>
> FWIW you can halve the running time by restricting I to 27 instead of
> 100 in the recursive term, and obtain the same result.
I found it easier to read this way:
WITH RECURSIVE
Z(Ix, Iy, Cx, Cy, X, Y, I)
AS ( SELECT Ix, Iy, X::float, Y::float, X::float, Y::float, 0 FROM (SELECT -2.2 + 0.031 * i, i FROM
generate_series(0,101)AS i) AS xgen(x,ix) CROSS JOIN (SELECT -1.5 + 0.031 * i, i FROM generate_series(0,101) AS
i)AS ygen(y,iy) UNION ALL SELECT Ix, Iy, Cx, Cy, X * X - Y * Y + Cx AS X, Y * X * 2 + Cy, I + 1 FROM Z WHERE X
*X + Y * Y < 16::float AND I < 27
),
Zt (Ix, Iy, I) AS ( SELECT Ix, Iy, MAX(I) AS I FROM Z GROUP BY Iy, Ix ORDER BY Iy, Ix
)
SELECT array_to_string( array_agg( SUBSTRING(' .,,,-----++++%%%%@@@@#### ', LEAST(GREATEST(I,1),27), 1) ),''
)
FROM Zt
GROUP BY Iy
ORDER BY Iy;
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sun, Nov 23, 2008 at 11:49:31AM -0800, David Fetter wrote:
> On Sun, Nov 23, 2008 at 12:34:21AM -0300, Alvaro Herrera wrote:
> > Gregory Stark wrote:
> >
> > > WITH RECURSIVE Z(IX, IY, CX, CY, X, Y, I) AS (
> > > [elided]
> >
> > FWIW you can halve the running time by restricting I to 27 instead of
> > 100 in the recursive term, and obtain the same result.
>
> I found it easier to read this way:
>
> WITH RECURSIVE
> Z(Ix, Iy, Cx, Cy, X, Y, I)
With the I < 27 in the first part, the second part doesn't need a
LEAST, so it now reads:
WITH RECURSIVE
Z(Ix, Iy, Cx, Cy, X, Y, I)
AS ( SELECT Ix, Iy, X::float, Y::float, X::float, Y::float, 0 FROM (SELECT -2.2 + 0.031 * i, i FROM
generate_series(0,101)AS i) AS xgen(x,ix) CROSS JOIN (SELECT -1.5 + 0.031 * i, i FROM generate_series(0,101) AS
i)AS ygen(y,iy) UNION ALL SELECT Ix, Iy, Cx, Cy, X * X - Y * Y + Cx AS X, Y * X * 2 + Cy, I + 1 FROM Z WHERE X
*X + Y * Y < 16::float AND I < 27
),
Zt (Ix, Iy, I) AS ( SELECT Ix, Iy, MAX(I) AS I FROM Z GROUP BY Iy, Ix ORDER BY Iy, Ix
)
SELECT array_to_string( array_agg( SUBSTRING(' .,,,-----++++%%%%@@@@#### ', GREATEST(I,1), 1) ),''
)
FROM Zt
GROUP BY Iy
ORDER BY Iy;
That cuts it to 786ms or so on my laptop :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate