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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
