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: (view raw or whole 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..


In response to


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-2015 The PostgreSQL Global Development Group