Re: Why so slow?

Ah, that did make a big difference! It went from taking 10x as long to
taking only 1.5x as long (about what I would have expected, if not
better.) Thank you!

On Fri, Feb 17, 2012 at 9:29 PM, Ants Aasma <ants(dot)aasma(at)eesti(dot)ee> wrote:

> On Feb 17, 2012 8:35 PM, "Alessandro Gagliardi" <alessandro(at)path(dot)com>
> wrote:
> > Here is the EXPLAIN: http://explain.depesz.com/s/ley
> >
> > I'm using PostgreSQL 9.0.6 on i486-pc-linux-gnu, compiled by GCC
> gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
> >
> > My random_page_cost is 2 and yet it still insists on using Seq Scan on
> blocks.
>
> As could be inferred from the row counts, it's slow because its joining
> and then aggregating a quarter of the blocks table. The hash join with its
> sequential scan is probably the correct choice for that type of join, it's
> the join itself that should be optimized out. The optimizer doesn't figure
> out that the join can be turned into a semi join if the output is
> aggregated with distinct and is from only one of the tables (in this case,
> because the output is the join key, it can be from either table).
>
> To make the optimizers job easier you can rewrite it as a semi-join
> explicitly:
> SELECT DISTINCT(user_id) FROM seen_its WHERE EXISTS (SELECT 1 FROM blocks
> WHERE blocks.user_id = seen_its.user_id) AND seen_its.created BETWEEN
> (now()::date - interval '8 days')::timestamp AND now()::date::timestamp
>
> --
> Ants Aasma
>