Skip site navigation (1) Skip section navigation (2)

Re: Why so slow?

From: Ants Aasma <ants(dot)aasma(at)eesti(dot)ee>
To: Alessandro Gagliardi <alessandro(at)path(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why so slow?
Date: 2012-02-18 05:29:15
Message-ID: CA+CSw_vPcZoYhgpcHdLHqYbuRiH=YCHuC0SDfJc8DcZJopEXow@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

  • Why so slow? at 2012-02-17 18:34:50 from Alessandro Gagliardi

Responses

pgsql-performance by date

Next:From: Steve HornDate: 2012-02-18 14:50:28
Subject: Query slow as function
Previous:From: Alessandro GagliardiDate: 2012-02-17 22:20:29
Subject: Re: Why so slow?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group