On 18 February 2014 14:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I notice though that the cost estimate for the seqscan plan isn't all that
> much lower than that for the indexscan plan. Probably lowering
> random_page_cost a bit would change the planner's mind. We have no
> information about total size of database vs available RAM, but if it's
> a mostly memory-resident database then such a change would be a good idea.
select pg_size_pretty(pg_database_size('fms'));
pg_size_pretty
----------------
3066 MB
(1 row)
The DB sits on a dedicated VM with 2G RAM, of which only about 600M is
currently used. So assuming it is mostly memory-resident seems pretty
reasonable.
I'm particularly interested in the massive different between cost and
actual for the index plan. The seq scan plan has 451984/248694 (ratio
1.82) for cost/actual, while the index plan has 502051/11597 (ratio
43.29). At least the seq scan plan is only out by a factor of ~2.
The row estimate for the Nested Loop op is 3362085 (vs 1934 actual).
The optimiser estimated 1934 rows (accurate!) for the
alarm_event_probe scan. As this table is joined to alarm_event on the
PK (alarm_event_id), each row in alarm_event_probe can match at most
one row from alarm_event, so the most rows you could expect from the
join would be 1934. The optimiser does not seem to realise that the
join is 1-to-1, or 1-to-0.
FWIW set random_page_cost = 3.6 causes it to generate the preferred plan.
I was under the impression that the best way to solve these kind of
optimiser problems was to ensure that the optimiser had good stats
information etc. There don't seem to be too many ways to direct it
when it makes poor choices.
What's the best way to fix this?
1. set random_page_cost = 3.0
2. set enable_seqscan = false;
Or something else?
Thanks,
Alistair