Re: significant slow down with various LIMIT

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