Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bpJBa-0006SV-JA for pgsql-performance@arkaria.postgresql.org; Wed, 28 Sep 2016 18:02:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bpJBZ-0007tb-CL for pgsql-performance@arkaria.postgresql.org; Wed, 28 Sep 2016 18:02:25 +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.84_2) (envelope-from ) id 1bpJ9t-0005dt-K1 for pgsql-performance@postgresql.org; Wed, 28 Sep 2016 18:00:41 +0000 Received: from tamriel.snowman.net ([2001:4830:167b::11]) by magus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bpJ9p-0007Gt-8w for pgsql-performance@postgresql.org; Wed, 28 Sep 2016 18:00:41 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 3CB245F79A; Wed, 28 Sep 2016 14:00:35 -0400 (EDT) Date: Wed, 28 Sep 2016 14:00:35 -0400 From: Stephen Frost To: Greg Spiegelberg Cc: Mike Sofen , "pgsql-performa." Subject: Re: Millions of tables Message-ID: <20160928180035.GX5148@tamriel.snowman.net> References: <002d01d218d1$41bb2160$c5316420$@runbox.com> <003401d218d5$cb900640$62b012c0$@runbox.com> <20160928172759.GW5148@tamriel.snowman.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="HENu/cXyPKFN4XCQ" Content-Disposition: inline In-Reply-To: X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/3.13.0-91-generic (x86_64) X-Uptime: 13:59:19 up 81 days, 19:23, 16 users, load average: 0.16, 0.12, 0.12 User-Agent: Mutt/1.5.21 (2010-09-15) X-Pg-Spam-Score: -4.9 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --HENu/cXyPKFN4XCQ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Greg, * Greg Spiegelberg (gspiegelberg@gmail.com) wrote: > On Wed, Sep 28, 2016 at 11:27 AM, Stephen Frost wrote: > > * Greg Spiegelberg (gspiegelberg@gmail.com) wrote: > > > Bigger buckets mean a wider possibility of response times. Some buckets > > > may contain 140k records and some 100X more. > > > > Have you analyzed the depth of the btree indexes to see how many more > > pages need to be read to handle finding a row in 140k records vs. 14M > > records vs. 140M records? > > > > I suspect you'd find that the change in actual depth (meaning how many > > pages have to actually be read to find the row you're looking for) isn't > > very much and that your concern over the "wider possibility of response > > times" isn't well founded. > Excellent feedback! Um, how does one look at tree depth in PostgreSQL? > Oracle I know but have not done the same in PG. Pointers? CREATE EXTENSION pageinspect; SELECT * FROM bt_metap('indexname'); https://www.postgresql.org/docs/9.5/static/pageinspect.html Thanks! Stephen --HENu/cXyPKFN4XCQ Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJX7AVCAAoJEO1sijiDR2RVcbsP+gPnxAkgMFAOHcUh/6chQp57 BA4JIXrUU1/a2WIMCwlGPw8a6CMfdenlTSUC5y2YR3rUqwhmOAJn1VIKnNSkthhw 9FWMzX4lIIeeGVlJgk+AWW1Wq7W8c3XKGpyNwnpz1wxboY9JNjR2ryfeZcQoey3f K7PYNcpNunzWHT93/wbBIgkuBLzbWlqOS/M9oF6ZPL84xTarImhELWMBsDxuWvLJ TD3/ekmAg2DR2xms8EfrIJ9Lx6Fy3zktlS3JWYkexCmaZkh3pndQ3HgjO9xV39iG Q66POFRZHPpc9qXCZoj9vQjGyBOp5y2WsXqbhrqRZytiUhoNLHuEQ6Bnh8MJRrRD eBo/GhpuUdDZgKzFvkazLn3RgcoVTacAO+pkZL1p19WuUKWNSFLSZX//YJMmnXUo fVMyrOmAAB17wLVvKJ+SrewEk0q4cILjqLr9Hsp5kiF6PmfpJHhdZQ7V3DQts5Qg FNCPr9iaN/Sbag5bSVf9TfsszjqQdgKS5kOnTn+t5qL004tFHThaG691doERSelI GXL1SqwZL+tH/cAJp5VLN22BPgHT4R5/C85YlEjeJSZdcD5DaYehkP7G9wSKcqkz leBadn3X7n2jv4PKWytrF4xRWibLUchtkcF+CEQ2uviJmePF1L0PZcde5mkcamN5 9EFUxh6h5ZAWJgdQWQ/p =F9/I -----END PGP SIGNATURE----- --HENu/cXyPKFN4XCQ--