Corin <wakathane(at)gmail(dot)com> wrote:
> It's already faster, which is great, but I wonder why the query
> plan is that complex.
Because that's the plan, out of all the ways the planner knows to
get the requested result set, which was estimated to cost the least.
If it isn't actually the fastest, that might suggest that you
should adjust your costing model. Could you tell us more about the
machine? Especially useful would be the amount of RAM, what else is
running on the machine, and what the disk system looks like. The
default configuration is almost never optimal for serious production
-- it's designed to behave reasonably if someone installs on their
desktop PC to try it out.
> I read in the pqsql docs that using a multicolumn key is almost
> never needed and only a waste of cpu/space.
Where in the docs did you see that?
> As in my previous tests, this is only a testing environment: so
> all data is in memory, no disk activity involved at all, no swap
> etc.
Ah, that suggests possible configuration changes. You can try these
out in the session to see the impact, and modify postgresql.conf if
they work out.
seq_page_cost = 0.01
random_page_cost = 0.01
effective_cache_size = <about 3/4 of your machine's RAM>
Also, make sure that you run VACUUM ANALYZE against the table after
initially populating it and before your benchmarks; otherwise you
might inadvertently include transient or one-time maintenance costs
to some benchmarks, or distort behavior by not yet having the
statistics present for sane optimizer choices.
-Kevin