Re: significant slow down with various LIMIT
| От | Helio Campos Mello de Andrade |
|---|---|
| Тема | Re: significant slow down with various LIMIT |
| Дата | |
| Msg-id | 4bc288f1.0534e50a.5b97.ffffd4c7@mx.google.com обсуждение исходный текст |
| Ответ на | significant slow down with various LIMIT (norn <andrey.perliev@gmail.com>) |
| Список | pgsql-performance |
1 ) Limit (cost=0.00..9.57 rows=3 width=4) (actual time=0.090..0.138 rows=3 loops=1)
2 ) Limit (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795 rows=4 loops=1)
1 ) -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual time=0.088..0.136 rows=3 loops=1)
2 ) -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual time=0.089..4436.791 rows=4 loops=1)
1 ) Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
2 ) Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
1 ) -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4) (actual time=0.056..0.095 rows=3 loops=1)
2 ) -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4) (actual time=0.056..3988.249 rows=4 loops=1)
###################################################################################################################################################################################################
1 ) -> Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032 rows=3 loops=1)
2 ) -> Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942 rows=1244476 loops=1)
1 ) -> Index Scan using plugins_guide_address_pkey on plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=3)
2 ) -> Index Scan using plugins_guide_address_pkey on plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1244476)
###################################################################################################################################################################################################
- I am not an expert in the matter but in the first query it took only 3 loops to find 1 row and in the second it looped 1244476 times to find no row at all. Is it possible that there is no other row in the table that match the data you are trying to retrieve?
- Have you tried to recreate the index of the table? It could be that its damaged in some way that postgres can not use the index and its making a full search in the table. Again, it's just a wild guess.
1 ) Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id)
2 ) Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id)
1 ) Filter: (plugins_guide_address.city_id = 4535)
2 ) Filter: (plugins_guide_address.city_id = 4535)
1 ) -> Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028 rows=3 loops=1)
2 ) -> Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.027..284.195 rows=1244479 loops=1)
1 ) Total runtime: 0.244 ms
2 ) Total runtime: 4436.894 ms
Regards...
--
Helio Campos Mello de Andrade
2 ) Limit (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795 rows=4 loops=1)
1 ) -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual time=0.088..0.136 rows=3 loops=1)
2 ) -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual time=0.089..4436.791 rows=4 loops=1)
1 ) Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
2 ) Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
1 ) -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4) (actual time=0.056..0.095 rows=3 loops=1)
2 ) -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4) (actual time=0.056..3988.249 rows=4 loops=1)
###################################################################################################################################################################################################
1 ) -> Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032 rows=3 loops=1)
2 ) -> Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942 rows=1244476 loops=1)
1 ) -> Index Scan using plugins_guide_address_pkey on plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=3)
2 ) -> Index Scan using plugins_guide_address_pkey on plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1244476)
###################################################################################################################################################################################################
- I am not an expert in the matter but in the first query it took only 3 loops to find 1 row and in the second it looped 1244476 times to find no row at all. Is it possible that there is no other row in the table that match the data you are trying to retrieve?
- Have you tried to recreate the index of the table? It could be that its damaged in some way that postgres can not use the index and its making a full search in the table. Again, it's just a wild guess.
1 ) Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id)
2 ) Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id)
1 ) Filter: (plugins_guide_address.city_id = 4535)
2 ) Filter: (plugins_guide_address.city_id = 4535)
1 ) -> Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028 rows=3 loops=1)
2 ) -> Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.027..284.195 rows=1244479 loops=1)
1 ) Total runtime: 0.244 ms
2 ) Total runtime: 4436.894 ms
Regards...
--
Helio Campos Mello de Andrade
В списке pgsql-performance по дате отправления: