Subject: Re: less than 2 sec for response - possible?
To: Torsten Zuehlsdorff <mailinglists@toco-domains.de>
References: <38b8542e-e011-370d-3863-207126ac9b33@mail.ru>
 <94532c82-1dfa-5904-da2c-09adcffcf4f3@toco-domains.de>
 <0461b3e8-cb38-0ded-6e8c-48e6dc545ea6@mail.ru>
 <0027eadb-4d83-14e5-e6a3-15fcf4376841@toco-domains.de>
Cc: pgsql-performance@postgresql.org
From: trafdev <trafdev@mail.ru>
Message-ID: <1d480b23-b7ed-413e-5ce9-cbf974bd8a9f@mail.ru>
Date: Wed, 6 Jul 2016 08:06:03 -0700
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:45.0) Gecko/20100101
 Thunderbird/45.1.1
MIME-Version: 1.0
In-Reply-To: <0027eadb-4d83-14e5-e6a3-15fcf4376841@toco-domains.de>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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
>


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance