Re: Cool hack with recursive queries

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate