Re: significant slow down with various LIMIT

On Apr 13, 5:28 am, Kevin.Gritt...@wicourts.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---------------