From: Jay Levitt <jay.levitt@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Subquery in a JOIN not getting restricted?
Date: Mon, 07 Nov 2011 18:19:32 -0500
Message-ID: <4EB86784.7080209@gmail.com> (raw)
In-Reply-To: <4EB80CAA0200002500042B3B@gw.wicourts.gov>
References: <4EB84CB4.2010102@gmail.com>
<4EB7FEFD0200002500042B1C@gw.wicourts.gov>
<4EB80CAA0200002500042B3B@gw.wicourts.gov>
Kevin Grittner wrote:
> "Kevin Grittner"<Kevin.Grittner@wicourts.gov> wrote:
>
>> If I had made the scores table wider, it might have gone from the
>> user table to scores on the index.
>
> Bah. I just forgot to put an index on scores.user_id. With that
> index available it did what you were probably expecting -- seq scan
> on questions, nested loop index scan on users, nested loop index
> scan on scores.
>
> You weren't running you test with just a few rows in each table and
> expecting the same plan to be generated as for tables with a lot of
> rows, were you?
No, we're a startup - we only have 2,000 users and 17,000 scores! We don't
need test databases yet...
But I just realized something I'd completely forgot (or blocked) - scores is
a view. And views don't have indexes. The underlying tables are ultimately
indexed by user_id, but I can believe that Postgres doesn't think that's a
cheap way to do it - especially since we're still using stock tuning
settings (I know) so its costs are all screwed up.
And yep! When I do a CREATE TABLE AS from that view, and add an index on
user_id, it works just as I'd like. I've been meaning to persist that view
anyway, so that's what I'll do.
Thanks for the push in the right direction..
Jay
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: jay.levitt@gmail.com, Kevin.Grittner@wicourts.gov
Subject: Re: Subquery in a JOIN not getting restricted?
In-Reply-To: <4EB86784.7080209@gmail.com>
* 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