Received: from malur.postgresql.org ([2a02:16a8:dc51::56])
	by arkaria.postgresql.org with esmtps
 (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1fdMsB-0004zA-N8
	for pgsql-performance@arkaria.postgresql.org; Wed, 11 Jul 2018 21:42:07 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1fdMs9-0007rx-70
	for pgsql-performance@arkaria.postgresql.org; Wed, 11 Jul 2018 21:42:05 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <legrand_legrand@hotmail.com>)
	id 1fdMs8-0007rq-Vs
	for pgsql-performance@lists.postgresql.org; Wed, 11 Jul 2018 21:42:05 +0000
Received: from n3.nabble.com ([162.255.23.22])
	by magus.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <legrand_legrand@hotmail.com>)
	id 1fdMs3-0000dZ-Uj
	for pgsql-performance@postgresql.org; Wed, 11 Jul 2018 21:42:03 +0000
Received: from n3.nabble.com (localhost [127.0.0.1])
	by n3.nabble.com (Postfix) with ESMTP id 896FDDF50DE5
	for <pgsql-performance@postgresql.org>; Wed, 11 Jul 2018 14:41:55 -0700 (MST)
Date: Wed, 11 Jul 2018 14:41:55 -0700 (MST)
From: legrand legrand <legrand_legrand@hotmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1531345315560-0.post@n3.nabble.com>
In-Reply-To: 
 <CABcidk+P9Ujq-qvXXjivGrYjm7z_zJUgpjJEM8hXhrxgraR8_Q@mail.gmail.com>
References: 
 <CABcidk+P9Ujq-qvXXjivGrYjm7z_zJUgpjJEM8hXhrxgraR8_Q@mail.gmail.com>
Subject: Re: Improving Performance of Query ~ Filter by A, Sort by B
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

Hello,

I have tested it with release 11 and limit 20 is pushed to each partition
when using index on tmstmp.

Could you tell us what is the result of your query applyed to one partition 

EXPLAIN ANALYZE
SELECT "a"."id"
FROM  a_partition1 "a"
WHERE "a"."parent_id" IN (
    34226,24506,40987,27162
)
ORDER BY "a"."tmstmp" DESC
LIMIT 20;

May be that limit 20 is not pushed to partitions in your version ?
Regards
PAscal





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html