Re: foreign key constraint, planner ignore index.
От | Richard Huxton |
---|---|
Тема | Re: foreign key constraint, planner ignore index. |
Дата | |
Msg-id | 476A9044.1060407@archonet.com обсуждение исходный текст |
Ответ на | Re: foreign key constraint, planner ignore index. (Andrew Nesheret <andrew@infinet.ru>) |
Ответы |
Re: foreign key constraint, planner ignore index.
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: