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 <alessan...@path.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 >
