Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 2572063312C for ; Wed, 14 Jul 2010 10:49:42 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 06908-06 for ; Wed, 14 Jul 2010 13:49:33 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from tamriel.snowman.net (tamriel.snowman.net [72.66.115.51]) by mail.postgresql.org (Postfix) with ESMTP id 35A3D632D5F for ; Wed, 14 Jul 2010 10:49:31 -0300 (ADT) Received: by tamriel.snowman.net (Postfix, from userid 1000) id 87F552290B; Wed, 14 Jul 2010 09:49:28 -0400 (EDT) Date: Wed, 14 Jul 2010 09:49:28 -0400 From: Stephen Frost To: Ivan Voras Cc: pgsql-performance@postgresql.org Subject: Re: Understanding tsearch2 performance Message-ID: <20100714134928.GL21875@tamriel.snowman.net> References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="bg242D1lw63VwPWB" Content-Disposition: inline In-Reply-To: X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/2.6.26-1-amd64 (x86_64) X-Uptime: 09:42:40 up 321 days, 1:07, 33 users, load average: 0.25, 0.16, 0.10 User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/219 X-Sequence-Number: 39608 --bg242D1lw63VwPWB Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable * Ivan Voras (ivoras@freebsd.org) wrote: > Total runtime: 0.507 ms [...] > Total runtime: 118.689 ms >=20 > See in the first query where I have a simple LIMIT, it fetches random 10 > rows quickly, but in the second one, as soon as I give it to execute and > calculate the entire result set before I limit it, the performance is > horrible. What you've shown is that it takes 0.5ms for 10 rows, and 118ms for 8500 rows. Now, maybe I've missed something, but that's 0.05ms per row for the first query and 0.01ms per row for the second, and you've added a sort into the mix. The performance of going through the data actually improves on a per-record basis, since you're doing more in bulk. Since you're ordering by 'id', PG has to look at every row returned by the index scan. That's not free. Regarding the statistics, it's entirely possible that the index is *not* the fastest way to pull this data (it's nearly 10% of the table..), if the stats were better it might use a seq scan instead, not sure how bad the cost of the filter itself would be. Thanks, Stephen --bg242D1lw63VwPWB Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkw9wGgACgkQrzgMPqB3kih2nwCdFkV64kLdOEQ4pDdUwJKIbiYs SisAoIpTEY0RriTCqFHoOvoNwf+MqmIf =ZIOd -----END PGP SIGNATURE----- --bg242D1lw63VwPWB--