Re: foreign key constraint, planner ignore index. - Mailing list pgsql-general

From Richard Huxton
Subject Re: foreign key constraint, planner ignore index.
Date
Msg-id 476A55AA.7010305@archonet.com
Whole thread Raw
In response to Re: foreign key constraint, planner ignore index.  (Andrew Nesheret <andrew@infinet.ru>)
Responses Re: foreign key constraint, planner ignore index.
List pgsql-general
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

pgsql-general by date:

Previous
From: Howard Cole
Date:
Subject: Re: Quick Regex Question
Next
From: Terry Fielder
Date:
Subject: Re: Quick Regex Question