trafdev <trafdev@xxxxxxx> writes: > CREATE INDEX ix_feed_sub_date > ON stats.feed_sub > USING brin > (date); > CREATE UNIQUE INDEX ixu_feed_sub > ON stats.feed_sub > USING btree > (date, gran, aid, pid, sid, fid, subid COLLATE pg_catalog."default"); > HashAggregate (cost=901171.72..912354.97 rows=344100 width=86) (actual > time=7207.825..7335.473 rows=126044 loops=1) > " Group Key: subid, sid" > Buffers: shared hit=3635804 > -> Index Scan using ixu_feed_sub on feed_sub (cost=0.56..806544.38 > rows=3440994 width=86) (actual time=0.020..3650.208 rows=3578344 loops=1) > Index Cond: ((date >= '2016-06-01 00:00:00'::timestamp without > time zone) AND (date <= '2016-06-30 00:00:00'::timestamp without time > zone) AND (gran = '1 day'::interval) AND (aid = 3)) > Buffers: shared hit=3635804 > Planning time: 0.150 ms > Execution time: 7352.009 ms Neither of those indexes is terribly well designed for this query. A btree index on (aid, gran, date) or (gran, aid, date) would work much better. See https://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html You could rearrange the column order in that giant unique index and get some of the benefit. But if you're desperate to optimize this particular query, an index not bearing so many irrelevant columns would probably be better for it. An alternative way of thinking would be to create an index with those three leading columns and then all of the other columns used by this query as later columns. That would be an even larger index, but it would allow an index-only scan, which might be quite a lot faster. The fact that you seem to be hitting about one page for each row retrieved says that the data you need is pretty badly scattered, so constructing an index that concentrates everything you need into one range of the index might be the ticket. Either of these additional-index ideas is going to penalize table insertions/updates, so keep an eye on that end of the performance question too. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
