public inbox for [email protected]  

help / color / mirror / Atom feed

From: Torsten Zuehlsdorff <[email protected]>
To: trafdev <[email protected]>
Cc: [email protected]
Subject: Re: less than 2 sec for response - possible?
Date: Wed, 6 Jul 2016 10:35:51 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: less than 2 sec for response - possible?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox