Re: less than 2 sec for response - possible?

Thanks Tom.

I've created index on aid, date:

create index aaa on stats.feed_sub(aid,date);

and simplified a query (dropped gran as it's equal for all rows anyway):

SELECT
sum(stats.feed_sub.c_filt_click_cap_ip) AS clicks_from_ip,
sum(stats.feed_sub.c_filt_doubleclick) AS clicks_on_target,
sum(stats.feed_sub.c_filt_delay_clicks) AS ip_click_period,
sum(stats.feed_sub.c_filt_fast_click) AS fast_click,
sum(stats.feed_sub.c_filt_ip_mismatch) AS ip_mismatch,
sum(stats.feed_sub.c_filt_lng_mismatch) AS lng_mismatch,
sum(stats.feed_sub.c_filt_ref_mismatch) AS ref_mismatch,
sum(stats.feed_sub.c_filt_ua_mismatch) AS ua_mismatch,
sum(stats.feed_sub.c_filt_url_expired) AS url_expired,
stats.feed_sub.subid AS stats_feed_sub_subid,
stats.feed_sub.sid AS stats_feed_sub_sid
FROM stats.feed_sub
WHERE stats.feed_sub.date >= '2016-06-01' :: TIMESTAMP AND
stats.feed_sub.date <= '2016-06-30' :: TIMESTAMP AND
stats.feed_sub.aid = 3
GROUP BY
stats.feed_sub.subid, stats.feed_sub.sid;

All data is in the cache and it still takes almost 5 seconds to complete:

QUERY PLAN
HashAggregate (cost=792450.42..803727.24 rows=346979 width=86) (actual
time=4742.145..4882.468 rows=126533 loops=1)
" Group Key: subid, sid"
Buffers: shared hit=1350371
-> Index Scan using aaa on feed_sub (cost=0.43..697031.39
rows=3469783 width=86) (actual time=0.026..1655.394 rows=3588376 loops=1)
Index Cond: ((aid = 3) AND (date >= '2016-06-01
00:00:00'::timestamp without time zone) AND (date <= '2016-06-30
00:00:00'::timestamp without time zone))
Buffers: shared hit=1350371
Planning time: 0.159 ms
Execution time: 4899.934 ms

It's better, but still is far from "<2 secs" goal.

Any thoughts?

On 07/01/16 18:23, Tom Lane wrote:
> trafdev <trafdev(at)mail(dot)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
>