Re: From Simple to Complex

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
>