Re: significant slow down with various LIMIT

On Apr 13, 5:28 am, Kevin(dot)Gritt(dot)(dot)(dot)(at)wicourts(dot)gov ("Kevin Grittner")
wrote:
> The cost settings help the optimizer make good decisions about plan
> choice.  I guess I don't have much reason to believe, at this point,
> that there is a better plan for it to choose for this query.  Do you
> think you see one?  What would that be?  (We might be able to force
> that plan and find out if you're right, which can be a valuable
> diagnostic step, even if the way it gets forced isn't a
> production-quality solution.)
I have no deep knowledge of Postgresql, so I've no idea which plan is
the best, but I am wondering why there are so big gap between two
limits and how to avoid this...

> Are you able to share the table descriptions?  (That might help us
> suggest an index or some such which might help.)
sure, here it is

# \d core_object
Table "public.core_object"
Column | Type |
Modifiers
-----------+---------
+----------------------------------------------------------
id | integer | not null default
nextval('core_object_id_seq'::regclass)
typeid_id | integer | not
null
Indexes:
"core_object_pkey" PRIMARY KEY, btree
(id)
"core_object_pkey_desc" btree (id
DESC)
"core_object_typeid_id" btree
(typeid_id)
Foreign-key
constraints:
"core_object_typeid_id_fkey" FOREIGN KEY (typeid_id) REFERENCES
core_obj_typeset(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "plugins_plugin_addr" CONSTRAINT
"plugins_plugin_addr_oid_id_fkey" FOREIGN KEY (oid_id) REFERENCES
core_object(id) DEFERRABLE INITIALLY
DEFERRED
...and many others, so I skipped as irrelevant....

# \d plugins_plugin_addr
Table "public.plugins_plugin_addr"
Column | Type | Modifiers
---------------+---------
+------------------------------------------------------------------
id | integer | not null default
nextval('plugins_plugin_addr_id_seq'::regclass)
oid_id | integer | not null
sub_attrib_id | integer | not null
address_id | integer | not null
Indexes:
"plugins_plugin_addr_pkey" PRIMARY KEY, btree (id)
"plugins_plugin_addr_sub_attrib_id_key" UNIQUE, btree
(sub_attrib_id)
"plugins_plugin_addr_address_id" btree (address_id)
"plugins_plugin_addr_oid_id" btree (oid_id)
Foreign-key constraints:
"plugins_plugin_addr_address_id_fkey" FOREIGN KEY (address_id)
REFERENCES plugins_guide_address(id) DEFERRABLE INITIALLY DEFERRED
"plugins_plugin_addr_oid_id_fkey" FOREIGN KEY (oid_id) REFERENCES
core_object(id) DEFERRABLE INITIALLY DEFERRED
"plugins_plugin_addr_sub_attrib_id_fkey" FOREIGN KEY
(sub_attrib_id) REFERENCES plugins_sub_attrib(id) DEFERRABLE INITIALLY
DEFERRED

# \d plugins_guide_address
Table
"public.plugins_guide_address"
Column | Type |
Modifiers
--------------+------------------------
+--------------------------------------------------------------------
id | integer | not null default
nextval('plugins_guide_address_id_seq'::regclass)
country_id | integer |
region_id | integer |
city_id | integer |
zip_id | integer |
street_id | integer |
house | character varying(20) |
district_id | integer |
code | character varying(23) |
significance | smallint |
alias_fr | character varying(300) |
alias_ru | character varying(300) |
alias_en | character varying(300) |
alias_de | character varying(300) |
alias_it | character varying(300) |
alias_len | smallint |
Indexes:
"plugins_guide_address_pkey" PRIMARY KEY, btree (id)
"plugins_guide_address_uniq" UNIQUE, btree (country_id, region_id,
district_id, city_id, street_id, house)
"plugins_guide_address_alias_ru" btree (alias_ru)
"plugins_guide_address_city_id" btree (city_id)
"plugins_guide_address_code" btree (code)
"plugins_guide_address_country_id" btree (country_id)
"plugins_guide_address_district_id" btree (district_id)
"plugins_guide_address_house" btree (house)
"plugins_guide_address_house_upper" btree (upper(house::text))
"plugins_guide_address_region_id" btree (region_id)
"plugins_guide_address_significance" btree (significance)
"plugins_guide_address_street_id" btree (street_id)
"plugins_guide_address_zip_id" btree (zip_id)
Foreign-key constraints:
"plugins_guide_address_city_id_fkey" FOREIGN KEY (city_id)
REFERENCES plugins_guide_city(id) DEFERRABLE INITIALLY DEFERRED
"plugins_guide_address_country_id_fkey" FOREIGN KEY (country_id)
REFERENCES plugins_guide_country(id) DEFERRABLE INITIALLY DEFERRED
"plugins_guide_address_district_id_fkey" FOREIGN KEY (district_id)
REFERENCES plugins_guide_district(id) DEFERRABLE INITIALLY DEFERRED
"plugins_guide_address_region_id_fkey" FOREIGN KEY (region_id)
REFERENCES plugins_guide_region(id) DEFERRABLE INITIALLY DEFERRED
"plugins_guide_address_street_id_fkey" FOREIGN KEY (street_id)
REFERENCES plugins_guide_street(id) DEFERRABLE INITIALLY DEFERRED
"plugins_guide_address_zip_id_fkey" FOREIGN KEY (zip_id)
REFERENCES plugins_guide_zip(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "plugins_guide_ziphelper" CONSTRAINT
"plugins_guide_ziphelper_address_id_fkey" FOREIGN KEY (address_id)
REFERENCES plugins_guide_address(id) DEFERRABLE INITIALLY DEFERRED
TABLE "plugins_plugin_addr" CONSTRAINT
"plugins_plugin_addr_address_id_fkey" FOREIGN KEY (address_id)
REFERENCES plugins_guide_address(id) DEFERRABLE INITIALLY DEFERRED

------------end---------------