Skip site navigation (1) Skip section navigation (2)

Re: Subquery in a JOIN not getting restricted?

From: Jay Levitt <jay(dot)levitt(at)gmail(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Subquery in a JOIN not getting restricted?
Date: 2011-11-07 23:19:32
Message-ID: 4EB86784.7080209@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Jay LevittDate: 2011-11-08 01:56:42
Subject: Re: Subquery in a JOIN not getting restricted?
Previous:From: Kevin GrittnerDate: 2011-11-07 22:51:54
Subject: Re: Subquery in a JOIN not getting restricted?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group