Vous n'êtes pas identifié(e).
Concernant la classe d'opérateur pour la création de l'index , la clause where ne fait que des "=" , une index avec classe d'opérateur n'apporterait à mon avis pas grand chose ?
La base en production est en 8.4.4 , le passage en 9.x est prévue au 2 trimestre 2015
la question que je me posais concerné le "rows=0"
Seq Scan on band bands0_ (cost=0.00..115113.70 rows=5 width=333) (actual time=727.182..727.182 rows=0 loops=1)
pourquoi l'explain analyse ne reference t-il pas le nb d'enregistrement trouvés ?
Rebonjour
correctif la valeur du statistique_target est de 500 et non de 5400
Bonjour j'ai un pb avec une simple requete SQL sur une table de 5 Millions d'enregistrements qui fait un sequentiel scan plutot que de passer par l'index btree non unique "ib_t_band_fk_metadata"
Il y a 5 enregistrements par ib_t_band_fk_metadata
Contexte Postgres 8.4.4 , linux suse11 SP3, statistique_target = 5400
explain analyze select
bands0_.FK_METADATA as FK10_1_, bands0_.id as id1_, bands0_.BAND_ID as BAND2_46_0_,
bands0_.BIT_ORDER as BIT3_46_0_, bands0_.BIT_PIXEL as BIT4_46_0_, bands0_.MAX_VALUE as MAX5_46_0_,
bands0_.MEAN_VALUE as MEAN6_46_0_, bands0_.FK_METADATA as FK10_46_0_, bands0_.MIN_VALUE as MIN7_46_0_,
bands0_.POLARIZATION as POLARIZA8_46_0_, bands0_.STD_DEVIATION as STD9_46_0_
from BAND bands0_ where bands0_.fk_metadata='4b364394-6f7f-4249-acb4-2b16ce99f70d';
----------------------------------------------------------------------------------------------------------------
Seq Scan on band bands0_ (cost=0.00..115113.70 rows=5 width=333) (actual time=727.182..727.182 rows=0 loops=1)
Filter: ((fk_metadata)::text = '4b364394-6f7f-4249-acb4-2b16ce99f70d'::text)
Total runtime: 727.215 ms
\d band
Table "public.band"
Column | Type | Modifiers
---------------+------------------------+-----------
id | character varying(36) | not null
band_id | character varying(200) |
bit_order | character varying(50) |
bit_pixel | integer |
max_value | real |
mean_value | real |
min_value | real |
polarization | character varying(50) |
std_deviation | real |
fk_metadata | character varying(36) | not null
Indexes:
"band_pkey" PRIMARY KEY, btree (id)
"ib_t_band_c_band_id" btree (band_id)
"ib_t_band_c_max_value" btree (max_value)
"ib_t_band_c_mean_value" btree (mean_value)
"ib_t_band_c_min_value" btree (min_value)
"ib_t_band_c_std_deviation" btree (std_deviation)
"ib_t_band_fk_metadata" btree (fk_metadata)
Foreign-key constraints:
"fk1efe3585145060" FOREIGN KEY (fk_metadata) REFERENCES md_metadata(id)
Cordialement
Francis
re-bonjour
ou puis-je trouver la signification (doc ?) précise des informations affichées dans ce log postgres afin de vérifier si il y a adéquation entre le fichier pg_sql.conf, la paramétrage du serveur qui héberge postgres et les traces affichées
[xsid-postgres] CacheMemoryContext: 2240560 total in 22 blocks; 1023648 free (3 chunks); 1216912 used
[xsid-postgres] MdSmgr: 8192 total in 1 blocks; 1528 free (0 chunks); 6664 used
[xsid-postgres] LOCALLOCK hash: 57344 total in 3 blocks; 27328 free (8 chunks); 30016 used
[xsid-postgres] Timezones: 78520 total in 2 blocks; 5968 free (0 chunks); 72552 used
[xsid-postgres] ErrorContext: 16384 total in 2 blocks; 16352 free (11 chunks); 32 used
pour infos,
le paramétrage postgres est celui-ci et il y au max 30 clients en // (paramétrage de la queue du serveur Jboss/hibernate)
work_mem = 80MB
maintenance_work_mem = 128MB
shared_buffers = 2GB
wal_buffers=4MB
effective_cache_size = 3GB
et la machine à 4 gigas de Ram (machine virtuelle vmware Linux redhat 32 bits)
Bonjour, ma base en production fait un "out of memory"
[xsid-postgres] TopMemoryContext: 241512 total in 29 blocks; 50264 free (359 chunks); 191248 used
.....
[xsid-postgres] CacheMemoryContext: 2240560 total in 22 blocks; 1023648 free (3 chunks); 1216912 used
[xsid-postgres] MdSmgr: 8192 total in 1 blocks; 1528 free (0 chunks); 6664 used
[xsid-postgres] LOCALLOCK hash: 57344 total in 3 blocks; 27328 free (8 chunks); 30016 used
[xsid-postgres] Timezones: 78520 total in 2 blocks; 5968 free (0 chunks); 72552 used
[xsid-postgres] ErrorContext: 16384 total in 2 blocks; 16352 free (11 chunks); 32 used
[xsid-postgres] BASE:BDEXPL DATE:2012-06-12 14:48:57 GMT SESSION:4fd749ea.388a LIGNE:13 TRANSACTION:0 ERROR: out of memory
[xsid-postgres] BASE:BDEXPL DATE:2012-06-12 14:48:57 GMT SESSION:4fd749ea.388a LIGNE:14 TRANSACTION:0 DETAIL: Failed on request of size 348.
Avez-vous la signification des différentes lignes ci-dessus afin que je comprenne l'origine du pb
Merci d'avance
Bonjour,
Après analyse (nagios) , il apparait que l'un des serveurs (machine virtuelle) qui héberge une de mes BDs est tombé, ce qui a du causer le pb lors du 2PC de de cette transaction qui impacte 4 BD postgres. Sur l'autre cluster postgres sur un autre serveur qui héberge les 3 autres Bds RAS .
merci pour vos réponses.
Bonjour, le Pb a été fixe
la table pg_stat_activity lors du drop constraint precise que la transaction est en waiting = true
Après investigation , la table était locké par une transaction qui avait échoué lors d'un two-phase commit
Correction apportée => faire un rollback manuel des transactions qui ont échouées.
> psql -p nnnnn -h xxxxxx-U dbadmin postgres
psql (8.4.1, server 8.4.4)
postgres=# SELECT database, gid FROM pg_prepared_xacts;
database | gid
----------------+------------------------------------------------------------------------------------------
db1 | 131075_MS0tN2RlZmY1YzU6Y2NhMDo0Y2QwMWZmNToxZjA2_LTdkZWZmNWM1OmNjYTA6NGNkMDFmZjU6MWYwZQ==
db1=# \connect db1 ;
db1=# ROLLBACK PREPARED '131075_MS0tN2RlZmY1YzU6Y2NhMDo0Y2QwMWZmNToxZjA2_LTdkZWZmNWM1OmNjYTA6NGNkMDFmZjU6MWYwZQ==';
Bonjour,
Postgres 8.4.4 se bloque sous psql et sous pgadmin lors de l’exécution de la commande suivante "alter table xxxxxxxxxxx drop constraint FKE4E5D3E69E8C4A99 ;"
La contrainte existe , un index existe sur la contrainte et la table fait 200.000 enregistrements
Aucune info dans les Logs
La base est en "archive_mode à true "
Cette commande est OK sur notre bd de développement
voici la séquence passée
1) Arrêt postgres
2) Démarrage postgres
3) passage sous psql
4) alter table xxxxxxxxxxx drop constraint FKE4E5D3E69E8C4A99 ;
Blocage postgres qui ne rend pas la main .....
5) ctrl-c provoque l'abandon de commande et j'ai de nouveau la main sous psql
faut-il détruire l'index avant de détruire la contrainte ? ou ajouter "l'option CASCADE ?
merci d'avance
Bonjour,
cette requete sql retourne 0 en 10 secondes sur une base 844 en postgis 1.5 , cela est du au fait que la requête sur les dates retournent une grosse partie sur la base (750.000 rows sur 800.000), par contre le critere geo retourne 0 rows .
ma question :
pourquoi l'optimiseur n'utilise par l'index gist ? et comment forcer l'optimiseur a utilisé l'index geo en premier ... afin de répondre en moins de 1 seconde
requete sql
-------------
select metadatas1_.EXTERNAL_ID as col_0_0_
from DG_PRODUCT dgproductf0_
inner join MD_METADATA metadatas1_ on dgproductf0_.id=metadatas1_.product
left outer join CATALOG_ID catalogide2_ on metadatas1_.fk_catalogID=catalogide2_.ID
left outer join IMAGE_EXTENSION imageexten3_ on metadatas1_.id=imageexten3_.metadata
where
catalogide2_.profile='LEVEL0'
and metadatas1_.attribute_description='SCENE'
and dgproductf0_.archive_searchByAllUsers='t'
and (imageexten3_.begin_viewing_date_time between '2005-07-21 02:32:38.656' and '2008-07-17 16:09:20.128')
and imageexten3_.cloud_cover_percentage>='3.125'
and st_intersects (metadatas1_.geometry, '0106000020E610000003000000010300000001000000060000000000008002E872C000000080942A13C0000000C0007C72C0000000E018020040000000E0CDD472C000000060E8D813C000000020FB5973C0000000804A852BC00000008002E872C000000080942A13C00000008002E872C000000080942A13C00103000000010000000600000000000060ECBF4C4000000080942A13C000000020FD0F5040000000E018020040000000A090594D4000000060E8D813C00000006027304940000000804A852BC000000060ECBF4C4000000080942A13C000000060ECBF4C4000000080942A13C00103000000010000000600000000000080FD177A4000000080942A13C000000040FF837A40000000E01802004000000020322B7A4000000060E8D813C0000000E004A67940000000804A852BC000000080FD177A4000000080942A13C000000080FD177A4000000080942A13C0'
and (metadatas1_.EXTERNAL_ID like '%')
limit '600'
Explain analyse
-------------------
"Limit (cost=0.00..38984.41 rows=600 width=22) (actual time=10180.527..10180.527 rows=0 loops=1)"
" Output: metadatas1_.external_id"
" -> Nested Loop (cost=0.00..880657.90 rows=13554 width=22) (actual time=10180.526..10180.526 rows=0 loops=1)"
" Output: metadatas1_.external_id"
" Join Filter: ((metadatas1_.fk_catalogid)::text = (catalogide2_.id)::text)"
" -> Seq Scan on catalog_id catalogide2_ (cost=0.00..1.01 rows=1 width=37) (actual time=0.005..0.005 rows=1 loops=1)"
" Output: catalogide2_.id, catalogide2_.familytype, catalogide2_.producttype, catalogide2_.profile, catalogide2_.template"
" Filter: ((profile)::text = 'LEVEL0'::text)"
" -> Nested Loop (cost=0.00..880487.46 rows=13554 width=58) (actual time=10180.517..10180.517 rows=0 loops=1)"
" Output: metadatas1_.external_id, metadatas1_.fk_catalogid"
" -> Nested Loop (cost=0.00..871441.01 rows=13554 width=94) (actual time=10180.517..10180.517 rows=0 loops=1)"
" Output: metadatas1_.external_id, metadatas1_.product, metadatas1_.fk_catalogid"
" -> Seq Scan on image_extension imageexten3_ (cost=0.00..48766.17 rows=754357 width=37) (actual time=0.004..534.509 rows=754271 loops=1)"
" Output: imageexten3_.id, imageexten3_.bh_ratio, imageexten3_.accross_track_incidence_angle, imageexten3_.along_track_incidence_angle, imageexten3_.antimeridian_longitude_convention, imageexten3_.begin_viewing_date_time, imageexten3_.campagne, imageexten3_.crs, imageexten3_.geometry, imageexten3_.center_viewing_date_time, imageexten3_.cloud_cover_confidence, imageexten3_.cloud_cover_notation_mode, imageexten3_.cloud_cover_percentage, imageexten3_.colour, imageexten3_.combined_mode, imageexten3_.x_name_type_code, imageexten3_.y_name_type_code, imageexten3_.elevation_angle, imageexten3_.end_viewing_date_time, imageexten3_.gain_number, imageexten3_.helios_gains_list, imageexten3_.helios_line_shift, imageexten3_.helios_loc_quality, imageexten3_.helios_mode_cu, imageexten3_.helios_retina, imageexten3_.helios_viewing_mode, imageexten3_.illumination_azimuth_angle, imageexten3_.illumination_elevation_angle, imageexten3_.incidence_angle, imageexten3_.orbit, imageexten3_.orbit_cycle, imageexten3_.orbit_direction, imageexten3_.orientation_angle, imageexten3_.pass_id, imageexten3_.pitch, imageexten3_.processing_level_code, imageexten3_.quality_code, imageexten3_.quality_notation_confidence, imageexten3_.quality_notation_mode, imageexten3_.quality_percentage, imageexten3_.received_aos, imageexten3_.received_los, imageexten3_.resolution_x, imageexten3_.resolution_y, imageexten3_.revolution, imageexten3_.roll, imageexten3_.scene_rank, imageexten3_.segment_max_global_angle, imageexten3_.segment_min_global_angle, imageexten3_.segment_rank, imageexten3_.sensor_operational_mode, imageexten3_.snow_cover_percentage, imageexten3_.swath, imageexten3_.swath_type, imageexten3_.viewing_id, imageexten3_.yaw, imageexten3_.metadata"
" Filter: ((begin_viewing_date_time >= '2005-07-21 02:32:38.656'::timestamp without time zone) AND (begin_viewing_date_time <= '2008-07-17 16:09:20.128'::timestamp without time zone) AND (cloud_cover_percentage >= 3.125::real))"
" -> Index Scan using ib_t_md_metadata_f_id on md_metadata metadatas1_ (cost=0.00..1.08 rows=1 width=130) (actual time=0.012..0.012 rows=0 loops=754271)"
" Output: metadatas1_.id, metadatas1_.accesses, metadatas1_.complete, metadatas1_.external_id, metadatas1_.insertion_date, metadatas1_.last_update_date, 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_source_resp, metadatas1_.fk_import_resp, metadatas1_.fk_acquisition, metadatas1_.fk_catalogid, metadatas1_.fk_srid, metadatas1_.product"
" Index Cond: ((metadatas1_.id)::text = (imageexten3_.metadata)::text)"
" Filter: ((metadatas1_.geometry && '0106000020E610000003000000010300000001000000060000000000008002E872C000000080942A13C0000000C0007C72C0000000E018020040000000E0CDD472C000000060E8D813C000000020FB5973C0000000804A852BC00000008002E872C000000080942A13C00000008002E872C000000080942A13C00103000000010000000600000000000060ECBF4C4000000080942A13C000000020FD0F5040000000E018020040000000A090594D4000000060E8D813C00000006027304940000000804A852BC000000060ECBF4C4000000080942A13C000000060ECBF4C4000000080942A13C00103000000010000000600000000000080FD177A4000000080942A13C000000040FF837A40000000E01802004000000020322B7A4000000060E8D813C0000000E004A67940000000804A852BC000000080FD177A4000000080942A13C000000080FD177A4000000080942A13C0'::geometry) AND ((metadatas1_.external_id)::text ~~ '%'::text) AND ((metadatas1_.attribute_description)::text = 'SCENE'::text) AND _st_intersects(metadatas1_.geometry, '0106000020E610000003000000010300000001000000060000000000008002E872C000000080942A13C0000000C0007C72C0000000E018020040000000E0CDD472C000000060E8D813C000000020FB5973C0000000804A852BC00000008002E872C000000080942A13C00000008002E872C000000080942A13C00103000000010000000600000000000060ECBF4C4000000080942A13C000000020FD0F5040000000E018020040000000A090594D4000000060E8D813C00000006027304940000000804A852BC000000060ECBF4C4000000080942A13C000000060ECBF4C4000000080942A13C00103000000010000000600000000000080FD177A4000000080942A13C000000040FF837A40000000E01802004000000020322B7A4000000060E8D813C0000000E004A67940000000804A852BC000000080FD177A4000000080942A13C000000080FD177A4000000080942A13C0'::geometry))"
" -> Index Scan using dg_product_pkey on dg_product dgproductf0_ (cost=0.00..0.65 rows=1 width=37) (never executed)"
" Output: dgproductf0_.id, dgproductf0_.accesses, dgproductf0_.complete, dgproductf0_.external_id, dgproductf0_.insertion_date, dgproductf0_.last_update_date, 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 = (metadatas1_.product)::text)"
" Filter: dgproductf0_.archive_searchbyallusers"
"Total runtime: 10285.736 ms"
"
Merci pour toutes ces réponses, cela clos le sujet sur cet "Explain incorrect"
Cordialement
Ce plan est-il calculé via un explain , un explain analyse ? et y a t-il un moyen de récupérer le plan réellement exécute en positionnant un paramètre dans le fichier de configuration de Postgres ?
J'ai fais quelques tests en spécifiant des statistiques au niveau des "colonnes" et il n'y a pas de résultats significatifs , donc pas d'amélioration de mon explain ... par contre, le plan d'exécution construit par l'optimiseur est-il toujours le même entre un explain et un explain analyse ? , lors de l'execution de la requête , l'optimiseur change-il de plan s'il s'aperçoit que son plan d'exécution n'est pas le bon.
Exemple: si le plan issu d'un explain remonte une estimation 100.000 rows , mais quant réalité le nb de rows retournés est < 100 , l' optimiseur changera -il son plan ?
Bonjour et Bonne Année... et pour finir au sujet de cet explain, 2 questions au sujet de l'erreur d'estimation (facteur 10)
L'erreur d'estimation se trouve sur cette ligne l
" -> Nested Loop (cost=22.08..7369.40 rows=957 width=108) (actual time=0.154..14.209 rows=92 loops=1)"
1) y-a-t-il un intérêt à positionner des statistiques au niveau des "colonnes jointes pu requetables" via la commande ALTER [ COLUMN ] column SET STATISTICS integer ? , car généralement ces colonnes sont indexées), ceci afin d'aider l'optimiseur ?
2) Quant une colonne est indexée, l'optimiseur utilise-t-il les informations relatives aux indexes ou s'appuie-il uniquement sur les statistiques collectés sur les colonnes, les tables ?
Merci, c'est nettement plus clair maintenant
Bonnes Fêtes
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)
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"
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 ?)
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"
Pas tout à fait , mon schema de BB contient 30 tables.
Mon traitement est construit ainsi
1) construction du select de "count " par rapport aux critères contenues dans la clause "where", a ce titre il va au max s'appuyer sur 15 tables( qui contiennent les champs requetables). ce count remonte le nb de résultats de requête, affichage du nb de rows sur le formulaire. soit l'utilisateur affine sa requete soit il demande le rapatriement des données
si le rapatriement est demandé
2) construction d'un select "id" "qui remonte uniquement l'id des données" identique au count, donc il va s'appuyer au max sur 15 tables (qui contiennent les champs requetables)
3) construction d'un select "avec tous les champs demandées par l'utilisateur" et dans ce cas le traitement peut parcourir l'ensemble des tables du modèle afin de récupérer les champs requis, la clause where dans ce cas comporte uniquement un "where ID in (les ids remontés lors de la requete decrié en 2)"
Donc le cout des requêtes 1 et 2 sont identiques , la requête 2 sera plus rapide car en cache
LA requête 3 dépendra des tables qu'il faudra traverser (via des inner join) pour récupérer les infos demandées par l'utilisateur.
Oui c'est bien un formulaire de recherche dans cette Bd qui permet d'accéder aux données.
Si je comprends bien, je ne peux pas utiliser l'explain et je n'arriverais pas par configuration postgres à établir un ratio me permettant de fiabiliser le résultat retourné par l'explain et dans mon cas indiquer à l'utilisateur que sa requête remonte trop de données.
Ce qui m'intéresse serait d'être proche de google et donc d'avoir un facteur 2 ou 3 mais pas 10 ou 100 !!!
Est-il préférable de faire un "count" (comme dans la première version du soft) ou d'utiliser le camp rows de l"explain analyse" qui exécute réellement la requête mais ne remonte pas d'infos
L'autre solution étant naturellement de jouer avec des select "LIMIT 500 OFFSET 500"
Merci ,pour la réponse rapide, je n'ai pas de pb de performances pour le moment , mais cette base de données va rapidement faire 10 Millions de Rows, Nous avons une première version du logiciel qui exploite cette bd qui effectue d'abord un 'select count " puis un select de rapatriement des données si le valeur du count est < à N données (N positionnée par l'utilisateur)
Nous voulons supprimer le count par un explain en exploitant le champ ("rows") afin d'éviter de faire de 2 requêtes. Mais on s'aperçoit que la valeur du champ "rows" de l'explain est souvent erronée (facteur 10 , 100) ce qui rend inexploitable l'utilisation de l'explain dans notre cas.
Je recherche donc un moyen de sécuriser/fiabiliser l'exploitation de ce champ ("rows") , est-ce possible , en augmentant le nb d'échantillon des stats ? en prenant un ratio , ...
Bonjour,
je souhaite exploiter, via un programme java, le résultat d'un explain pour des raisons de performances (sans la clause analyse) , plutôt que celui d'un "count" lors d'un select et le pb est que l'explain ne donne pas les résultats attendus (92 résultats via un count et 866 avec l'explain), en général je constate une facteur 10
Y-t-il un moyen améliorer ..le résultat de l'explain
la BD 844 postgres comporte 1Million de rows
Mes statistiques sont à 500 sur toutes les tables (default_statistics_target = 500)
Merci
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
et le résultat de l'explain
------------------------------------------
"HashAggregate (cost=13859.60..13868.26 rows=866 width=37) (actual time=51.707..51.766 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..13857.44 rows=866 width=37) (actual time=0.441..51.586 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..13833.87 rows=58 width=109) (actual time=0.426..50.352 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..13752.29 rows=58 width=145) (actual time=0.414..49.090 rows=92 loops=1)"
" 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.009..0.010 rows=1 loops=1)"
" Index Cond: ((profile)::text = 'LEVEL0'::text)"
" -> Nested Loop (cost=22.08..13745.29 rows=58 width=181) (actual time=0.401..48.891 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..13379.61 rows=892 width=218) (actual time=0.219..35.159 rows=1046 loops=1)"
" -> Nested Loop (cost=22.08..13016.89 rows=892 width=217) (actual time=0.205..20.162 rows=1046 loops=1)"
" -> Nested Loop (cost=22.08..8715.53 rows=957 width=145) (actual time=0.179..15.676 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..7369.40 rows=957 width=108) (actual time=0.154..14.209 rows=92 loops=1)"
" -> Bitmap Heap Scan on image_extension imagefeatu4_ (cost=22.08..2089.86 rows=957 width=37) (actual time=0.131..0.944 rows=1046 loops=1)"
" 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.116..0.116 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.011 rows=0 loops=1046)"
" 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) (actual time=0.011..0.012 rows=1 loops=92)"
" 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) (actual time=0.018..0.027 rows=11 loops=92)"
" 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.011..0.011 rows=1 loops=1046)"
" 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.012..0.012 rows=0 loops=1046)"
" 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) (actual time=0.010..0.010 rows=1 loops=92)"
" 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) (actual time=0.010..0.011 rows=1 loops=92)"
" Index Cond: ((dgproductf0_.id)::text = (dgproductf0_1_.id)::text)"
" Filter: dgproductf0_.archive_searchbyallusers"
"Total runtime: 51.962 ms"
-------------------------------------------
merci et Bon week-end
A bientot
Naturellement
je vous fait un retour en juillet sur le cluster et from_collapse - join_collapse
pour cela , je continue à répondre à ce message ou j'en ouvre un autre ???
Oui,
je me demande si le cluster a une utilité !!! , j'ai un gros test de chargement au moins de juillet sur cette base , passage de 200.000 enregs à 500.000 enregs donc je vous informerais de l'utilité ou pas du cluster !!!!
Merci pour toutes vos informations qui ont été très précieuses.
A bientôt
Francis
