From: Helio Campos Mello de Andrade <helio.campos@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: significant slow down with various LIMIT
Date: Sun, 11 Apr 2010 23:43:57 -0300
Message-ID: <4bc288f1.0534e50a.5b97.ffffd4c7@mx.google.com> (raw)
In-Reply-To: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com>
References: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: helio.campos@gmail.com
Subject: Re: significant slow down with various LIMIT
In-Reply-To: <4bc288f1.0534e50a.5b97.ffffd4c7@mx.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox