Re: foreign key constraint, planner ignore index.

Andrew Nesheret wrote:
> set enable_seqscan to on;
>
> prepare testStatement (int) as
> SELECT 1 FROM ONLY sf_ipv4traffic x WHERE $1 OPERATOR(pg_catalog.=)
> node FOR SHARE OF x;
>
> EXPLAIN ANALYZE execute testStatement( 2007 );
> EXPLAIN ANALYZE execute testStatement( 156 );
> QUERY PLAN

> Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383
> Total runtime: 129131.315 ms

> Seq Scan on sf_ipv4traffic x (cost=0.00..360281.29 rows=15795383
> Total runtime: 420342.751 ms

> set enable_seqscan to off;

> Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..577918.84
> Total runtime: 93.944 ms

> Index Scan using fki_nodes on sf_ipv4traffic x (cost=0.00..577918.84
> Total runtime: 445145.901 ms

OK, so your cost estimates are about 360,000 for seq-scan and 578,000
for index-scan. Of course the row estimates are fixed regardless of the
value you test so the estimated cost is the same for both 2007 and 156.

However, the timings aren't in line with the costs for node=156, and
you'd hope they would be. That should be fixable by tweaking the planner
cost settings (see chapter "17.6.2 planner cost constants"),
particularly effective_cache_size and random_page cost I'd guess.

I'm putting together a small test case to see if I can reproduce your
behaviour here.

--
Richard Huxton
Archonet Ltd