Re: Subquery in a JOIN not getting restricted?

Kevin Grittner wrote:
> "Kevin Grittner"<Kevin(dot)Grittner(at)wicourts(dot)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