Revisions to Optimizing a simple query joining two big tables

added [postgresql-performance] to 571 questions - Shog9 (Id=1924)

Link

edited Jan 7, 2020 at 22:25

Community Bot

1

added [query-performance] to 2412 questions - Shog9 (Id=1924)

Link

edited Jan 7, 2020 at 22:23

Community Bot

1

postgresql performance index-tuning postgresql-9.6 query-performance

add info from comment, minor edits

Source Link

edited Jun 7, 2018 at 12:35

Erwin Brandstetter

186.5k
28
465
639

(I deleted a couple of columns that are not involved in the query in order to improve readibilityreadability).

The result of EXPLAIN ANALYZE isEXPLAIN ANALYZE:

"HashHash Join (cost=17709.29..152088.29 rows=147368 width=887) (actual time=137.074..879.884 rows=210708 loops=1)" " Hash Cond: (z.pkid_site = s.pkid)" " -> Seq Scan on zones_sites z (cost=0.00..85198.14 rows=210717 width=855) (actual time=0.140..384.964 rows=210708 loops=1)" " Filter: ((statut)::text = 'selected'::text)" " Rows Removed by Filter: 23"23 " -> Hash (cost=13433.16..13433.16 rows=210490 width=36) (actual time=136.772..136.772 rows=210708 loops=1)" " Buckets: 65536 Batches: 8 Memory Usage: 2191kB"2191kB " -> Seq Scan on sites_candidats s (cost=0.00..13433.16 rows=210490 width=36) (actual time=3.085..87.774 rows=210708 loops=1)" " Filter: ((statut)::text = 'selected'::text)" " Rows Removed by Filter: 90265"90265 "PlanningPlanning time: 0.386 ms"ms "ExecutionExecution time: 888.436 ms"ms

Table traitements.sites_candidats has about 300k rows (210k of them with statut='selected') and traitements.zones_sites about 210k rows (nearly 99% of have statut='selected'). The query takes more than 4 minutes.

(I deleted a couple of columns that are not involved in the query in order to improve readibility).

The result of EXPLAIN ANALYZE is

"Hash Join (cost=17709.29..152088.29 rows=147368 width=887) (actual time=137.074..879.884 rows=210708 loops=1)" " Hash Cond: (z.pkid_site = s.pkid)" " -> Seq Scan on zones_sites z (cost=0.00..85198.14 rows=210717 width=855) (actual time=0.140..384.964 rows=210708 loops=1)" " Filter: ((statut)::text = 'selected'::text)" " Rows Removed by Filter: 23" " -> Hash (cost=13433.16..13433.16 rows=210490 width=36) (actual time=136.772..136.772 rows=210708 loops=1)" " Buckets: 65536 Batches: 8 Memory Usage: 2191kB" " -> Seq Scan on sites_candidats s (cost=0.00..13433.16 rows=210490 width=36) (actual time=3.085..87.774 rows=210708 loops=1)" " Filter: ((statut)::text = 'selected'::text)" " Rows Removed by Filter: 90265" "Planning time: 0.386 ms" "Execution time: 888.436 ms"

Table traitements.sites_candidats has about 300k rows (210k of them with statut='selected') and traitements.zones_sites about 210k rows. The query takes more than 4 minutes.

(I deleted a couple of columns that are not involved in the query in order to improve readability).

The result of EXPLAIN ANALYZE:

Hash Join (cost=17709.29..152088.29 rows=147368 width=887) (actual time=137.074..879.884 rows=210708 loops=1) Hash Cond: (z.pkid_site = s.pkid) -> Seq Scan on zones_sites z (cost=0.00..85198.14 rows=210717 width=855) (actual time=0.140..384.964 rows=210708 loops=1) Filter: ((statut)::text = 'selected'::text) Rows Removed by Filter: 23 -> Hash (cost=13433.16..13433.16 rows=210490 width=36) (actual time=136.772..136.772 rows=210708 loops=1) Buckets: 65536 Batches: 8 Memory Usage: 2191kB -> Seq Scan on sites_candidats s (cost=0.00..13433.16 rows=210490 width=36) (actual time=3.085..87.774 rows=210708 loops=1) Filter: ((statut)::text = 'selected'::text) Rows Removed by Filter: 90265 Planning time: 0.386 ms Execution time: 888.436 ms

Table traitements.sites_candidats has about 300k rows (210k of them with statut='selected') and traitements.zones_sites about 210k rows (nearly 99% of have statut='selected'). The query takes more than 4 minutes.