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

Re: From Simple to Complex

From: Alessandro Gagliardi <alessandro(at)path(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: From Simple to Complex
Date: 2012-01-31 23:43:10
Message-ID: CAAB3BB+tgVjObo5tt3XM2yyvxQyqKNEyu+NdtrcTD806WJYoYg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
I just got a pointer on presenting EXPLAIN ANALYZE in a more human friendly
fashion (thanks, Agent M!): http://explain.depesz.com/s/A9S

From this it looks like the bottleneck happens when Postgres does an Index
Scan using emotions_moment_id_idx on emotions before filtering on
moments.inserted so I thought I'd try filtering on emotions.inserted
instead but that only made it worse. At the same time, I noticed that "FROM
pg_class, moments WHERE moments.tableoid = pg_class.oid" tends to run a bit
faster than "FROM pg_class JOIN moments ON moments.tableoid =
pg_class.oid". So I tried:

SELECT relname, emotion, COUNT(feedback_id)
  FROM pg_class, moments, emotions
 WHERE moments.tableoid = pg_class.oid
   AND emotions.inserted > 'yesterday'
   AND moments.inserted BETWEEN 'yesterday' AND 'today'
   AND emotions.moment_id = moments.moment_id
 GROUP BY relname, emotion
 ORDER BY relname, emotion;

That was a bit faster, but still very slow. Here's the EXPLAIN:
http://explain.depesz.com/s/ZdF

On Tue, Jan 31, 2012 at 2:53 PM, Alessandro Gagliardi
<alessandro(at)path(dot)com>wrote:

> I changed the query a bit so the results would not change over the
> course of the day to:
>
> SELECT relname, emotion, COUNT(feedback_id) FROM pg_class, moments
> JOIN emotions USING (moment_id)
> WHERE moments.inserted BETWEEN 'yesterday' AND 'today' AND
> moments.tableoid = pg_class.oid
> GROUP BY relname, emotion ORDER BY relname, emotion;
>

In response to

Responses

pgsql-performance by date

Next:From: Jeff JanesDate: 2012-02-01 02:11:57
Subject: Re: How to improve insert speed with index on text column
Previous:From: Alessandro GagliardiDate: 2012-01-31 22:53:46
Subject: Re: From Simple to Complex

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