Kevin Grittner wrote:
"Kevin Grittner"<Kevin.Grittner@xxxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
