From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig James <cjames@emolecules.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: 100x slowdown for nearly identical tables
Date: Wed, 01 May 2013 21:51:16 -0400
Message-ID: <9392.1367459476@sss.pgh.pa.us> (raw)
In-Reply-To: <CAFwQ8rchn1J9Jic=FaHDsHG2cTT+s_pFr2nuNy7PTG3_LCEYnA@mail.gmail.com>
References: <CAFwQ8rfY0X_uMiAczWz4tp+E=mFQC0ohMt-F1MrETMDKVBXqBw@mail.gmail.com>
<7768.1367453912@sss.pgh.pa.us>
<CAFwQ8rchn1J9Jic=FaHDsHG2cTT+s_pFr2nuNy7PTG3_LCEYnA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Craig James <cjames@emolecules.com> writes:
> On Wed, May 1, 2013 at 5:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It looks like old_str_conntab is more or less clustered by "id",
>> and str_conntab not so much. You could try EXPLAIN (ANALYZE, BUFFERS)
>> (on newer PG versions) to verify how many distinct pages are getting
>> touched during the indexscan.
> Yeah, now that you say it, it's obvious. The original table was built with
> ID from a sequence, so it's going to be naturally clustered by ID. The new
> table was built by reloading the data in alphabetical order by supplier
> name, so it would have scattered the IDs all over the place.
> I guess I could actually cluster the new table, but since that one table
> holds about 90% of the total data in the database, that would be a chore.
> Probably better to find a more efficient way to do the query.
Just out of curiosity, you could try forcing a bitmap indexscan to see
how much that helps. The planner evidently thinks "not at all", but
it's been wrong before ;-)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, cjames@emolecules.com
Subject: Re: 100x slowdown for nearly identical tables
In-Reply-To: <9392.1367459476@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox