Wondering what are your CPU\RAM characteristics?
On 07/06/16 01:35, Torsten Zuehlsdorff wrote:
> 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
>
