On 05.07.2016 17:35, trafdev wrote:
> [..]
> Without TIMESTAMP cast:
>
> QUERY PLAN
> HashAggregate (cost=1405666.90..1416585.93 rows=335970 width=86)
> (actual time=4797.272..4924.015 rows=126533 loops=1)
> " Group Key: subid, sid"
> Buffers: shared hit=1486949
> -> Index Scan using ix_feed_sub_aid_date on feed_sub
> (cost=0.44..1313275.32 rows=3359694 width=86) (actual
> time=0.019..1783.104 rows=3588376 loops=1)
> Index Cond: ((aid = 3) AND (date >= '2016-06-01'::date) AND
> (date <= '2016-06-30'::date))
> Buffers: shared hit=1486949
> Planning time: 0.164 ms
> Execution time: 4941.259 ms
>
> I need to be sure it's a physical limitation of a Postgresql (when all
> data is in a memory and fetching\joining 1.5 mln of rows can't be done
> in less than 2-3 seconds) and there is no way to improve it.
It could be a physical limitation of your hardware. I just did a short
test on one of my databases:
Aggregate (cost=532018.95..532018.96 rows=1 width=0) (actual
time=3396.689..3396.689 rows=1 loops=1)
Buffers: shared hit=155711
-> Index Only Scan using requests_request_time_idx on requests
(cost=0.43..493109.90 rows=15563620 width=0) (actual
time=0.021..2174.614 rows=16443288 loops=1)
Index Cond: ((request_time >= '2016-07-01
00:00:00+00'::timestamp with time zone) AND (request_time <= '2017-07-06
00:00:00+00'::timestamp with time zone))
Heap Fetches: 31254
Buffers: shared hit=155711
Planning time: 0.143 ms
Execution time: 3396.715 ms
(8 rows)
As you can see i can get 16.4 Mio rows within 3.4 seconds from cache.
Your index-scan fetches 3.5 mio in 1.7 second, that's hardly half of the
performance of my database.
Greetings,
Torsten
