Forums PostgreSQL.fr

F.Chanson

Membre

Index Gist non utlisé

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"

"

cedric

Membre

Re : Index Gist non utlisé

Je suppose qu'utiliser une requete de ce genre peut aider en ce sens, mais il peut exister des raisons pertinentes pour lesquelles le planner de postgresql choisi ce plan:

WITH m1 as (
select EXTERNAL_ID, product, fk_catalogID, id
from MD_METADATA
where attribute_description='SCENE'
and st_intersects (geometry, '0106000020E610000003000000010300000001000000060000000000008002E872C000000080942A13C0000000C0007C72C0000000E018020040000000E0CDD472C000000060E8D813C000000020FB5973C0000000804A852BC00000008002E872C000000080942A13C00000008002E872C000000080942A13C00103000000010000000600000000000060ECBF4C4000000080942A13C000000020FD0F5040000000E018020040000000A090594D4000000060E8D813C00000006027304940000000804A852BC000000060ECBF4C4000000080942A13C000000060ECBF4C4000000080942A13C00103000000010000000600000000000080FD177A4000000080942A13C000000040FF837A40000000E01802004000000020322B7A4000000060E8D813C0000000E004A67940000000804A852BC000000080FD177A4000000080942A13C000000080FD177A4000000080942A13C0'
and (EXTERNAL_ID like '%')
)
select metadatas1_.EXTERNAL_ID as col_0_0_
from m1 metadatas1_
inner join DG_PRODUCT dgproductf0_ 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 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'
limit 600

---

Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation