Bad plan after vacuum analyze - Mailing list pgsql-performance

Hi,

We have some performances problem on a particular query.

We reproduced the problem on a 7.4.5 and on a 7.4.7 server.
* we load the dump in a new database
* query: it's fast (< 1ms)
* VACUUM FULL ANALYZE;
* query: it's really slow (130ms) and it's another plan
* set enable_seqscan=off;
* query: it's fast (< 1ms) : it uses the best plan

I attached the EXPLAIN ANALYZE outputs, the query and the tables
description. I really can't understand why the planner chooses this plan
and especially the line :
->  Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t98
(cost=0.00..2554.07 rows=33510 width=81) (actual time=0.043..56.392
rows=33510 loops=1).
I never saw an index scan on such a number of lines. For your
information, there are 33510 lines in this table so it scans the whole
table.

The problem seems to be the left join on the acs_objects t98 table for
the parent_application_id as if I remove it or if I change it to a
subquery, it's ok. The query is automatically generated by a persistence
layer so I can't really rewrite it.

Thanks for any help

Regards

--
Guillaume

                                                                                  QUERY PLAN
                                                      

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Right Join  (cost=9.27..9.48 rows=1 width=545) (actual time=129.364..129.365 rows=1 loops=1)
   Merge Cond: ("outer".application_id = "inner".parent_application_id)
   ->  Index Scan using applicati_applicati_id_p_ogstm on applications t116  (cost=0.00..5.51 rows=28 width=20) (actual
time=0.030..0.073rows=28 loops=1) 
   ->  Sort  (cost=9.27..9.27 rows=1 width=529) (actual time=129.202..129.203 rows=1 loops=1)
         Sort Key: t22.parent_application_id
         ->  Merge Right Join  (cost=8.92..9.26 rows=1 width=529) (actual time=129.100..129.103 rows=1 loops=1)
               Merge Cond: ("outer".object_id = "inner".parent_application_id)
               ->  Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t98  (cost=0.00..2554.07 rows=33510
width=81)(actual time=0.043..56.392 rows=33510 loops=1) 
               ->  Sort  (cost=8.92..8.93 rows=1 width=452) (actual time=0.309..0.310 rows=1 loops=1)
                     Sort Key: t22.parent_application_id
                     ->  Nested Loop  (cost=2.44..8.91 rows=1 width=452) (actual time=0.259..0.287 rows=1 loops=1)
                           ->  Hash Join  (cost=2.44..3.68 rows=1 width=339) (actual time=0.227..0.251 rows=1 loops=1)
                                 Hash Cond: ("outer".application_type_id = "inner".application_type_id)
                                 ->  Seq Scan on application_types t47  (cost=0.00..1.15 rows=15 width=28) (actual
time=0.009..0.025rows=15 loops=1) 
                                 ->  Hash  (cost=2.44..2.44 rows=1 width=315) (actual time=0.121..0.121 rows=0 loops=1)
                                       ->  Hash Join  (cost=1.01..2.44 rows=1 width=315) (actual time=0.073..0.112
rows=1loops=1) 
                                             Hash Cond: ("outer".application_id = "inner".section_id)
                                             ->  Seq Scan on applications t22  (cost=0.00..1.28 rows=28 width=70)
(actualtime=0.002..0.027 rows=28 loops=1) 
                                             ->  Hash  (cost=1.01..1.01 rows=1 width=245) (actual time=0.026..0.026
rows=0loops=1) 
                                                   ->  Seq Scan on content_sections t0  (cost=0.00..1.01 rows=1
width=245)(actual time=0.020..0.022 rows=1 loops=1) 
                           ->  Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t7  (cost=0.00..5.22
rows=1width=121) (actual time=0.016..0.017 rows=1 loops=1) 
                                 Index Cond: ("outer".application_id = t7.object_id)
 Total runtime: 129.754 ms
(23 lignes)




SET
                                                                                     QUERY PLAN
                                                             

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=17.48..18.03 rows=1 width=545) (actual time=0.653..0.656 rows=1 loops=1)
   Merge Cond: ("outer".parent_application_id = "inner".application_id)
   ->  Merge Left Join  (cost=17.48..17.81 rows=1 width=529) (actual time=0.616..0.618 rows=1 loops=1)
         Merge Cond: ("outer".parent_application_id = "inner".object_id)
         ->  Sort  (cost=17.48..17.49 rows=1 width=452) (actual time=0.587..0.588 rows=1 loops=1)
               Sort Key: t22.parent_application_id
               ->  Nested Loop  (cost=4.68..17.47 rows=1 width=452) (actual time=0.424..0.484 rows=1 loops=1)
                     ->  Nested Loop  (cost=4.68..13.37 rows=1 width=428) (actual time=0.236..0.293 rows=1 loops=1)
                           ->  Hash Join  (cost=4.68..8.14 rows=1 width=315) (actual time=0.176..0.229 rows=1 loops=1)
                                 Hash Cond: ("outer".application_id = "inner".section_id)
                                 ->  Index Scan using applicati_parent_app_id_idx on applications t22  (cost=0.00..3.31
rows=28width=70) (actual time=0.029..0.074 rows=28 loops=1) 
                                 ->  Hash  (cost=4.68..4.68 rows=1 width=245) (actual time=0.045..0.045 rows=0 loops=1)
                                       ->  Index Scan using content_sections_pk on content_sections t0
(cost=0.00..4.68rows=1 width=245) (actual time=0.039..0.042 rows=1 loops=1) 
                           ->  Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t7  (cost=0.00..5.22
rows=1width=121) (actual time=0.049..0.050 rows=1 loops=1) 
                                 Index Cond: ("outer".application_id = t7.object_id)
                     ->  Index Scan using appli_typ_appli_typ_id_p_r5e8o on application_types t47  (cost=0.00..4.09
rows=1width=28) (actual time=0.175..0.177 rows=1 loops=1) 
                           Index Cond: ("outer".application_type_id = t47.application_type_id)
         ->  Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t98  (cost=0.00..2554.07 rows=33510
width=81)(actual time=0.009..0.009 rows=1 loops=1) 
   ->  Index Scan using applicati_applicati_id_p_ogstm on applications t116  (cost=0.00..5.51 rows=28 width=20) (actual
time=0.020..0.020rows=1 loops=1) 
 Total runtime: 1.001 ms
(20 lignes)




                                                                            QUERY PLAN
                                          

------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=2.44..17.36 rows=1 width=5532) (actual time=0.441..0.466 rows=1 loops=1)
   Join Filter: ("outer".parent_application_id = "inner".application_id)
   ->  Nested Loop Left Join  (cost=2.44..15.73 rows=1 width=5214) (actual time=0.378..0.402 rows=1 loops=1)
         ->  Nested Loop  (cost=2.44..9.70 rows=1 width=4878) (actual time=0.365..0.388 rows=1 loops=1)
               ->  Hash Join  (cost=2.44..3.68 rows=1 width=4228) (actual time=0.315..0.334 rows=1 loops=1)
                     Hash Cond: ("outer".application_type_id = "inner".application_type_id)
                     ->  Seq Scan on application_types t47  (cost=0.00..1.15 rows=15 width=326) (actual
time=0.037..0.051rows=15 loops=1) 
                     ->  Hash  (cost=2.44..2.44 rows=1 width=3906) (actual time=0.115..0.115 rows=0 loops=1)
                           ->  Hash Join  (cost=1.01..2.44 rows=1 width=3906) (actual time=0.065..0.104 rows=1 loops=1)
                                 Hash Cond: ("outer".application_id = "inner".section_id)
                                 ->  Seq Scan on applications t22  (cost=0.00..1.28 rows=28 width=1370) (actual
time=0.006..0.027rows=28 loops=1) 
                                 ->  Hash  (cost=1.01..1.01 rows=1 width=2536) (actual time=0.028..0.028 rows=0
loops=1)
                                       ->  Seq Scan on content_sections t0  (cost=0.00..1.01 rows=1 width=2536) (actual
time=0.018..0.020rows=1 loops=1) 
               ->  Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t7  (cost=0.00..6.01 rows=1 width=658)
(actualtime=0.037..0.039 rows=1 loops=1) 
                     Index Cond: ("outer".application_id = t7.object_id)
         ->  Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t98  (cost=0.00..6.01 rows=1 width=340)
(actualtime=0.001..0.001 rows=0 loops=1) 
               Index Cond: ("outer".parent_application_id = t98.object_id)
   ->  Seq Scan on applications t116  (cost=0.00..1.28 rows=28 width=322) (actual time=0.002..0.024 rows=28 loops=1)
 Total runtime: 0.775 ms
(19 lignes)




explain analyze select t0.section_id as "this.id",
                                    t7.object_type as "this.objectType",
                                    t7.display_name as "this.displayName",
                                    t7.default_domain_class as "this.defaultDomainClass",
                                    t22.title as "this.title",
                                    t22.description as "this.description",
                                    t22.timestamp as "this.timestamp",
                                    t22.application_type_id as "this.resourceType.id",
                                    t47.workspace_application_p as "this.resourceType.isWorkspace8",
                                    t47.has_full_page_view_p as "this.resourceType.hasFullPage9",
                                    t47.has_embedded_view_p as "this.resourceType.hasEmbedde10",
                                    t47.singleton_p as "this.resourceType.isSingleton",
                                    t47.title as "this.resourceType.title",
                                    t22.parent_application_id as "this.parentResource.id",
                                    t98.object_type as "this.parentResource.objectType",
                                    t98.default_domain_class as "this.parentResource.defaultD15",
                                    t116.title as "this.parentResource.title",
                                    t22.primary_url as "this.primaryURL",
                                    t0.pretty_name as "this.label",
                                    t0.page_resolver_class as "this.pageResolverClass",
                                    t0.item_resolver_class as "this.itemResolverClass",
                                    t0.template_resolver_class as "this.templateResolverClass",
                                    t0.xml_generator_class as "this.xmlGeneratorClass"
                                    from content_sections t0
                                    join acs_objects t7 on t0.section_id = t7.object_id
                                    join applications t22 on t0.section_id = t22.application_id
                                    join application_types t47 on t22.application_type_id = t47.application_type_id
                                    left join acs_objects t98 on t22.parent_application_id = t98.object_id
                                    left join applications t116 on t22.parent_application_id = t116.application_id
                                    where 1 = 1;



                Table "public.acs_objects"
        Column        |          Type          | Modifiers
----------------------+------------------------+-----------
 object_id            | integer                | not null
 object_type          | character varying(100) | not null
 display_name         | character varying(200) | not null
 default_domain_class | character varying(100) |
Indexes:
    "acs_objects_object_id_p_hhkb1" primary key, btree (object_id)
Triggers:
    acs_object_dnm_ctx_add_trg AFTER INSERT ON acs_objects FOR EACH ROW EXECUTE PROCEDURE acs_object_dnm_ctx_add_fn()
    acs_object_dnm_ctx_del_trg BEFORE DELETE ON acs_objects FOR EACH ROW EXECUTE PROCEDURE acs_object_dnm_ctx_del_fn()

                Table "public.content_sections"
         Column          |          Type           | Modifiers
-------------------------+-------------------------+-----------
 section_id              | integer                 | not null
 pretty_name             | character varying(300)  | not null
 root_folder_id          | integer                 | not null
 templates_folder_id     | integer                 |
 staff_group_id          | integer                 | not null
 viewers_group_id        | integer                 | not null
 page_resolver_class     | character varying(1000) | not null
 item_resolver_class     | character varying(1000) | not null
 template_resolver_class | character varying(1000) | not null
 xml_generator_class     | character varying(1000) | not null
Indexes:
    "content_sections_pk" primary key, btree (section_id)
    "csections_name_un" unique, btree (pretty_name)
    "content_sections_rt_folder_idx" btree (root_folder_id)
    "content_sections_staff_grp_idx" btree (staff_group_id)
    "content_sections_tp_folder_idx" btree (templates_folder_id)
    "content_sections_vwrs_grp_idx" btree (viewers_group_id)
Foreign-key constraints:
    "csections_viewers_group_id_fk" FOREIGN KEY (viewers_group_id) REFERENCES groups(group_id)
    "csections_staff_group_id_fk" FOREIGN KEY (staff_group_id) REFERENCES groups(group_id)
    "csections_temps_folder_id_fk" FOREIGN KEY (templates_folder_id) REFERENCES cms_folders(folder_id)
    "csections_root_folder_id_fk" FOREIGN KEY (root_folder_id) REFERENCES cms_folders(folder_id)
    "csections_section_id_fk" FOREIGN KEY (section_id) REFERENCES applications(application_id)

                 Table "public.applications"
        Column         |           Type           | Modifiers
-----------------------+--------------------------+-----------
 application_id        | integer                  | not null
 title                 | character varying(200)   |
 description           | character varying(4000)  |
 timestamp             | timestamp with time zone | not null
 application_type_id   | integer                  | not null
 parent_application_id | integer                  |
 cell_number           | integer                  |
 sort_key              | integer                  |
 primary_url           | character varying(4000)  |
 package_id            | integer                  |
Indexes:
    "applicati_applicati_id_p_ogstm" primary key, btree (application_id)
    "applicati_applicati_typ_id_idx" btree (application_type_id)
    "applicati_package_id_idx" btree (package_id)
    "applicati_parent_app_id_idx" btree (parent_application_id)
    "idx_test" btree (primary_url)
Foreign-key constraints:
    "application_package_id_f_cdaho" FOREIGN KEY (package_id) REFERENCES apm_packages(package_id)
    "applicati_applicati_id_f_a35g2" FOREIGN KEY (application_id) REFERENCES acs_objects(object_id)
    "applica_par_applica_id_f_hvxh7" FOREIGN KEY (parent_application_id) REFERENCES applications(application_id)
    "applica_applica_typ_id_f_k2bi3" FOREIGN KEY (application_type_id) REFERENCES
application_types(application_type_id)

               Table "public.application_types"
         Column          |          Type           | Modifiers
-------------------------+-------------------------+-----------
 application_type_id     | integer                 | not null
 object_type             | character varying(100)  | not null
 title                   | character varying(200)  |
 description             | character varying(4000) |
 workspace_application_p | boolean                 |
 has_full_page_view_p    | boolean                 |
 has_embedded_view_p     | boolean                 |
 singleton_p             | boolean                 |
 provider_id             | integer                 |
 profile                 | character varying(20)   |
 package_type_id         | integer                 |
Indexes:
    "appli_typ_appli_typ_id_p_r5e8o" primary key, btree (application_type_id)
    "applicat_typ_obje_type_u_pf2uk" unique, btree (object_type)
    "appli_typ_package_type_id_idx" btree (package_type_id)
    "appli_typ_provider_id_idx" btree (provider_id)
Foreign-key constraints:
    "applicat_typ_provid_id_f_bm274" FOREIGN KEY (provider_id) REFERENCES application_types(application_type_id)
    "applica_typ_pac_typ_id_f_v80ma" FOREIGN KEY (package_type_id) REFERENCES apm_package_types(package_type_id)