Message-ID: <4EB86784.7080209@gmail.com>
Date: Mon, 07 Nov 2011 18:19:32 -0500
From: Jay Levitt <jay.levitt@gmail.com>
User-Agent: Postbox 3.0.1 (Macintosh/20111102)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-performance@postgresql.org
Subject: Re: Subquery in a JOIN not getting restricted?
References: <4EB84CB4.2010102@gmail.com>
	<4EB7FEFD0200002500042B1C@gw.wicourts.gov>
	<4EB80CAA0200002500042B3B@gw.wicourts.gov>
In-Reply-To: <4EB80CAA0200002500042B3B@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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