0

We are doing a full text search on an object. Unfortunately, the object is normalized across different tables, and the full text search results in a condition with several OR, because the term can exist in any of those tables. For that query, postgres doesn't use the existing indexes. If I change the condition to AND - just for testing of execution plans, it uses the index, and the expected cost is ~1% of the original query.

Here is the original OR query:

SELECT *
FROM "Reservation"."Reservation" AS x
         INNER JOIN "Reservation"."Booking" AS "x.Booking" ON x."BookingId" = "x.Booking"."Id"
         INNER JOIN "Reservation"."Person" AS "x.Booking.Booker" ON "x.Booking"."BookerId" = "x.Booking.Booker"."Id"
         INNER JOIN "Reservation"."Person" AS "x.PrimaryGuest" ON x."PrimaryGuestId" = "x.PrimaryGuest"."Id"
WHERE ("x.PrimaryGuest"."SearchVector" @@ to_tsquery('tim' || ':*')) OR ("x.Booking.Booker"."SearchVector" @@ to_tsquery('tim' || ':*')) OR (x."SearchVector" @@ to_tsquery('tim' || ':*'))

and the execution plan is:

Gather  (cost=69009.66..118182.16 rows=1436 width=1546) (actual time=842.841..1222.045 rows=730 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=52506, temp read=44132 written=44856
  ->  Parallel Hash Join  (cost=68009.66..117038.56 rows=598 width=1546) (actual time=828.978..1124.544 rows=243 loops=3)
        Hash Cond: (x."PrimaryGuestId" = "x.PrimaryGuest"."Id")
        Join Filter: (("x.PrimaryGuest"."SearchVector" @@ to_tsquery('tim:*'::text)) OR ("x.Booking.Booker"."SearchVector" @@ to_tsquery('tim:*'::text)) OR (x."SearchVector" @@ to_tsquery('tim:*'::text)))
        Rows Removed by Join Filter: 43033
        Buffers: shared hit=52506, temp read=44132 written=44856
        ->  Parallel Hash Join  (cost=44689.29..70217.16 rows=61723 width=1217) (actual time=392.995..466.565 rows=43277 loops=3)
              Hash Cond: (x."BookingId" = "x.Booking"."Id")
              Buffers: shared hit=35650, temp read=23043 written=23480
              ->  Parallel Seq Scan on "Reservation" x  (cost=0.00..12575.23 rows=61723 width=513) (actual time=0.011..32.160 rows=43277 loops=3)
                    Buffers: shared hit=11958
              ->  Parallel Hash  (cost=39823.34..39823.34 rows=47996 width=704) (actual time=291.006..291.006 rows=38387 loops=3)
                    Buckets: 8192  Batches: 32  Memory Usage: 1792kB
                    Buffers: shared hit=23692, temp read=10620 written=17048
                    ->  Parallel Hash Join  (cost=10645.91..39823.34 rows=47996 width=704) (actual time=154.054..220.003 rows=38387 loops=3)
                          Hash Cond: ("x.Booking.Booker"."Id" = "x.Booking"."BookerId")
                          Buffers: shared hit=23692, temp read=10620 written=10780
                          ->  Parallel Seq Scan on "Person" "x.Booking.Booker"  (cost=0.00..17500.83 rows=103083 width=329) (actual time=0.006..39.031 rows=82736 loops=3)
                                Buffers: shared hit=16470
                          ->  Parallel Hash  (cost=7701.96..7701.96 rows=47996 width=375) (actual time=39.575..39.575 rows=38387 loops=3)
                                Buckets: 16384  Batches: 16  Memory Usage: 1664kB
                                Buffers: shared hit=7222, temp written=2696
                                ->  Parallel Seq Scan on "Booking" "x.Booking"  (cost=0.00..7701.96 rows=47996 width=375) (actual time=0.008..14.347 rows=38387 loops=3)
                                      Buffers: shared hit=7222
        ->  Parallel Hash  (cost=17500.83..17500.83 rows=103083 width=329) (actual time=244.585..244.585 rows=82736 loops=3)
              Buckets: 16384  Batches: 32  Memory Usage: 2304kB
              Buffers: shared hit=16470, temp written=7916
              ->  Parallel Seq Scan on "Person" "x.PrimaryGuest"  (cost=0.00..17500.83 rows=103083 width=329) (actual time=0.035..82.779 rows=82736 loops=3)
                    Buffers: shared hit=16470
Planning Time: 1.483 ms
Execution Time: 1222.244 ms

The adjusted AND query (just to show that we really have the necessary index):

SELECT *
FROM "Reservation"."Reservation" AS x
         INNER JOIN "Reservation"."Booking" AS "x.Booking" ON x."BookingId" = "x.Booking"."Id"
         INNER JOIN "Reservation"."Person" AS "x.Booking.Booker" ON "x.Booking"."BookerId" = "x.Booking.Booker"."Id"
         INNER JOIN "Reservation"."Person" AS "x.PrimaryGuest" ON x."PrimaryGuestId" = "x.PrimaryGuest"."Id"
WHERE ("x.PrimaryGuest"."SearchVector" @@ to_tsquery('tim' || ':*')) AND ("x.Booking.Booker"."SearchVector" @@ to_tsquery('tim' || ':*')) AND (x."SearchVector" @@ to_tsquery('tim' || ':*'))

with a much faster execution plan:

Nested Loop  (cost=109.50..310.70 rows=1 width=1546) (actual time=0.306..0.306 rows=0 loops=1)
  Buffers: shared hit=69
  ->  Nested Loop  (cost=109.08..309.38 rows=1 width=1217) (actual time=0.306..0.306 rows=0 loops=1)
        Buffers: shared hit=69
        ->  Nested Loop  (cost=108.78..301.07 rows=1 width=842) (actual time=0.306..0.306 rows=0 loops=1)
              Buffers: shared hit=69
              ->  Bitmap Heap Scan on "Reservation" x  (cost=108.36..170.71 rows=15 width=513) (actual time=0.244..0.258 rows=9 loops=1)
                    Recheck Cond: ("SearchVector" @@ to_tsquery('tim:*'::text))
                    Heap Blocks: exact=8
                    Buffers: shared hit=33
                    ->  Bitmap Index Scan on "IX_Reservation_SearchVector"  (cost=0.00..108.36 rows=15 width=0) (actual time=0.239..0.239 rows=12 loops=1)
                          Index Cond: ("SearchVector" @@ to_tsquery('tim:*'::text))
                          Buffers: shared hit=25
              ->  Index Scan using "PK_Person" on "Person" "x.PrimaryGuest"  (cost=0.42..8.69 rows=1 width=329) (actual time=0.005..0.005 rows=0 loops=9)
                    Index Cond: ("Id" = x."PrimaryGuestId")
                    Filter: ("SearchVector" @@ to_tsquery('tim:*'::text))
                    Rows Removed by Filter: 1
                    Buffers: shared hit=36
        ->  Index Scan using "PK_Booking" on "Booking" "x.Booking"  (cost=0.29..8.31 rows=1 width=375) (never executed)
              Index Cond: ("Id" = x."BookingId")
  ->  Index Scan using "PK_Person" on "Person" "x.Booking.Booker"  (cost=0.42..1.32 rows=1 width=329) (never executed)
        Index Cond: ("Id" = "x.Booking"."BookerId")
        Filter: ("SearchVector" @@ to_tsquery('tim:*'::text))
Planning Time: 0.721 ms
Execution Time: 0.372 ms

now, the query with AND won't give me the correct result, but it made me thinking, actually the OR query should be equivalent to a UNION with those 3 conditions, so I constructed it.

Just for testing, what would the execution plan of UNION be:

This query, should be pretty much the equivalent of the above mentioned OR query (if it's not, please point it out):

SELECT *
FROM "Reservation"."Reservation" AS x
         INNER JOIN "Reservation"."Booking" AS "x.Booking" ON x."BookingId" = "x.Booking"."Id"
         INNER JOIN "Reservation"."Person" AS "x.Booking.Booker" ON "x.Booking"."BookerId" = "x.Booking.Booker"."Id"
         INNER JOIN "Reservation"."Person" AS "x.PrimaryGuest" ON x."PrimaryGuestId" = "x.PrimaryGuest"."Id"
WHERE ("x.PrimaryGuest"."SearchVector" @@ to_tsquery('tim' || ':*'))
UNION SELECT *
FROM "Reservation"."Reservation" AS x
         INNER JOIN "Reservation"."Booking" AS "x.Booking" ON x."BookingId" = "x.Booking"."Id"
         INNER JOIN "Reservation"."Person" AS "x.Booking.Booker" ON "x.Booking"."BookerId" = "x.Booking.Booker"."Id"
         INNER JOIN "Reservation"."Person" AS "x.PrimaryGuest" ON x."PrimaryGuestId" = "x.PrimaryGuest"."Id"
WHERE ("x.Booking.Booker"."SearchVector" @@ to_tsquery('tim' || ':*'))
UNION SELECT *
FROM "Reservation"."Reservation" AS x
         INNER JOIN "Reservation"."Booking" AS "x.Booking" ON x."BookingId" = "x.Booking"."Id"
         INNER JOIN "Reservation"."Person" AS "x.Booking.Booker" ON "x.Booking"."BookerId" = "x.Booking.Booker"."Id"
         INNER JOIN "Reservation"."Person" AS "x.PrimaryGuest" ON x."PrimaryGuestId" = "x.PrimaryGuest"."Id"
WHERE (x."SearchVector" @@ to_tsquery('tim' || ':*'))

and the execution plan, although much more complicated and obviously slower than the one for the AND query is still just a fraction of the OR query:

Unique  (cost=20211.38..20586.04 rows=1441 width=2135) (actual time=93.770..105.010 rows=730 loops=1)
  Buffers: shared hit=18613 read=5 dirtied=1
  I/O Timings: read=4.873
  ->  Sort  (cost=20211.38..20214.99 rows=1441 width=2135) (actual time=93.768..93.897 rows=1312 loops=1)
        Sort Key: [...ALL COLUMNS...]
        Sort Method: quicksort  Memory: 2033kB
        Buffers: shared hit=18613 read=5 dirtied=1
        I/O Timings: read=4.873
        ->  Append  (cost=1058.60..20135.78 rows=1441 width=2135) (actual time=6.695..42.429 rows=1312 loops=1)
              Buffers: shared hit=18613 read=5 dirtied=1
              I/O Timings: read=4.873
              ->  Gather  (cost=1058.60..10355.74 rows=713 width=1546) (actual time=6.694..27.805 rows=687 loops=1)
                    Workers Planned: 1
                    Workers Launched: 1
                    Buffers: shared hit=9976 read=5
                    I/O Timings: read=4.873
                    ->  Nested Loop  (cost=58.60..9284.44 rows=419 width=1546) (actual time=2.641..13.110 rows=344 loops=2)
                          Buffers: shared hit=9976 read=5
                          I/O Timings: read=4.873
                          ->  Nested Loop  (cost=58.18..8836.86 rows=419 width=1217) (actual time=2.626..9.580 rows=344 loops=2)
                                Buffers: shared hit=7227 read=5
                                I/O Timings: read=4.873
                                ->  Nested Loop  (cost=57.89..8621.26 rows=419 width=842) (actual time=2.603..5.754 rows=344 loops=2)
                                      Buffers: shared hit=5165 read=2
                                      I/O Timings: read=0.019
                                      ->  Parallel Bitmap Heap Scan on "Person" "x.PrimaryGuest"  (cost=57.47..3926.77 rows=700 width=329) (actual time=2.579..3.513 rows=625 loops=2)
                                            Recheck Cond: ("SearchVector" @@ to_tsquery('tim:*'::text))
                                            Heap Blocks: exact=560
                                            Buffers: shared hit=729
                                            ->  Bitmap Index Scan on "IX_Person_SearchVector"  (cost=0.00..57.17 rows=1190 width=0) (actual time=5.019..5.019 rows=1250 loops=1)
                                                  Index Cond: ("SearchVector" @@ to_tsquery('tim:*'::text))
                                                  Buffers: shared hit=10
                                      ->  Index Scan using "IX_Reservation_PrimaryGuestId" on "Reservation" x  (cost=0.42..6.71 rows=1 width=513) (actual time=0.003..0.003 rows=1 loops=1250)
                                            Index Cond: ("PrimaryGuestId" = "x.PrimaryGuest"."Id")
                                            Buffers: shared hit=4436 read=2
                                            I/O Timings: read=0.019
                                ->  Index Scan using "PK_Booking" on "Booking" "x.Booking"  (cost=0.29..0.51 rows=1 width=375) (actual time=0.010..0.010 rows=1 loops=687)
                                      Index Cond: ("Id" = x."BookingId")
                                      Buffers: shared hit=2062 read=3
                                      I/O Timings: read=4.854
                          ->  Index Scan using "PK_Person" on "Person" "x.Booking.Booker"  (cost=0.42..1.07 rows=1 width=329) (actual time=0.009..0.009 rows=1 loops=687)
                                Index Cond: ("Id" = "x.Booking"."BookerId")
                                Buffers: shared hit=2749
              ->  Gather  (cost=1058.60..9320.48 rows=713 width=1546) (actual time=2.370..14.158 rows=616 loops=1)
                    Workers Planned: 1
                    Workers Launched: 1
                    Buffers: shared hit=8505 dirtied=1
                    ->  Nested Loop  (cost=58.60..8249.18 rows=419 width=1546) (actual time=0.505..6.246 rows=308 loops=2)
                          Buffers: shared hit=8505 dirtied=1
                          ->  Nested Loop  (cost=58.18..7860.37 rows=419 width=1217) (actual time=0.492..5.308 rows=308 loops=2)
                                Buffers: shared hit=6039 dirtied=1
                                ->  Nested Loop  (cost=57.76..7569.66 rows=326 width=704) (actual time=0.476..4.217 rows=276 loops=2)
                                      Buffers: shared hit=3785
                                      ->  Parallel Bitmap Heap Scan on "Person" "x.Booking.Booker_1"  (cost=57.47..3926.77 rows=700 width=329) (actual time=0.458..3.059 rows=625 loops=2)
                                            Recheck Cond: ("SearchVector" @@ to_tsquery('tim:*'::text))
                                            Heap Blocks: exact=717
                                            Buffers: shared hit=729
                                            ->  Bitmap Index Scan on "IX_Person_SearchVector"  (cost=0.00..57.17 rows=1190 width=0) (actual time=0.802..0.802 rows=1250 loops=1)
                                                  Index Cond: ("SearchVector" @@ to_tsquery('tim:*'::text))
                                                  Buffers: shared hit=10
                                      ->  Index Scan using "IX_Booking_BookerId" on "Booking" "x.Booking_1"  (cost=0.29..5.20 rows=1 width=375) (actual time=0.002..0.002 rows=0 loops=1250)
                                            Index Cond: ("BookerId" = "x.Booking.Booker_1"."Id")
                                            Buffers: shared hit=3056
                                ->  Index Scan using "IX_Reservation_BookingId" on "Reservation" x_1  (cost=0.42..0.88 rows=1 width=513) (actual time=0.003..0.003 rows=1 loops=551)
                                      Index Cond: ("BookingId" = "x.Booking_1"."Id")
                                      Buffers: shared hit=2254 dirtied=1
                          ->  Index Scan using "PK_Person" on "Person" "x.PrimaryGuest_1"  (cost=0.42..0.93 rows=1 width=329) (actual time=0.002..0.002 rows=1 loops=616)
                                Index Cond: ("Id" = x_1."PrimaryGuestId")
                                Buffers: shared hit=2466
              ->  Nested Loop  (cost=109.50..437.94 rows=15 width=1546) (actual time=0.454..0.574 rows=9 loops=1)
                    Buffers: shared hit=132
                    ->  Nested Loop  (cost=109.08..311.38 rows=15 width=1217) (actual time=0.438..0.533 rows=9 loops=1)
                          Buffers: shared hit=96
                          ->  Nested Loop  (cost=108.66..295.36 rows=15 width=888) (actual time=0.418..0.472 rows=9 loops=1)
                                Buffers: shared hit=60
                                ->  Bitmap Heap Scan on "Reservation" x_2  (cost=108.36..170.71 rows=15 width=513) (actual time=0.406..0.424 rows=9 loops=1)
                                      Recheck Cond: ("SearchVector" @@ to_tsquery('tim:*'::text))
                                      Heap Blocks: exact=8
                                      Buffers: shared hit=33
                                      ->  Bitmap Index Scan on "IX_Reservation_SearchVector"  (cost=0.00..108.36 rows=15 width=0) (actual time=0.400..0.400 rows=12 loops=1)
                                            Index Cond: ("SearchVector" @@ to_tsquery('tim:*'::text))
                                            Buffers: shared hit=25
                                ->  Index Scan using "PK_Booking" on "Booking" "x.Booking_2"  (cost=0.29..8.31 rows=1 width=375) (actual time=0.004..0.004 rows=1 loops=9)
                                      Index Cond: ("Id" = x_2."BookingId")
                                      Buffers: shared hit=27
                          ->  Index Scan using "PK_Person" on "Person" "x.Booking.Booker_2"  (cost=0.42..1.07 rows=1 width=329) (actual time=0.005..0.005 rows=1 loops=9)
                                Index Cond: ("Id" = "x.Booking_2"."BookerId")
                                Buffers: shared hit=36
                    ->  Index Scan using "PK_Person" on "Person" "x.PrimaryGuest_2"  (cost=0.42..8.44 rows=1 width=329) (actual time=0.003..0.003 rows=1 loops=9)
                          Index Cond: ("Id" = x_2."PrimaryGuestId")
                          Buffers: shared hit=36
Planning Time: 3.678 ms
Execution Time: 105.752 ms

Why is postgres choosing such a slow execution plan for the OR query? and how can I influence it?

  • Can you please show us the execution plans generated using explain (analyze, buffers, format text) (not just a "simple" explain). My guess is that the OR condition estimates so many rows from each table that the index scan doesn't make sense, but proof for that can only be seen when the plan includes estimated and actual row counts. And don't pay attention to the cost values, the most important parts are the strategies chosen and the row estimates (which indicate if the chosen strategies are a good choice) – a_horse_with_no_name Aug 14 at 9:07
  • 1
    OR conditions are usually a performance killer for query optimizers (that's essentially true for all planners, not only Postgres) and rewriting them as a UNION is very often a good way to optimize things. – a_horse_with_no_name Aug 14 at 9:13
  • Which Postgres version are you using? – a_horse_with_no_name Aug 14 at 9:13
  • @a_horse_with_no_name thx, I've updated the execution plans and also searched for a more realistic term, which actually returns few rows; I've also tagged it with the correct version, it's postgres 11 on RDS – peter Aug 14 at 9:17
  • See this answer for an explanation why PostgreSQL cannot rewrite the query to UNIONS automatically. – Laurenz Albe Aug 14 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.