Cool hack with recursive queries

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