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 22:10:11
Message-ID: CAAB3BBKxBK1suf9GcKUibWaK_WT7+V5rrLp+FPR7HXoC7EFHQQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Looks like I missed a key sentence in
http://www.postgresql.org/docs/9.0/static/ddl-inherit.html which states: "A
serious limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single
tables, not to their inheritance children."
I should have realized that as I exploited that "limitation" in three of my
tables. Gradually adding those indices now; will report on what kind of
difference it makes....

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

> My slow query today is somewhat more complex than yesterday's, but I'm
> hopeful it can be improved. Here's the query:
>
> SELECT relname, emotion, COUNT(feedback_id) FROM pg_class, moments
> JOIN emotions USING (moment_id)
> WHERE moments.inserted > 'today' AND moments.tableoid = pg_class.oid
> GROUP BY relname, emotion ORDER BY relname, emotion;
>
> As you'll see below, moments is inherited by a number of other tables
> and the purpose of relname is to see which one. Meanwhile, emotions
> inherits feedback.
>
> Here's the Full Table and Index Schema:
>
> CREATE TABLE moments
> (
>   moment_id character(24) NOT NULL DEFAULT to_char(now(),
> 'JHH24MISSUS'::text),
>   block_id character(24) NOT NULL,
>   inserted timestamp without time zone NOT NULL DEFAULT now(),
>   CONSTRAINT moments_pkey PRIMARY KEY (moment_id )
> )
> WITH (
>   OIDS=FALSE
> );
>
> CREATE INDEX moments_block_id_idx
>   ON moments
>   USING btree
>   (block_id );
>
> CREATE INDEX moments_inserted_idx
>   ON moments
>   USING btree
>   (inserted );
>
> CREATE TABLE feedback
> (
>  feedback_id character(24) NOT NULL,
>  user_id character(24) NOT NULL,
>  moment_id character(24) NOT NULL,
>  created timestamp without time zone,
>  inserted timestamp without time zone NOT NULL DEFAULT now(),
>  lnglat point,
>  CONSTRAINT feedback_pkey PRIMARY KEY (feedback_id )
> )
> WITH (
>  OIDS=FALSE
> );
>
> CREATE INDEX feedback_lnglat_idx
>  ON feedback
>  USING gist
>  (lnglat );
>
> CREATE INDEX feedback_moment_id_idx
>  ON feedback
>  USING btree
>  (moment_id );
>
> CREATE TABLE emotions
> (
> -- Inherited from table feedback:  feedback_id character(24) NOT NULL,
> -- Inherited from table feedback:  user_id character(24) NOT NULL,
> -- Inherited from table feedback:  moment_id character(24) NOT NULL,
> -- Inherited from table feedback:  created timestamp without time zone,
> -- Inherited from table feedback:  inserted timestamp without time
> zone NOT NULL DEFAULT now(),
>  emotion character varying NOT NULL,
> -- Inherited from table :  lnglat point,
>  CONSTRAINT emotions_pkey PRIMARY KEY (feedback_id )
> )
> INHERITS (feedback)
> WITH (
>  OIDS=FALSE
> );
>
> CREATE INDEX emotions_emotion_idx
>  ON emotions
>  USING btree
>  (emotion );
>
> Here's the results from EXPLAIN ANALYZE:
>
> "Sort  (cost=309717.70..309718.43 rows=1460 width=94) (actual
> time=60462.534..60462.544 rows=25 loops=1)"
> "  Sort Key: pg_class.relname, emotions.emotion"
> "  Sort Method:  quicksort  Memory: 20kB"
> "  ->  HashAggregate  (cost=309697.24..309702.35 rows=1460 width=94)
> (actual time=60462.457..60462.476 rows=25 loops=1)"
> "        ->  Hash Join  (cost=133154.62..308963.70 rows=489024
> width=94) (actual time=26910.488..60031.589 rows=194642 loops=1)"
> "              Hash Cond: (public.moments.tableoid = pg_class.oid)"
> "              ->  Hash Join  (cost=133144.72..307119.96 rows=489024
> width=34) (actual time=26909.984..59434.137 rows=194642 loops=1)"
> "                    Hash Cond: (public.moments.moment_id =
> emotions.moment_id)"
> "                    ->  Append  (cost=0.00..114981.64 rows=119665
> width=29) (actual time=883.153..21696.939 rows=357565 loops=1)"
> "                          ->  Seq Scan on moments  (cost=0.00..0.00
> rows=1 width=104) (actual time=0.000..0.000 rows=0 loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on thoughts moments
> (cost=0.00..38856.88 rows=44388 width=29) (actual
> time=883.150..9040.959 rows=115436 loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on photos moments
> (cost=0.00..29635.78 rows=194 width=29) (actual
> time=5329.700..5827.447 rows=116420 loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on music moments
> (cost=0.00..9371.88 rows=19070 width=29) (actual time=354.147..383.266
> rows=37248 loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on people moments
> (cost=0.00..5945.26 rows=27 width=29) (actual time=185.393..185.393
> rows=0 loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on places moments
> (cost=0.00..24551.03 rows=54961 width=29) (actual
> time=5224.044..5324.517 rows=85564 loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on videos moments
> (cost=0.00..981.31 rows=734 width=29) (actual time=21.075..28.735
> rows=2897 loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on facebook_people moments
> (cost=0.00..10.84 rows=80 width=104) (actual time=0.001..0.001 rows=0
> loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on address_people moments
> (cost=0.00..10.84 rows=80 width=104) (actual time=0.005..0.005 rows=0
> loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on path_people moments
> (cost=0.00..5606.79 rows=30 width=29) (actual time=211.166..211.166
> rows=0 loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                          ->  Seq Scan on sleep moments
> (cost=0.00..11.05 rows=100 width=104) (actual time=0.002..0.002 rows=0
> loops=1)"
> "                                Filter: (inserted > '2012-01-31
> 00:00:00'::timestamp without time zone)"
> "                    ->  Hash  (cost=79292.49..79292.49 rows=4059496
> width=55) (actual time=25757.998..25757.998 rows=4058642 loops=1)"
> "                          Buckets: 262144  Batches: 4  Memory Usage:
> 75211kB"
> "                          ->  Seq Scan on emotions
> (cost=0.00..79292.49 rows=4059496 width=55) (actual
> time=0.012..15969.981 rows=4058642 loops=1)"
> "              ->  Hash  (cost=8.88..8.88 rows=292 width=68) (actual
> time=0.487..0.487 rows=319 loops=1)"
> "                    Buckets: 1024  Batches: 1  Memory Usage: 28kB"
> "                    ->  Seq Scan on pg_class  (cost=0.00..8.88
> rows=292 width=68) (actual time=0.013..0.234 rows=319 loops=1)"
> "Total runtime: 60601.612 ms"
>
> Postgres version: is still 9.0.5
>
> History: N/A (This is the first time I've run this query.)
>
> Hardware: 1.7 GB Cache and other things you'd expect from a Ronin
> instance of a Heroku Postgres database.
>
> Maintenance Setup: What Heroku does. As before, vacuum should not be
> relevant as there are no deletes or even updates (just inserts and
> selects)
>
> WAL Configuration: I still don't know. Heroku hosts the database on
> Amazon's servers, so maybe that answers the question?
>
> GUC Settings: As per the yesterday's discussion, I reduced
> random_page_cost to 2. Other than that, it's all default.
>
> Bonus question: If that was too simple, here's something even more
> complex I'd like to do: I have another table that inherits feedback
> called "comments". Ideally, rather than an "emotion" column coming
> out, I would like to have a "feedback_type" column that would be
> either the value in the emotion column of the emotions table, or
> "comment" if it's from the comments table. I'm thinking I'm going to
> have to simply put that together on the client, but if I can do that
> in a single query (that doesn't take an hour to run) that would be
> super cool. But that's definitely secondary compared to getting the
> above query to run faster.
>
> Thank you very much for any help!
> -Alessandro Gagliardi
>

In response to

Responses

pgsql-performance by date

Next:From: Alessandro GagliardiDate: 2012-01-31 22:53:46
Subject: Re: From Simple to Complex
Previous:From: Alessandro GagliardiDate: 2012-01-31 21:22:12
Subject: From Simple to Complex

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