Forums PostgreSQL.fr

F.Chanson

Membre

Explain Iincorrect via pgadmin 1.12.1

Bonjour
J'ai un  Pb avec l'explain issue de la version 1.12.1 de pgadmin, en effet alors que la requête ne demande que l'ID d'une donnée, il semble que Postgres remonte l'ensemble des infos de la table "imagefeatu4"

Voici le select
----------------
select
            dgproductf0_.id as col_0_0_
from
            DG_PRODUCT dgproductf0_
            inner join FEATURE dgproductf0_1_ on dgproductf0_.id=dgproductf0_1_.id
            inner join MD_METADATA metadatas1_ on dgproductf0_.id=metadatas1_.product
            inner join FEATURE metadatas1_1_ on metadatas1_.id=metadatas1_1_.id
            inner join METADATA_ID metadataid5_ on metadatas1_1_.fk_metadataID=metadataid5_.id
            inner join MD_METADATA metadatas2_ on dgproductf0_.id=metadatas2_.product
            inner join FEATURE metadatas2_1_ on metadatas2_.id=metadatas2_1_.id
            inner join CATALOG_ID catalogide3_ on metadatas2_.fk_catalogID=catalogide3_.ID
            inner join IMAGE_EXTENSION imagefeatu4_ on metadatas2_.fk_image_extension=imagefeatu4_.id
where
            catalogide3_.profile='LEVEL0'
            and metadatas2_.attribute_description='DATASTRIP' and
            dgproductf0_.archive_searchByAllUsers='true' and
            (imagefeatu4_.orbit between '12000' and '12100') and
            (metadataid5_.EXTERNAL_ID like 'DS%')
group by dgproductf0_.id

-------------- extrait  de l'explain ---------------------------------------
                        Output: catalogide3_.id, catalogide3_.familytype, catalogide3_.producttype, catalogide3_.profile, catalogide3_.template"
"                                                  Output: metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid"
"                                                        Output: imagefeatu4_.id, imagefeatu4_.bh_ratio, imagefeatu4_.accross_track_incidence_angle, imagefeatu4_.along_track_incidence_angle, imagefeatu4_.antimeridian_longitude_convention, imagefeatu4_.begin_viewing_date_time, imagefeatu4_.campagne, imagefeatu4_.center_viewing_date_time, imagefeatu4_.cloud_cover_confidence, imagefeatu4_.cloud_cover_notation_mode, imagefeatu4_.cloud_cover_percentage, imagefeatu4_.colour, imagefeatu4_.combined_mode, imagefeatu4_.x_name_type_code, imagefeatu4_.y_name_type_code, imagefeatu4_.elevation_angle, imagefeatu4_.end_viewing_date_time, imagefeatu4_.gain_number, imagefeatu4_.helios_gains_list, imagefeatu4_.helios_line_shift, imagefeatu4_.helios_loc_quality, imagefeatu4_.helios_mode_cu, imagefeatu4_.helios_retina, imagefeatu4_.helios_viewing_mode, imagefeatu4_.illumination_azimuth_angle, imagefeatu4_.illumination_elevation_angle, imagefeatu4_.incidence_angle, imagefeatu4_.orbit, imagefeatu4_.orbit_cycle, imagefeatu4_.orbit_direction, imagefeatu4_.orientation_angle, imagefeatu4_.pass_id, imagefeatu4_.pitch, imagefeatu4_.processing_level_code, imagefeatu4_.quality_code, imagefeatu4_.quality_notation_confidence, imagefeatu4_.quality_notation_mode, imagefeatu4_.quality_percentage, imagefeatu4_.received_aos, imagefeatu4_.received_los, imagefeatu4_.resolution_x, imagefeatu4_.resolution_y, imagefeatu4_.revolution, imagefeatu4_.roll, imagefeatu4_.scene_rank, imagefeatu4_.segment_max_global_angle, imagefeatu4_.segment_min_global_angle, imagefeatu4_.segment_rank, imagefeatu4_.sensor_operational_mode, imagefeatu4_.snow_cover_percentage, imagefeatu4_.swath, imagefeatu4_.swath_type, imagefeatu4_.viewing_id, imagefeatu4_.yaw, imagefeatu4_.fk_image_modelmin, imagefeatu4_.fk_line_quotation_2, imagefeatu4_.fk_line_quotation_1, imagefeatu4_.fk_line_quotation_4, imagefeatu4_.fk_image_modelmax, imagefeatu4_.fk_line_quotation_3, imagefeatu4_.fk_image_modelavg"

-------------- Explain  complet---------------------------------------
"HashAggregate  (cost=9504.04..9512.70 rows=866 width=37) (actual time=647.276..647.336 rows=92 loops=1)"
"  Output: dgproductf0_.id"
"  ->  Nested Loop  (cost=22.08..9501.88 rows=866 width=37) (actual time=0.603..646.121 rows=1046 loops=1)"
"        Output: dgproductf0_.id"
"        ->  Nested Loop  (cost=22.08..9484.93 rows=58 width=109) (actual time=0.584..632.887 rows=1046 loops=1)"
"              Output: dgproductf0_1_.id, metadatas2_.product, metadatas1_.product"
"              ->  Nested Loop  (cost=22.08..9461.34 rows=58 width=145) (actual time=0.568..618.476 rows=1046 loops=1)"
"                    Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas1_.product"
"                    Join Filter: ((metadatas2_.fk_catalogid)::text = (catalogide3_.id)::text)"
"                    ->  Index Scan using ib_t_catalogid_f_profile on catalog_id catalogide3_  (cost=0.00..6.27 rows=1 width=118) (actual time=0.011..0.012 rows=1 loops=1)"
"                          Output: catalogide3_.id, catalogide3_.familytype, catalogide3_.producttype, catalogide3_.profile, catalogide3_.template"
"                          Index Cond: ((profile)::text = 'LEVEL0'::text)"
"                    ->  Nested Loop  (cost=22.08..9454.35 rows=58 width=181) (actual time=0.550..616.805 rows=1046 loops=1)"
"                          Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid, metadatas1_.product"
"                          ->  Nested Loop  (cost=22.08..9088.66 rows=892 width=218) (actual time=0.306..373.054 rows=19860 loops=1)"
"                                Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid, metadatas1_.product, metadatas1_1_.fk_metadataid"
"                                ->  Nested Loop  (cost=22.08..8725.95 rows=892 width=217) (actual time=0.286..102.007 rows=19860 loops=1)"
"                                      Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid, metadatas1_.product, metadatas1_.id"
"                                      ->  Nested Loop  (cost=22.08..7756.15 rows=957 width=145) (actual time=0.240..31.983 rows=1046 loops=1)"
"                                            Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid"
"                                            ->  Nested Loop  (cost=22.08..7367.01 rows=957 width=108) (actual time=0.217..17.388 rows=1046 loops=1)"
"                                                  Output: metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid"
"                                                  ->  Bitmap Heap Scan on image_extension imagefeatu4_  (cost=22.08..2089.86 rows=957 width=37) (actual time=0.191..1.065 rows=1046 loops=1)"
"                                                        Output: imagefeatu4_.id, imagefeatu4_.bh_ratio, imagefeatu4_.accross_track_incidence_angle, imagefeatu4_.along_track_incidence_angle, imagefeatu4_.antimeridian_longitude_convention, imagefeatu4_.begin_viewing_date_time, imagefeatu4_.campagne, imagefeatu4_.center_viewing_date_time, imagefeatu4_.cloud_cover_confidence, imagefeatu4_.cloud_cover_notation_mode, imagefeatu4_.cloud_cover_percentage, imagefeatu4_.colour, imagefeatu4_.combined_mode, imagefeatu4_.x_name_type_code, imagefeatu4_.y_name_type_code, imagefeatu4_.elevation_angle, imagefeatu4_.end_viewing_date_time, imagefeatu4_.gain_number, imagefeatu4_.helios_gains_list, imagefeatu4_.helios_line_shift, imagefeatu4_.helios_loc_quality, imagefeatu4_.helios_mode_cu, imagefeatu4_.helios_retina, imagefeatu4_.helios_viewing_mode, imagefeatu4_.illumination_azimuth_angle, imagefeatu4_.illumination_elevation_angle, imagefeatu4_.incidence_angle, imagefeatu4_.orbit, imagefeatu4_.orbit_cycle, imagefeatu4_.orbit_direction, imagefeatu4_.orientation_angle, imagefeatu4_.pass_id, imagefeatu4_.pitch, imagefeatu4_.processing_level_code, imagefeatu4_.quality_code, imagefeatu4_.quality_notation_confidence, imagefeatu4_.quality_notation_mode, imagefeatu4_.quality_percentage, imagefeatu4_.received_aos, imagefeatu4_.received_los, imagefeatu4_.resolution_x, imagefeatu4_.resolution_y, imagefeatu4_.revolution, imagefeatu4_.roll, imagefeatu4_.scene_rank, imagefeatu4_.segment_max_global_angle, imagefeatu4_.segment_min_global_angle, imagefeatu4_.segment_rank, imagefeatu4_.sensor_operational_mode, imagefeatu4_.snow_cover_percentage, imagefeatu4_.swath, imagefeatu4_.swath_type, imagefeatu4_.viewing_id, imagefeatu4_.yaw, imagefeatu4_.fk_image_modelmin, imagefeatu4_.fk_line_quotation_2, imagefeatu4_.fk_line_quotation_1, imagefeatu4_.fk_line_quotation_4, imagefeatu4_.fk_image_modelmax, imagefeatu4_.fk_line_quotation_3, imagefeatu4_.fk_image_modelavg"
"                                                        Recheck Cond: ((orbit >= 12000) AND (orbit <= 12100))"
"                                                        ->  Bitmap Index Scan on ib_t_image_extension_f_orbit  (cost=0.00..21.84 rows=957 width=0) (actual time=0.171..0.171 rows=1046 loops=1)"
"                                                              Index Cond: ((orbit >= 12000) AND (orbit <= 12100))"
"                                                  ->  Index Scan using ib_t_md_metadata_fk_image_extension on md_metadata metadatas2_  (cost=0.00..5.50 rows=1 width=144) (actual time=0.011..0.012 rows=1 loops=1046)"
"                                                        Output: metadatas2_.id, metadatas2_.abstract_id, metadatas2_.access_count, metadatas2_.coord_path, metadatas2_.coord_row, metadatas2_.association_type, metadatas2_.attribute_description, metadatas2_.authority, metadatas2_.citationidentifier, metadatas2_.classification, metadatas2_.archive_format, metadatas2_.data_quality_lineage_process_step_source, metadatas2_.data_quality_process_step_source_description, metadatas2_.datestamp, metadatas2_.dimension_size, metadatas2_.edition, metadatas2_.file_identifier, metadatas2_.format, metadatas2_.format_edition, metadatas2_.crs, metadatas2_.geometry, metadatas2_.geographic_element_identifier, metadatas2_.gipp_parameter_id, metadatas2_.gipp_type, metadatas2_.identification_info_priority, metadatas2_.identification_info_status, metadatas2_.last_access_date, metadatas2_.local_language, metadatas2_.passive_mode, metadatas2_.position_in_product, metadatas2_.process_step_date_time_stamp, metadatas2_.processing_date_time, metadatas2_.resolution_equivalent_scale, metadatas2_.source_metadata_id, metadatas2_.spatial_representation_class, metadatas2_.title, metadatas2_.transfert_option_original_size, metadatas2_.usage_date_time, metadatas2_.fk_metadata_resp, metadatas2_.fk_acquisition, metadatas2_.product, metadatas2_.fk_srid, metadatas2_.fk_import_resp, metadatas2_.fk_image_extension, metadatas2_.fk_catalogid, metadatas2_.fk_source_resp"
"                                                        Index Cond: ((metadatas2_.fk_image_extension)::text = (imagefeatu4_.id)::text)"
"                                            ->  Index Scan using feature_pkey on feature dgproductf0_1_  (cost=0.00..0.39 rows=1 width=37) (actual time=0.010..0.011 rows=1 loops=1046)"
"                                                  Output: dgproductf0_1_.id, dgproductf0_1_.fk_metadataid"
"                                                  Index Cond: ((dgproductf0_1_.id)::text = (metadatas2_.product)::text)"
"                                      ->  Index Scan using ib_t_md_metadata_fk_product on md_metadata metadatas1_  (cost=0.00..0.83 rows=15 width=72) (actual time=0.019..0.034 rows=19 loops=1046)"
"                                            Output: metadatas1_.id, metadatas1_.abstract_id, metadatas1_.access_count, metadatas1_.coord_path, metadatas1_.coord_row, metadatas1_.association_type, metadatas1_.attribute_description, metadatas1_.authority, metadatas1_.citationidentifier, metadatas1_.classification, metadatas1_.archive_format, metadatas1_.data_quality_lineage_process_step_source, metadatas1_.data_quality_process_step_source_description, metadatas1_.datestamp, metadatas1_.dimension_size, metadatas1_.edition, metadatas1_.file_identifier, metadatas1_.format, metadatas1_.format_edition, metadatas1_.crs, metadatas1_.geometry, metadatas1_.geographic_element_identifier, metadatas1_.gipp_parameter_id, metadatas1_.gipp_type, metadatas1_.identification_info_priority, metadatas1_.identification_info_status, metadatas1_.last_access_date, metadatas1_.local_language, metadatas1_.passive_mode, metadatas1_.position_in_product, metadatas1_.process_step_date_time_stamp, metadatas1_.processing_date_time, metadatas1_.resolution_equivalent_scale, metadatas1_.source_metadata_id, metadatas1_.spatial_representation_class, metadatas1_.title, metadatas1_.transfert_option_original_size, metadatas1_.usage_date_time, metadatas1_.fk_metadata_resp, metadatas1_.fk_acquisition, metadatas1_.product, metadatas1_.fk_srid, metadatas1_.fk_import_resp, metadatas1_.fk_image_extension, metadatas1_.fk_catalogid, metadatas1_.fk_source_resp"
"                                            Index Cond: ((metadatas1_.product)::text = (dgproductf0_1_.id)::text)"
"                                ->  Index Scan using feature_pkey on feature metadatas1_1_  (cost=0.00..0.39 rows=1 width=74) (actual time=0.010..0.011 rows=1 loops=19860)"
"                                      Output: metadatas1_1_.id, metadatas1_1_.fk_metadataid"
"                                      Index Cond: ((metadatas1_1_.id)::text = (metadatas1_.id)::text)"
"                          ->  Index Scan using metadata_id_pkey on metadata_id metadataid5_  (cost=0.00..0.40 rows=1 width=37) (actual time=0.011..0.011 rows=0 loops=19860)"
"                                Output: metadataid5_.id, metadataid5_.accesses, metadataid5_.complete, metadataid5_.external_id, metadataid5_.insertion_date, metadataid5_.last_update_date"
"                                Index Cond: ((metadataid5_.id)::text = (metadatas1_1_.fk_metadataid)::text)"
"                                Filter: ((metadataid5_.external_id)::text ~~ 'DS%'::text)"
"              ->  Index Scan using feature_pkey on feature metadatas2_1_  (cost=0.00..0.39 rows=1 width=37) (actual time=0.010..0.011 rows=1 loops=1046)"
"                    Output: metadatas2_1_.id, metadatas2_1_.fk_metadataid"
"                    Index Cond: ((metadatas2_1_.id)::text = (metadatas2_.id)::text)"
"        ->  Index Scan using ib_t_dg_product_f_id on dg_product dgproductf0_  (cost=0.00..0.28 rows=1 width=37) (actual time=0.009..0.010 rows=1 loops=1046)"
"              Output: dgproductf0_.id, dgproductf0_.archive_id, dgproductf0_.archiving_center, dgproductf0_.archiving_date, dgproductf0_.archive_expiry, dgproductf0_.archive_key, dgproductf0_.last_updade_date, dgproductf0_.archive_nearline, dgproductf0_.archive_offline, dgproductf0_.archive_online, dgproductf0_.publication_date, dgproductf0_.archive_searchbyallusers, dgproductf0_.archive_size, dgproductf0_.lock_owner, dgproductf0_.locked"
"              Index Cond: ((dgproductf0_.id)::text = (dgproductf0_1_.id)::text)"
"              Filter: dgproductf0_.archive_searchbyallusers"
"Total runtime: 647.571 ms"

Marc Cousin

Membre

Re : Explain Iincorrect via pgadmin 1.12.1

Le parcours d'une table rapporte toutes les colonnes de la table. Par contre, ces colonnes ne sont pas consommées par les noeuds au dessus, si vous regardez bien le plan. Elles sont donc 'jetées' si elles ne servent à rien.

---

Marc.

F.Chanson

Membre

Re : Explain Iincorrect via pgadmin 1.12.1

Si ces colonnes comportent des champs de type bytea (donc volumineux dans mon cas des images)? Postgres remonte ces champs ?? donc cela a automatiquement  une influence sur les perfs si la sous-requête requête remonte beaucoup de résultats (ici 1046 rows ?)

Marc Cousin

Membre

Re : Explain Iincorrect via pgadmin 1.12.1

En fait, je ne comprends pas bien comment vous avez cette sortie précise. Je n'arrive pas à la reproduire.

Je ne connais pas trop pgadmin… je me suis probablement un peu avancé en regardant le plan.

Pourriez-vous me retourner le résultat de:

EXPLAIN VERBOSE select
            dgproductf0_.id as col_0_0_
from
            DG_PRODUCT dgproductf0_
            inner join FEATURE dgproductf0_1_ on dgproductf0_.id=dgproductf0_1_.id
            inner join MD_METADATA metadatas1_ on dgproductf0_.id=metadatas1_.product
            inner join FEATURE metadatas1_1_ on metadatas1_.id=metadatas1_1_.id
            inner join METADATA_ID metadataid5_ on metadatas1_1_.fk_metadataID=metadataid5_.id
            inner join MD_METADATA metadatas2_ on dgproductf0_.id=metadatas2_.product
            inner join FEATURE metadatas2_1_ on metadatas2_.id=metadatas2_1_.id
            inner join CATALOG_ID catalogide3_ on metadatas2_.fk_catalogID=catalogide3_.ID
            inner join IMAGE_EXTENSION imagefeatu4_ on metadatas2_.fk_image_extension=imagefeatu4_.id
where
            catalogide3_.profile='LEVEL0'
            and metadatas2_.attribute_description='DATASTRIP' and
            dgproductf0_.archive_searchByAllUsers='true' and
            (imagefeatu4_.orbit between '12000' and '12100') and
            (metadataid5_.EXTERNAL_ID like 'DS%')
group by dgproductf0_.id

En tout cas, si votre champ est un bytea, il est probable qu'il ne sera pas stocké dans la table de toutes façons, mais dans une table «toast». Les 'gros' champs (>2k) sont envoyés dans une table extérieure, justement pour ne pas avoir le problème que vous décrivez.

Envoyez quand même le explain verbose …

---

Marc.

Marc Cousin

Membre

Re : Explain Iincorrect via pgadmin 1.12.1

Voici un exemple de jointure… On voit bien que seule la colonne a remonte. Par contre je ne sais pas d'où sortent les lignes supplémentaires de votre explain, que je ne retrouve pas dans le mien.

marc=# EXPLAIN VERBOSE SELECT t1.a from test t1 join test t2 on (t1.a=t2.a) where t1.a<10000;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
Hash Join  (cost=20642.44..66525.99 rows=10555 width=4)
   Output: t1.a
   Hash Cond: (t2.a = t1.a)
   ->  Seq Scan on public.test t2  (cost=0.00..37028.00 rows=1000000 width=4)
         Output: t2.a
   ->  Hash  (cost=20510.50..20510.50 rows=10555 width=4)
         Output: t1.a
         ->  Bitmap Heap Scan on public.test t1  (cost=198.16..20510.50 rows=10555 width=4)
               Output: t1.a
               Recheck Cond: (t1.a < 10000)
               ->  Bitmap Index Scan on tst1  (cost=0.00..195.52 rows=10555 width=0)
                     Index Cond: (t1.a < 10000)

Dernière modification par Marc Cousin (23/12/2010 17:11:29)

---

Marc.

F.Chanson

Membre

Re : Explain Iincorrect via pgadmin 1.12.1

sous pgadmin

EXPLAIN VERBOSE select
            dgproductf0_.id as col_0_0_
from
            DG_PRODUCT dgproductf0_
            inner join FEATURE dgproductf0_1_ on dgproductf0_.id=dgproductf0_1_.id
            inner join MD_METADATA metadatas1_ on dgproductf0_.id=metadatas1_.product
            inner join FEATURE metadatas1_1_ on metadatas1_.id=metadatas1_1_.id
            inner join METADATA_ID metadataid5_ on metadatas1_1_.fk_metadataID=metadataid5_.id
            inner join MD_METADATA metadatas2_ on dgproductf0_.id=metadatas2_.product
            inner join FEATURE metadatas2_1_ on metadatas2_.id=metadatas2_1_.id
            inner join CATALOG_ID catalogide3_ on metadatas2_.fk_catalogID=catalogide3_.ID
            inner join IMAGE_EXTENSION imagefeatu4_ on metadatas2_.fk_image_extension=imagefeatu4_.id
where
            catalogide3_.profile='LEVEL0'
            and metadatas2_.attribute_description='DATASTRIP' and
            dgproductf0_.archive_searchByAllUsers='true' and
            (imagefeatu4_.orbit between '12000' and '12100') and
            (metadataid5_.EXTERNAL_ID like 'DS%')
group by dgproductf0_.id

"HashAggregate  (cost=13859.60..13868.26 rows=866 width=37)"
"  Output: dgproductf0_.id"
"  ->  Nested Loop  (cost=22.08..13857.44 rows=866 width=37)"
"        Output: dgproductf0_.id"
"        ->  Nested Loop  (cost=22.08..13833.87 rows=58 width=109)"
"              Output: dgproductf0_1_.id, metadatas2_.product, metadatas1_.product"
"              ->  Nested Loop  (cost=22.08..13752.29 rows=58 width=145)"
"                    Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas1_.product"
"                    Join Filter: ((metadatas2_.fk_catalogid)::text = (catalogide3_.id)::text)"
"                    ->  Index Scan using ib_t_catalogid_f_profile on catalog_id catalogide3_  (cost=0.00..6.27 rows=1 width=118)"
"                          Output: catalogide3_.id, catalogide3_.familytype, catalogide3_.producttype, catalogide3_.profile, catalogide3_.template"
"                          Index Cond: ((profile)::text = 'LEVEL0'::text)"
"                    ->  Nested Loop  (cost=22.08..13745.29 rows=58 width=181)"
"                          Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid, metadatas1_.product"
"                          ->  Nested Loop  (cost=22.08..13379.61 rows=892 width=218)"
"                                Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid, metadatas1_.product, metadatas1_1_.fk_metadataid"
"                                ->  Nested Loop  (cost=22.08..13016.89 rows=892 width=217)"
"                                      Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid, metadatas1_.product, metadatas1_.id"
"                                      ->  Nested Loop  (cost=22.08..8715.53 rows=957 width=145)"
"                                            Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid"
"                                            ->  Nested Loop  (cost=22.08..7369.40 rows=957 width=108)"
"                                                  Output: metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid"
"                                                  ->  Bitmap Heap Scan on image_extension imagefeatu4_  (cost=22.08..2089.86 rows=957 width=37)"
"                                                        Output: imagefeatu4_.id, imagefeatu4_.bh_ratio, imagefeatu4_.accross_track_incidence_angle, imagefeatu4_.along_track_incidence_angle, imagefeatu4_.antimeridian_longitude_convention, imagefeatu4_.begin_viewing_date_time, imagefeatu4_.campagne, imagefeatu4_.center_viewing_date_time, imagefeatu4_.cloud_cover_confidence, imagefeatu4_.cloud_cover_notation_mode, imagefeatu4_.cloud_cover_percentage, imagefeatu4_.colour, imagefeatu4_.combined_mode, imagefeatu4_.x_name_type_code, imagefeatu4_.y_name_type_code, imagefeatu4_.elevation_angle, imagefeatu4_.end_viewing_date_time, imagefeatu4_.gain_number, imagefeatu4_.helios_gains_list, imagefeatu4_.helios_line_shift, imagefeatu4_.helios_loc_quality, imagefeatu4_.helios_mode_cu, imagefeatu4_.helios_retina, imagefeatu4_.helios_viewing_mode, imagefeatu4_.illumination_azimuth_angle, imagefeatu4_.illumination_elevation_angle, imagefeatu4_.incidence_angle, imagefeatu4_.orbit, imagefeatu4_.orbit_cycle, imagefeatu4_.orbit_direction, imagefeatu4_.orientation_angle, imagefeatu4_.pass_id, imagefeatu4_.pitch, imagefeatu4_.processing_level_code, imagefeatu4_.quality_code, imagefeatu4_.quality_notation_confidence, imagefeatu4_.quality_notation_mode, imagefeatu4_.quality_percentage, imagefeatu4_.received_aos, imagefeatu4_.received_los, imagefeatu4_.resolution_x, imagefeatu4_.resolution_y, imagefeatu4_.revolution, imagefeatu4_.roll, imagefeatu4_.scene_rank, imagefeatu4_.segment_max_global_angle, imagefeatu4_.segment_min_global_angle, imagefeatu4_.segment_rank, imagefeatu4_.sensor_operational_mode, imagefeatu4_.snow_cover_percentage, imagefeatu4_.swath, imagefeatu4_.swath_type, imagefeatu4_.viewing_id, imagefeatu4_.yaw, imagefeatu4_.fk_image_modelmin, imagefeatu4_.fk_line_quotation_2, imagefeatu4_.fk_line_quotation_1, imagefeatu4_.fk_line_quotation_4, imagefeatu4_.fk_image_modelmax, imagefeatu4_.fk_line_quotation_3, imagefeatu4_.fk_image_modelavg"
"                                                        Recheck Cond: ((orbit >= 12000) AND (orbit <= 12100))"
"                                                        ->  Bitmap Index Scan on ib_t_image_extension_f_orbit  (cost=0.00..21.84 rows=957 width=0)"
"                                                              Index Cond: ((orbit >= 12000) AND (orbit <= 12100))"
"                                                  ->  Index Scan using ib_t_md_metadata_fk_image_extension on md_metadata metadatas2_  (cost=0.00..5.50 rows=1 width=144)"
"                                                        Output: metadatas2_.id, metadatas2_.abstract_id, metadatas2_.access_count, metadatas2_.coord_path, metadatas2_.coord_row, metadatas2_.association_type, metadatas2_.attribute_description, metadatas2_.authority, metadatas2_.citationidentifier, metadatas2_.classification, metadatas2_.archive_format, metadatas2_.data_quality_lineage_process_step_source, metadatas2_.data_quality_process_step_source_description, metadatas2_.datestamp, metadatas2_.dimension_size, metadatas2_.edition, metadatas2_.file_identifier, metadatas2_.format, metadatas2_.format_edition, metadatas2_.crs, metadatas2_.geometry, metadatas2_.geographic_element_identifier, metadatas2_.gipp_parameter_id, metadatas2_.gipp_type, metadatas2_.identification_info_priority, metadatas2_.identification_info_status, metadatas2_.last_access_date, metadatas2_.local_language, metadatas2_.passive_mode, metadatas2_.position_in_product, metadatas2_.process_step_date_time_stamp, metadatas2_.processing_date_time, metadatas2_.resolution_equivalent_scale, metadatas2_.source_metadata_id, metadatas2_.spatial_representation_class, metadatas2_.title, metadatas2_.transfert_option_original_size, metadatas2_.usage_date_time, metadatas2_.fk_metadata_resp, metadatas2_.fk_acquisition, metadatas2_.product, metadatas2_.fk_srid, metadatas2_.fk_import_resp, metadatas2_.fk_image_extension, metadatas2_.fk_catalogid, metadatas2_.fk_source_resp"
"                                                        Index Cond: ((metadatas2_.fk_image_extension)::text = (imagefeatu4_.id)::text)"
"                                                        Filter: ((metadatas2_.attribute_description)::text = 'DATASTRIP'::text)"
"                                            ->  Index Scan using feature_pkey on feature dgproductf0_1_  (cost=0.00..1.39 rows=1 width=37)"
"                                                  Output: dgproductf0_1_.id, dgproductf0_1_.fk_metadataid"
"                                                  Index Cond: ((dgproductf0_1_.id)::text = (metadatas2_.product)::text)"
"                                      ->  Index Scan using ib_t_md_metadata_fk_product on md_metadata metadatas1_  (cost=0.00..4.31 rows=15 width=72)"
"                                            Output: metadatas1_.id, metadatas1_.abstract_id, metadatas1_.access_count, metadatas1_.coord_path, metadatas1_.coord_row, metadatas1_.association_type, metadatas1_.attribute_description, metadatas1_.authority, metadatas1_.citationidentifier, metadatas1_.classification, metadatas1_.archive_format, metadatas1_.data_quality_lineage_process_step_source, metadatas1_.data_quality_process_step_source_description, metadatas1_.datestamp, metadatas1_.dimension_size, metadatas1_.edition, metadatas1_.file_identifier, metadatas1_.format, metadatas1_.format_edition, metadatas1_.crs, metadatas1_.geometry, metadatas1_.geographic_element_identifier, metadatas1_.gipp_parameter_id, metadatas1_.gipp_type, metadatas1_.identification_info_priority, metadatas1_.identification_info_status, metadatas1_.last_access_date, metadatas1_.local_language, metadatas1_.passive_mode, metadatas1_.position_in_product, metadatas1_.process_step_date_time_stamp, metadatas1_.processing_date_time, metadatas1_.resolution_equivalent_scale, metadatas1_.source_metadata_id, metadatas1_.spatial_representation_class, metadatas1_.title, metadatas1_.transfert_option_original_size, metadatas1_.usage_date_time, metadatas1_.fk_metadata_resp, metadatas1_.fk_acquisition, metadatas1_.product, metadatas1_.fk_srid, metadatas1_.fk_import_resp, metadatas1_.fk_image_extension, metadatas1_.fk_catalogid, metadatas1_.fk_source_resp"
"                                            Index Cond: ((metadatas1_.product)::text = (dgproductf0_1_.id)::text)"
"                                ->  Index Scan using feature_pkey on feature metadatas1_1_  (cost=0.00..0.39 rows=1 width=74)"
"                                      Output: metadatas1_1_.id, metadatas1_1_.fk_metadataid"
"                                      Index Cond: ((metadatas1_1_.id)::text = (metadatas1_.id)::text)"
"                          ->  Index Scan using metadata_id_pkey on metadata_id metadataid5_  (cost=0.00..0.40 rows=1 width=37)"
"                                Output: metadataid5_.id, metadataid5_.accesses, metadataid5_.complete, metadataid5_.external_id, metadataid5_.insertion_date, metadataid5_.last_update_date"
"                                Index Cond: ((metadataid5_.id)::text = (metadatas1_1_.fk_metadataid)::text)"
"                                Filter: ((metadataid5_.external_id)::text ~~ 'DS%'::text)"
"              ->  Index Scan using feature_pkey on feature metadatas2_1_  (cost=0.00..1.39 rows=1 width=37)"
"                    Output: metadatas2_1_.id, metadatas2_1_.fk_metadataid"
"                    Index Cond: ((metadatas2_1_.id)::text = (metadatas2_.id)::text)"
"        ->  Index Scan using ib_t_dg_product_f_id on dg_product dgproductf0_  (cost=0.00..0.39 rows=1 width=37)"
"              Output: dgproductf0_.id, dgproductf0_.archive_id, dgproductf0_.archiving_center, dgproductf0_.archiving_date, dgproductf0_.archive_expiry, dgproductf0_.archive_key, dgproductf0_.last_updade_date, dgproductf0_.archive_nearline, dgproductf0_.archive_offline, dgproductf0_.archive_online, dgproductf0_.publication_date, dgproductf0_.archive_searchbyallusers, dgproductf0_.archive_size, dgproductf0_.lock_owner, dgproductf0_.locked"
"              Index Cond: ((dgproductf0_.id)::text = (dgproductf0_1_.id)::text)"
"              Filter: dgproductf0_.archive_searchbyallusers"

F.Chanson

Membre

Re : Explain Iincorrect via pgadmin 1.12.1

Directement sous Postgres 844
-------------------------------
HashAggregate  (cost=14004.11..14012.79 rows=868 width=37)
   Output: dgproductf0_.id
   ->  Nested Loop  (cost=22.18..14001.94 rows=868 width=37)
         Output: dgproductf0_.id
         ->  Nested Loop  (cost=22.18..13977.89 rows=59 width=109)
               Output: dgproductf0_1_.id, metadatas2_.product, metadatas1_.product
               ->  Nested Loop  (cost=22.18..13894.80 rows=59 width=145)
                     Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas1_.product
                     Join Filter: ((metadatas2_.fk_catalogid)::text = (catalogide3_.id)::text)
                     ->  Index Scan using ib_t_catalogid_f_profile on catalog_id catalogide3_  (cost=0.00..6.27 rows=1 width=118)
                           Output: catalogide3_.id, catalogide3_.familytype, catalogide3_.producttype, catalogide3_.profile, catalogide3_.template
                           Index Cond: ((profile)::text = 'LEVEL0'::text)
                     ->  Nested Loop  (cost=22.18..13887.79 rows=59 width=181)
                           Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid, metadatas1_.product
                           ->  Nested Loop  (cost=22.18..13517.94 rows=901 width=218)
                                 Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid, metadatas1_.product, metadatas1_1_.fk_metadataid
                                 ->  Nested Loop  (cost=22.18..13150.95 rows=901 width=217)
                                       Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid, metadatas1_.product, metadatas1_.id
                                       ->  Nested Loop  (cost=22.18..8804.11 rows=967 width=145)
                                             Output: dgproductf0_1_.id, metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid
                                             ->  Nested Loop  (cost=22.18..7442.23 rows=967 width=108)
                                                   Output: metadatas2_.product, metadatas2_.id, metadatas2_.fk_catalogid
                                                   ->  Bitmap Heap Scan on image_extension imagefeatu4_  (cost=22.18..2110.89 rows=967 width=37)
                                                         Output: imagefeatu4_.id, imagefeatu4_.bh_ratio, imagefeatu4_.accross_track_incidence_angle, imagefeatu4_.along_track_incidence_angle, imagefeatu4_.antimeridian_longitude_convention, imagefeatu4_.begin_viewing_date_time, imagefeatu4_.campagne, imagefeatu4_.center_viewing_date_time, imagefeatu4_.cloud_cover_confidence, image
featu4_.cloud_cover_notation_mode, imagefeatu4_.cloud_cover_percentage, imagefeatu4_.colour, imagefeatu4_.combined_mode, imagefeatu4_.x_name_type_code, imagefeatu4_.y_name_type_code, imagefeatu4_.elevation_angle, imagefeatu4_.end_viewing_date_time, imagefeatu4_.gain_number, imagefeatu4_.helios_gains_list, imagefeatu4_.helios_line_shift, imagefeatu4_.helios_loc_quality, imagefeat
u4_.helios_mode_cu, imagefeatu4_.helios_retina, imagefeatu4_.helios_viewing_mode, imagefeatu4_.illumination_azimuth_angle, imagefeatu4_.illumination_elevation_angle, imagefeatu4_.incidence_angle, imagefeatu4_.orbit, imagefeatu4_.orbit_cycle, imagefeatu4_.orbit_direction, imagefeatu4_.orientation_angle, imagefeatu4_.pass_id, imagefeatu4_.pitch, imagefeatu4_.processing_level_code,
imagefeatu4_.quality_code, imagefeatu4_.quality_notation_confidence, imagefeatu4_.quality_notation_mode, imagefeatu4_.quality_percentage, imagefeatu4_.received_aos, imagefeatu4_.received_los, imagefeatu4_.resolution_x, imagefeatu4_.resolution_y, imagefeatu4_.revolution, imagefeatu4_.roll, imagefeatu4_.scene_rank, imagefeatu4_.segment_max_global_angle, imagefeatu4_.segment_min_g
lobal_angle, imagefeatu4_.segment_rank, imagefeatu4_.sensor_operational_mode, imagefeatu4_.snow_cover_percentage, imagefeatu4_.swath, imagefeatu4_.swath_type, imagefeatu4_.viewing_id, imagefeatu4_.yaw, imagefeatu4_.fk_image_modelmin, imagefeatu4_.fk_line_quotation_2, imagefeatu4_.fk_line_quotation_1, imagefeatu4_.fk_line_quotation_4, imagefeatu4_.fk_image_modelmax, imagefeatu4_.
fk_line_quotation_3, imagefeatu4_.fk_image_modelavg
                                                         Recheck Cond: ((orbit >= 12000) AND (orbit <= 12100))
                                                         ->  Bitmap Index Scan on ib_t_image_extension_f_orbit  (cost=0.00..21.94 rows=967 width=0)
                                                               Index Cond: ((orbit >= 12000) AND (orbit <= 12100))
                                                   ->  Index Scan using ib_t_md_metadata_fk_image_extension on md_metadata metadatas2_  (cost=0.00..5.50 rows=1 width=144)
                                                         Output: metadatas2_.id, metadatas2_.abstract_id, metadatas2_.access_count, metadatas2_.coord_path, metadatas2_.coord_row, metadatas2_.association_type, metadatas2_.attribute_description, metadatas2_.authority, metadatas2_.citationidentifier, metadatas2_.classification, metadatas2_.archive_format, metadatas2_.data_quality_l
ineage_process_step_source, metadatas2_.data_quality_process_step_source_description, metadatas2_.datestamp, metadatas2_.dimension_size, metadatas2_.edition, metadatas2_.file_identifier, metadatas2_.format, metadatas2_.format_edition, metadatas2_.crs, metadatas2_.geometry, metadatas2_.geographic_element_identifier, metadatas2_.gipp_parameter_id, metadatas2_.gipp_type, metadatas2
_.identification_info_priority, metadatas2_.identification_info_status, metadatas2_.last_access_date, metadatas2_.local_language, metadatas2_.passive_mode, metadatas2_.position_in_product, metadatas2_.process_step_date_time_stamp, metadatas2_.processing_date_time, metadatas2_.resolution_equivalent_scale, metadatas2_.source_metadata_id, metadatas2_.spatial_representation_class, m
etadatas2_.title, metadatas2_.transfert_option_original_size, metadatas2_.usage_date_time, metadatas2_.fk_metadata_resp, metadatas2_.fk_acquisition, metadatas2_.product, metadatas2_.fk_srid, metadatas2_.fk_import_resp, metadatas2_.fk_image_extension, metadatas2_.fk_catalogid, metadatas2_.fk_source_resp
                                                         Index Cond: ((metadatas2_.fk_image_extension)::text = (imagefeatu4_.id)::text)
                                                         Filter: ((metadatas2_.attribute_description)::text = 'DATASTRIP'::text)
                                             ->  Index Scan using feature_pkey on feature dgproductf0_1_  (cost=0.00..1.40 rows=1 width=37)
                                                   Output: dgproductf0_1_.id, dgproductf0_1_.fk_metadataid
                                                   Index Cond: ((dgproductf0_1_.id)::text = (metadatas2_.product)::text)
                                       ->  Index Scan using ib_t_md_metadata_fk_product on md_metadata metadatas1_  (cost=0.00..4.31 rows=15 width=72)
                                             Output: metadatas1_.id, metadatas1_.abstract_id, metadatas1_.access_count, metadatas1_.coord_path, metadatas1_.coord_row, metadatas1_.association_type, metadatas1_.attribute_description, metadatas1_.authority, metadatas1_.citationidentifier, metadatas1_.classification, metadatas1_.archive_format, metadatas1_.data_quality_lineage_proce
ss_step_source, metadatas1_.data_quality_process_step_source_description, metadatas1_.datestamp, metadatas1_.dimension_size, metadatas1_.edition, metadatas1_.file_identifier, metadatas1_.format, metadatas1_.format_edition, metadatas1_.crs, metadatas1_.geometry, metadatas1_.geographic_element_identifier, metadatas1_.gipp_parameter_id, metadatas1_.gipp_type, metadatas1_.identifica
tion_info_priority, metadatas1_.identification_info_status, metadatas1_.last_access_date, metadatas1_.local_language, metadatas1_.passive_mode, metadatas1_.position_in_product, metadatas1_.process_step_date_time_stamp, metadatas1_.processing_date_time, metadatas1_.resolution_equivalent_scale, metadatas1_.source_metadata_id, metadatas1_.spatial_representation_class, metadatas1_.t
itle, metadatas1_.transfert_option_original_size, metadatas1_.usage_date_time, metadatas1_.fk_metadata_resp, metadatas1_.fk_acquisition, metadatas1_.product, metadatas1_.fk_srid, metadatas1_.fk_import_resp, metadatas1_.fk_image_extension, metadatas1_.fk_catalogid, metadatas1_.fk_source_resp
                                             Index Cond: ((metadatas1_.product)::text = (dgproductf0_1_.id)::text)
                                 ->  Index Scan using feature_pkey on feature metadatas1_1_  (cost=0.00..0.39 rows=1 width=74)
                                       Output: metadatas1_1_.id, metadatas1_1_.fk_metadataid
                                       Index Cond: ((metadatas1_1_.id)::text = (metadatas1_.id)::text)
                           ->  Index Scan using metadata_id_pkey on metadata_id metadataid5_  (cost=0.00..0.40 rows=1 width=37)
                                 Output: metadataid5_.id, metadataid5_.accesses, metadataid5_.complete, metadataid5_.external_id, metadataid5_.insertion_date, metadataid5_.last_update_date
                                 Index Cond: ((metadataid5_.id)::text = (metadatas1_1_.fk_metadataid)::text)
                                 Filter: ((metadataid5_.external_id)::text ~~ 'DS%'::text)
               ->  Index Scan using feature_pkey on feature metadatas2_1_  (cost=0.00..1.40 rows=1 width=37)
                     Output: metadatas2_1_.id, metadatas2_1_.fk_metadataid
                     Index Cond: ((metadatas2_1_.id)::text = (metadatas2_.id)::text)
         ->  Index Scan using ib_t_dg_product_f_id on dg_product dgproductf0_  (cost=0.00..0.40 rows=1 width=37)
               Output: dgproductf0_.id, dgproductf0_.archive_id, dgproductf0_.archiving_center, dgproductf0_.archiving_date, dgproductf0_.archive_expiry, dgproductf0_.archive_key, dgproductf0_.last_updade_date, dgproductf0_.archive_nearline, dgproductf0_.archive_offline, dgproductf0_.archive_online, dgproductf0_.publication_date, dgproductf0_.archive_searchbyallusers, dgproductf
0_.archive_size, dgproductf0_.lock_owner, dgproductf0_.locked
               Index Cond: ((dgproductf0_.id)::text = (dgproductf0_1_.id)::text)
               Filter: dgproductf0_.archive_searchbyallusers
(51 rows)

Marc Cousin

Membre

Re : Explain Iincorrect via pgadmin 1.12.1

Je comprends la différence d'apparence: l'affichage 'verbose' a changé entre la 8.4 et la 9.0.

Je n'ai pas trop le temps de chercher à vérifier qu'il ne fait pas n'importe quoi dans votre cas (il faudrait monter un cas similaire, ce serait très long), mais voici une démonstration du fait qu'il affiche des données pouvant porter à confusion en 8.4…

J'ai créé une table avec un int, et un gros bytea (donc 4 octets pour le premier champ, 800k pour le second). On doit donc avoir des différences monumentales en termes de temps d'exécution suivant qu'on va regarder le contenu de la seconde colonne ou non.

EXPLAIN VERBOSE SELECT count(a) from test where a<10000;
                                 QUERY PLAN                                 
-----------------------------------------------------------------------------
Aggregate  (cost=2650.94..2650.95 rows=1 width=4)
   Output: count(a)
   ->  Bitmap Heap Scan on test  (cost=2625.96..2649.11 rows=732 width=4)
         Output: a, b
         Recheck Cond: (a < 10000)
         ->  Bitmap Index Scan on tst  (cost=0.00..2625.78 rows=732 width=0)
               Index Cond: (a < 10000)

=> D'après le plan, cette requête va aller inspecter le bytea. Normalement, pas besoin (c'est un gros bytea, il a été stocké dans une table «TOAST», et son contenu ne nous intéresse pas)

SELECT count(a) from test where a<10000;
count
-------
   731
(1 row)

Time: 0,753 ms

Pas mal pour lire 731*800ko…

Comparons maintenant à une requête du même genre, mais où on va le forcer à inspecter le contenu de b:

EXPLAIN VERBOSE SELECT count(a),count(distinct b) from test where a<10000;
                                 QUERY PLAN                                 
-----------------------------------------------------------------------------
Aggregate  (cost=2652.77..2652.78 rows=1 width=132)
   Output: count(a), count(DISTINCT b)
   ->  Bitmap Heap Scan on test  (cost=2625.96..2649.11 rows=732 width=132)
         Output: a, b
         Recheck Cond: (a < 10000)
         ->  Bitmap Index Scan on tst  (cost=0.00..2625.78 rows=732 width=0)
               Index Cond: (a < 10000)

Même plan, à part un count(distinct)

SELECT count(a),count(distinct b) from test where a<10000;
count | count
-------+-------
   731 |     1
(1 row)

Time: 3571,692 ms

Si on regarde le détail du plan d'exécution pour vérifier que ce n'est pas la fonction d'aggregat qui coute (je ne le mets pas ici, c'est déjà assez illisible comme ça), on constate que la cette seconde requête, contrairement à la précédente, a eu besoin d'aller lire 22000 blocs de données sur le disque.

C'est donc simplement un problème d'affichage des colonnes. La version 9.0 est plus claire de ce point de vue.

PS: une dernière sur la route (la raison pour laquelle j'ai mis tant de temps à répondre ):

SELECT count(a),sum(length(b)) from test where a<10000;
count |    sum   
-------+-----------
  1024 | 174182400
(1 row)

Time: 0,757 ms

On lui demande de calculer la longueur de b, et pourtant c'est instantané. La raison, c'est que cette longueur est stockée dans la table principale. Pas besoin d'aller lire le contenu du toast pour la connaître.

EXPLAIN VERBOSE SELECT count(a),sum(length(b)) from test where a<10000;
                                 QUERY PLAN                                 
-----------------------------------------------------------------------------
Aggregate  (cost=2652.77..2652.79 rows=1 width=132)
   Output: count(a), sum(length(b))
   ->  Bitmap Heap Scan on test  (cost=2625.96..2649.11 rows=732 width=132)
         Output: a, b
         Recheck Cond: (a < 10000)
         ->  Bitmap Index Scan on tst  (cost=0.00..2625.78 rows=732 width=0)
               Index Cond: (a < 10000)

Et pourtant, le plan est toujours le même

=> Ne pas se fier aux colonnes, elles ne veulent pas forcément dire que l'exécuteur de requête va aller chercher l'enregistrement et le lire. Il est bien plus malin qu'il ne le laisserait croire avec le seul plan d'exécution.

---

Marc.

F.Chanson

Membre

Re : Explain Iincorrect via pgadmin 1.12.1

Merci,  c'est nettement plus clair maintenant
Bonnes Fêtes