Re: foreign key constraint, planner ignore index.

Andrew Nesheret wrote:
> Richard Huxton wrote:
>>
>>
>> 1. Try adding another 5 million rows to the test "traffic" table and
>> see if that makes any difference. It shouldn't.
> Opps.
> 1. Step
[snip re-running of script]
> --------------------------------------------------------------------------------------------------------------------------
>
> Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
> width=6) (actual time=45.494..45.509 rows=1 loops=1)
> Index Cond: (node = 9)
> Trigger for constraint traffic_node_fkey: time=459.164 calls=1
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Good.
> Total runtime: 656.148 ms

> 2. Step
> Run script again w/o creating data. *NO ANY MODIFICATIONS* to database.

> ------------------------------------------------------------------------------------------------------------------------
>
> Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
> width=6) (actual time=0.041..0.043 rows=1 loops=1)
> Index Cond: (node = 9)
> Trigger for constraint traffic_node_fkey: time=41469.620 calls=1
> ~~~~~~~~~~~~~~~~~~~~ BAD
> Total runtime: 41497.467 ms

Hmm - not seeing that here. Is it just that your machine has a very
variable workload? The times above are far enough apart from the times
below that I'm not sure they can be trusted.

What if you run it 10 times - do the times stay consistent?

> 3. Step Recreate data with 4999999 rows (SAME DIFFERENT results on first
> execute and second!!!)

> --------------------------------------------------------------------------------------------------------------------------
>
> Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
> width=6) (actual time=25.050..25.054 rows=1 loops=1)
> Index Cond: (node = 9)
> Trigger for constraint traffic_node_fkey: time=18.339 calls=1
> ~~~~~~~~~~~~~~~~~~~~~~ GOOD!
> Total runtime: 43.519 ms

> ------------------------------------------------------------------------------------------------------------------------
>
> Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
> width=6) (actual time=0.114..0.116 rows=1 loops=1)
> Index Cond: (node = 9)
> Trigger for constraint traffic_node_fkey: time=7183.677 calls=1

There's no reason for changes in timing here - the traffic table isn't
updated by the delete, only the testnode table and that's small enough
not to matter.

>> 2. Run a "vacuum verbose sf_ipv4traffic" and see if there's a lot of
>> dead rows. I shouldn't have thought there are.
> No dead rows.

[snip vacuum verbose output]

No, so that can't be anything to do with it.

--
Richard Huxton
Archonet Ltd