explain.depesz.com

PostgreSQL's explain analyze made readable

Result: di8q : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Q1; plan #sxXT; plan #BKRV; plan #jpuF; plan #VPjc; plan #eD51; plan #qMdY; plan #TFnuN

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.317 519.548 ↓ 91.0 91 1

Unique (cost=235.81..235.83 rows=1 width=14) (actual time=517.775..519.548 rows=91 loops=1)

  • Output: taxref.cd_ref, taxref.group2_inpn
2. 0.862 519.231 ↓ 91.0 91 1

Group (cost=235.81..235.82 rows=1 width=14) (actual time=517.770..519.231 rows=91 loops=1)

  • Output: taxref.cd_ref, taxref.group2_inpn
  • Group Key: taxref.cd_ref, taxref.group2_inpn
3. 1.679 518.369 ↓ 382.0 382 1

Sort (cost=235.81..235.82 rows=1 width=14) (actual time=517.764..518.369 rows=382 loops=1)

  • Output: taxref.cd_ref, taxref.group2_inpn
  • Sort Key: taxref.cd_ref, taxref.group2_inpn
  • Sort Method: quicksort Memory: 42kB
4. 19.452 516.690 ↓ 382.0 382 1

Nested Loop (cost=5.97..235.80 rows=1 width=14) (actual time=12.753..516.690 rows=382 loops=1)

  • Output: taxref.cd_ref, taxref.group2_inpn
  • Inner Unique: true
5. 38.065 455.182 ↓ 3,004.0 3,004 1

Nested Loop (cost=5.54..235.27 rows=1 width=22) (actual time=3.289..455.182 rows=3,004 loops=1)

  • Output: synthese.id_synthese, taxref.cd_ref, taxref.group2_inpn, l_areas.id_area
  • Inner Unique: true
6. 10.012 387.077 ↓ 3,004.0 3,004 1

Nested Loop (cost=5.11..234.63 rows=1 width=12) (actual time=3.250..387.077 rows=3,004 loops=1)

  • Output: synthese.cd_nom, synthese.id_synthese, l_areas.id_area
7. 0.028 0.028 ↑ 1.0 1 1

Index Scan using pk_l_areas on ref_geo.l_areas (cost=0.42..8.44 rows=1 width=294) (actual time=0.023..0.028 rows=1 loops=1)

  • Output: l_areas.id_area, l_areas.id_type, l_areas.area_name, l_areas.area_code, l_areas.geom, l_areas.centroid, l_areas.source, l_areas.comment, l_areas.enable, l_areas.meta_create_date, l_areas.meta_update_date
  • Index Cond: (l_areas.id_area = 1234)
8. 374.737 377.037 ↓ 3,004.0 3,004 1

Bitmap Heap Scan on gn_synthese.synthese (cost=4.69..226.17 rows=1 width=40) (actual time=3.215..377.037 rows=3,004 loops=1)

  • Output: synthese.id_synthese, synthese.unique_id_sinp, synthese.unique_id_sinp_grp, synthese.id_source, synthese.id_module, synthese.entity_source_pk_value, synthese.id_dataset, synthese.id_nomenclature_geo_object_nature, synthese.id_nomenclature_grp_typ, synthese.id_nomenclature_obs_meth, synthese.id_nomenclature_obs_technique, synthese.id_nomenclature_bio_status, synthese.id_nomenclature_bio_condition, synthese.id_nomenclature_naturalness, synthese.id_nomenclature_exist_proof, synthese.id_nomenclature_valid_status, synthese.id_nomenclature_diffusion_level, synthese.id_nomenclature_life_stage, synthese.id_nomenclature_sex, synthese.id_nomenclature_obj_count, synthese.id_nomenclature_type_count, synthese.id_nomenclature_sensitivity, synthese.id_nomenclature_observation_status, synthese.id_nomenclature_blurring, synthese.id_nomenclature_source_status, synthese.id_nomenclature_info_geo_type, synthese.count_min, synthese.count_max, synthese.cd_nom, synthese.nom_cite, synthese.meta_v_taxref, synthese.sample_number_proof, synthese.digital_proof, synthese.non_digital_proof, synthese.altitude_min, synthese.altitude_max, synthese.the_geom_4326, synthese.the_geom_point, synthese.the_geom_local, synthese.date_min, synthese.date_max, synthese.validator, synthese.validation_comment, synthese.observers, synthese.determiner, synthese.id_digitiser, synthese.id_nomenclature_determination_method, synthese.comment_context, synthese.comment_description, synthese.meta_validation_date, synthese.meta_create_date, synthese.meta_update_date, synthese.last_action
  • Recheck Cond: (synthese.the_geom_local && st_expand(l_areas.geom, '2000'::double precision))
  • Filter: ((l_areas.geom && st_expand(synthese.the_geom_local, '2000'::double precision)) AND _st_dwithin(synthese.the_geom_local, l_areas.geom, '2000'::double precision))
  • Rows Removed by Filter: 1074
  • Heap Blocks: exact=3472
9. 2.300 2.300 ↓ 76.9 4,078 1

Bitmap Index Scan on i_synthese_the_geom_local (cost=0.00..4.69 rows=53 width=0) (actual time=2.298..2.300 rows=4,078 loops=1)

  • Index Cond: (synthese.the_geom_local && st_expand(l_areas.geom, '2000'::double precision))
10. 30.040 30.040 ↑ 1.0 1 3,004

Index Scan using pk_taxref on taxonomie.taxref (cost=0.42..0.64 rows=1 width=18) (actual time=0.010..0.010 rows=1 loops=3,004)

  • Output: taxref.cd_nom, taxref.id_statut, taxref.id_habitat, taxref.id_rang, taxref.regne, taxref.phylum, taxref.classe, taxref.ordre, taxref.famille, taxref.sous_famille, taxref.tribu, taxref.cd_taxsup, taxref.cd_sup, taxref.cd_ref, taxref.lb_nom, taxref.lb_auteur, taxref.nom_complet, taxref.nom_complet_html, taxref.nom_valide, taxref.nom_vern, taxref.nom_vern_eng, taxref.group1_inpn, taxref.group2_inpn, taxref.url
  • Index Cond: (taxref.cd_nom = synthese.cd_nom)
11. 42.056 42.056 ↓ 0.0 0 3,004

Index Only Scan using pk_cor_area_synthese on gn_synthese.cor_area_synthese (cost=0.43..0.54 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=3,004)

  • Output: cor_area_synthese.id_synthese, cor_area_synthese.id_area
  • Index Cond: ((cor_area_synthese.id_synthese = synthese.id_synthese) AND (cor_area_synthese.id_area = 1234))
  • Heap Fetches: 0
Planning time : 1.181 ms
Execution time : 519.935 ms