Geek Answers Handbook

Why do I have one PostgreSQL server that makes a Hash Join and another that integrates into Semi Join?

I have two different machines that run psql 8.4.7 with the same data (using pg_restore), but I have different requests from them. There are other functional differences between the two machines (one is CentOS and the other Ubuntu, and they use different gcc), but I would think that they will use more or less the same logic.

One machine uses Hash Join and it works very fast (50 ms). Another uses a nested loop and takes an extra long time (10 seconds). Any hints on how I can figure this out?

On one:

database_production=> EXPLAIN ANALYZE SELECT tags.*, COUNT(*) AS count FROM "tags" LEFT OUTER JOIN taggings ON tags.id = taggings.tag_id AND taggings.context = 'categories' INNER JOIN vendors ON vendors.id = taggings.taggable_id WHERE (taggings.taggable_type = 'Vendor') AND (taggings.taggable_id IN(SELECT vendors.id FROM "vendors" INNER JOIN "deals" ON "deals"."vendor_id" = "vendors"."id" INNER JOIN "programs" ON "programs"."id" = "deals"."program_id" INNER JOIN "memberships" ON "memberships"."program_id" = "programs"."id" WHERE (memberships.user_id = 1) AND (vendors.id NOT IN (SELECT vendor_id FROM vendor_ignores WHERE user_id = 1)))) GROUP BY tags.id, tags.name HAVING COUNT(*) > 0 ORDER BY count DESC; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=164.89..164.89 rows=1 width=520) (actual time=9444.003..9444.009 rows=6 loops=1) Sort Key: (count(*)) Sort Method: quicksort Memory: 17kB -> HashAggregate (cost=164.86..164.88 rows=1 width=520) (actual time=9443.936..9443.942 rows=6 loops=1) Filter: (count(*) > 0) -> Nested Loop Semi Join (cost=14.92..164.85 rows=1 width=520) (actual time=67.355..9443.645 rows=94 loops=1) Join Filter: (public.vendors.id = deals.vendor_id) -> Nested Loop (cost=9.35..29.93 rows=1 width=528) (actual time=3.570..154.104 rows=7636 loops=1) -> Nested Loop (cost=9.35..21.65 rows=1 width=524) (actual time=3.534..83.165 rows=7636 loops=1) -> Bitmap Heap Scan on taggings (cost=9.35..13.37 rows=1 width=8) (actual time=3.476..12.277 rows=7636 loops=1) Recheck Cond: (((taggable_type)::text = 'Vendor'::text) AND ((context)::text = 'categories'::text)) -> BitmapAnd (cost=9.35..9.35 rows=1 width=0) (actual time=3.410..3.410 rows=0 loops=1) -> Bitmap Index Scan on index_taggings_on_taggable_type (cost=0.00..4.55 rows=40 width=0) (actual time=1.664..1.664 rows=7636 loops=1) Index Cond: ((taggable_type)::text = 'Vendor'::text) -> Bitmap Index Scan on index_taggings_on_context (cost=0.00..4.55 rows=40 width=0) (actual time=1.727..1.727 rows=7648 loops=1) Index Cond: ((context)::text = 'categories'::text) -> Index Scan using tags_pkey on tags (cost=0.00..8.27 rows=1 width=520) (actual time=0.004..0.005 rows=1 loops=7636) Index Cond: (tags.id = taggings.tag_id) -> Index Scan using vendors_pkey on vendors (cost=0.00..8.27 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=7636) Index Cond: (public.vendors.id = taggings.taggable_id) -> Nested Loop (cost=5.57..134.62 rows=24 width=8) (actual time=0.035..1.117 rows=93 loops=7636) -> Nested Loop (cost=4.54..100.19 rows=24 width=4) (actual time=0.028..0.344 rows=93 loops=7636) -> Nested Loop (cost=0.00..9.57 rows=1 width=8) (actual time=0.010..0.035 rows=3 loops=7636) -> Seq Scan on memberships (cost=0.00..1.29 rows=1 width=4) (actual time=0.004..0.009 rows=3 loops=7636) Filter: (user_id = 1) -> Index Scan using programs_pkey on programs (cost=0.00..8.27 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=22810) Index Cond: (programs.id = memberships.program_id) -> Bitmap Heap Scan on deals (cost=4.54..90.16 rows=37 width=8) (actual time=0.012..0.042 rows=31 loops=22810) Recheck Cond: (deals.program_id = programs.id) -> Bitmap Index Scan on index_deals_on_program_id (cost=0.00..4.53 rows=37 width=0) (actual time=0.008..0.008 rows=31 loops=22810) Index Cond: (deals.program_id = programs.id) -> Index Scan using vendors_pkey on vendors (cost=1.03..1.42 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=713413) Index Cond: (public.vendors.id = deals.vendor_id) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> Seq Scan on vendor_ignores (cost=0.00..1.02 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=1) Filter: (user_id = 1) Total runtime: 9444.501 ms (38 rows)

On the other hand, the result is actually displayed on a separate screen:

  QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=977.70..978.94 rows=496 width=23) (actual time=49.859..49.863 rows=6 loops=1) Sort Key: (count(*)) Sort Method: quicksort Memory: 17kB -> HashAggregate (cost=946.81..955.49 rows=496 width=23) (actual time=49.793..49.801 rows=6 loops=1) Filter: (count(*) > 0) -> Hash Join (cost=440.07..875.99 rows=7082 width=23) (actual time=28.661..49.599 rows=94 loops=1) Hash Cond: (taggings.tag_id = tags.id) -> Hash Join (cost=424.91..763.45 rows=7082 width=4) (actual time=27.388..48.105 rows=94 loops=1) Hash Cond: (taggings.taggable_id = public.vendors.id) -> Seq Scan on taggings (cost=0.00..184.72 rows=7378 width=8) (actual time=0.030..12.791 rows=7636 loops=1) Filter: (((context)::text = 'categories'::text) AND ((taggable_type)::text = 'Vendor'::text)) -> Hash (cost=331.68..331.68 rows=7458 width=12) (actual time=27.226..27.226 rows=94 loops=1) -> Nested Loop (cost=134.67..331.68 rows=7458 width=12) (actual time=26.056..27.084 rows=94 loops=1) -> HashAggregate (cost=134.67..134.98 rows=31 width=8) (actual time=26.047..26.153 rows=94 loops=1) -> Nested Loop (cost=1.03..134.59 rows=31 width=8) (actual time=24.422..25.890 rows=94 loops=1) -> Nested Loop (cost=0.00..49.95 rows=59 width=4) (actual time=14.902..15.359 rows=94 loops=1) -> Nested Loop (cost=0.00..9.57 rows=1 width=8) (actual time=0.108..0.143 rows=3 loops=1) -> Seq Scan on memberships (cost=0.00..1.29 rows=1 width=4) (actual time=0.050..0.057 rows=3 loops=1) Filter: (user_id = 1) -> Index Scan using programs_pkey on programs (cost=0.00..8.27 rows=1 width=4) (actual time=0.020..0.022 rows=1 loops=3) Index Cond: (programs.id = memberships.program_id) -> Index Scan using index_deals_on_program_id on deals (cost=0.00..39.64 rows=59 width=8) (actual time=4.943..5.005 rows=31 loops=3) Index Cond: (deals.program_id = programs.id) -> Index Scan using vendors_pkey on vendors (cost=1.03..1.42 rows=1 width=4) (actual time=0.106..0.108 rows=1 loops=94) Index Cond: (public.vendors.id = deals.vendor_id) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> Seq Scan on vendor_ignores (cost=0.00..1.02 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=1) Filter: (user_id = 1) -> Index Scan using vendors_pkey on vendors (cost=0.00..6.33 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=94) Index Cond: (public.vendors.id = deals.vendor_id) -> Hash (cost=8.96..8.96 rows=496 width=23) (actual time=1.257..1.257 rows=496 loops=1) -> Seq Scan on tags (cost=0.00..8.96 rows=496 width=23) (actual time=0.051..0.619 rows=496 loops=1) Total runtime: 50.357 ms (34 rows)
+7
source share
2 answers

The costs noted in the query plan vary greatly, which Postgres calculates based on index statistics. This information is taken into account by the query planner, on the basis of which the most predicted action is.

If you have different data in each database, the index statistics will be different, which will lead to a different tariff plan. If the data is the same, you can simply have outdated index statistics for one or both databases. VACUUM ANALYZE related tables and try again.

Edit: Obviously, VACUUM ANALYZE did the trick in your case. Next steps:

  • Make sure autovacuum is working
    • You are using 8.4, so the default configuration is probably fine. However, with older versions of postgres, enabling and configuring autovaccum was more difficult.
  • After large write operations (deleting / inserting a large number of lines or importing from pg_dump), run VACUUM ANALYZE to update your indexes.
+10
source

According to the Postgres wiki :

A hashed subplane is fast, but the scheduler only allows this plan for small result sets; a simple subplane is terribly slow (actually O (NĀ²)). This means that performance may look good in small-scale tests, but can then be reduced by 5 or more orders of magnitude after crossing the size threshold; You do not want this to happen.

Thus, probably the slowest database result is simply more than the threshold that Postgres considers "too large for hashing."

The solution is probably to increase the setting of worker_mem in Postgres, which defaults to 4MB until Postgres can conveniently hash the subquery.

0
source

More articles:


All Articles