Thread: Cool hack with recursive queries

Cool hack with recursive queries

From
Gregory Stark
Date:
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!


Re: Cool hack with recursive queries

From
Grzegorz Jaskiewicz
Date:
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  :)




Re: Cool hack with recursive queries

From
"David Rowley"
Date:
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.






Re: Cool hack with recursive queries

From
Tatsuo Ishii
Date:
> 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


Re: Cool hack with recursive queries

From
"Harald Armin Massa"
Date:
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!


Re: Cool hack with recursive queries

From
David Fetter
Date:
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


Re: Cool hack with recursive queries

From
Alvaro Herrera
Date:
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


Re: Cool hack with recursive queries

From
Zdenek Kotala
Date:
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


Re: Cool hack with recursive queries

From
"Merlin Moncure"
Date:
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


Re: Cool hack with recursive queries

From
David Fetter
Date:
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


Re: Cool hack with recursive queries

From
Tom Lane
Date:
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


Re: Cool hack with recursive queries

From
David Fetter
Date:
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


Re: Cool hack with recursive queries

From
Alvaro Herrera
Date:
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.


Re: Cool hack with recursive queries

From
Bruce Momjian
Date:
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. +


Re: Cool hack with recursive queries

From
Stefan Kaltenbrunner
Date:
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


Re: Cool hack with recursive queries

From
David Fetter
Date:
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


Re: Cool hack with recursive queries

From
Alvaro Herrera
Date:
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.


Re: Cool hack with recursive queries

From
David Fetter
Date:
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


Re: Cool hack with recursive queries

From
David Fetter
Date:
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