trafdev <trafdev@mail.ru> 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
