Skip to main content
Database Administrators

Return to Question

added 832 characters in body
Source Link
Salami
Salami
  • 151
  • 6

Edit 3

The new query:

EXPLAIN (ANALYZE, BUFFERS) SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel ORDER BY name <->> 'trade center new york mariott' LIMIT 5;
                                                                       QUERY PLAN

 Limit  (cost=0.41..5.65 rows=5 width=31) (actual time=3632.397..3633.554 rows=5 loops=1)
   Buffers: shared hit=1 read=32537
   I/O Timings: read=184.432
   ->  Index Scan using autocomplete_gist on hotels_hotel  (cost=0.41..2140836.75 rows=2043215 width=31) (actual time=3632.394..3633.548 rows=5 loops=1)
         Order By: ((name)::text <->> 'trade center new york mariott'::text)
         Buffers: shared hit=1 read=32537
         I/O Timings: read=184.432
 Planning Time: 0.250 ms
 Execution Time: 3679.847 ms

Edit 3

The new query:

EXPLAIN (ANALYZE, BUFFERS) SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel ORDER BY name <->> 'trade center new york mariott' LIMIT 5;
                                                                       QUERY PLAN

 Limit  (cost=0.41..5.65 rows=5 width=31) (actual time=3632.397..3633.554 rows=5 loops=1)
   Buffers: shared hit=1 read=32537
   I/O Timings: read=184.432
   ->  Index Scan using autocomplete_gist on hotels_hotel  (cost=0.41..2140836.75 rows=2043215 width=31) (actual time=3632.394..3633.548 rows=5 loops=1)
         Order By: ((name)::text <->> 'trade center new york mariott'::text)
         Buffers: shared hit=1 read=32537
         I/O Timings: read=184.432
 Planning Time: 0.250 ms
 Execution Time: 3679.847 ms
added indices
Source Link
Salami
Salami
  • 151
  • 6

Edit 1:

EXPLAIN ANALYZE SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name LIMIT 5;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1264.75 rows=5 width=27) (actual time=0.391..15.981 rows=5 loops=1)
   ->  Seq Scan on hotels_hotel  (cost=0.00..516777.29 rows=2043 width=27) (actual time=0.384..15.969 rows=5 loops=1)
         Filter: ('trade center new york mariott'::text %> (name)::text)
         Rows Removed by Filter: 292
 Planning Time: 0.285 ms
 Execution Time: 16.041 ms
(6 rows)

Time: 17.800 ms

Edit 2

The relevant indices present on the table:

CREATE INDEX autocomplete_gist ON public.hotels_hotel USING gist (name gist_trgm_ops)
CREATE INDEX autocomplete_name_idx ON public.hotels_hotel USING gin (name gin_trgm_ops)
CREATE INDEX hotels_hotel_name_a9005e17 ON public.hotels_hotel USING btree (name)
CREATE INDEX hotels_hotel_name_a9005e17_like ON public.hotels_hotel USING btree (name varchar_pattern_ops)
CREATE UNIQUE INDEX hotels_hotel_pkey ON public.hotels_hotel USING btree (id)
CREATE INDEX hotels_hotel_popularity_05985c85 ON public.hotels_hotel USING btree (popularity)

Edit 1:

EXPLAIN ANALYZE SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name LIMIT 5;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1264.75 rows=5 width=27) (actual time=0.391..15.981 rows=5 loops=1)
   ->  Seq Scan on hotels_hotel  (cost=0.00..516777.29 rows=2043 width=27) (actual time=0.384..15.969 rows=5 loops=1)
         Filter: ('trade center new york mariott'::text %> (name)::text)
         Rows Removed by Filter: 292
 Planning Time: 0.285 ms
 Execution Time: 16.041 ms
(6 rows)

Time: 17.800 ms

Edit 1

EXPLAIN ANALYZE SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name LIMIT 5;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1264.75 rows=5 width=27) (actual time=0.391..15.981 rows=5 loops=1)
   ->  Seq Scan on hotels_hotel  (cost=0.00..516777.29 rows=2043 width=27) (actual time=0.384..15.969 rows=5 loops=1)
         Filter: ('trade center new york mariott'::text %> (name)::text)
         Rows Removed by Filter: 292
 Planning Time: 0.285 ms
 Execution Time: 16.041 ms
(6 rows)

Time: 17.800 ms

Edit 2

The relevant indices present on the table:

CREATE INDEX autocomplete_gist ON public.hotels_hotel USING gist (name gist_trgm_ops)
CREATE INDEX autocomplete_name_idx ON public.hotels_hotel USING gin (name gin_trgm_ops)
CREATE INDEX hotels_hotel_name_a9005e17 ON public.hotels_hotel USING btree (name)
CREATE INDEX hotels_hotel_name_a9005e17_like ON public.hotels_hotel USING btree (name varchar_pattern_ops)
CREATE UNIQUE INDEX hotels_hotel_pkey ON public.hotels_hotel USING btree (id)
CREATE INDEX hotels_hotel_popularity_05985c85 ON public.hotels_hotel USING btree (popularity)
added index on name column and reran queries for slight improvement
Source Link
Salami
Salami
  • 151
  • 6
SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name ORDER BY sml DESC LIMIT 5;

                                          name                                          |    sml
----------------------------------------------------------------------------------------+------------
 Courtyard by Marriott New York Downtown Manhattan/World Trade Center Area              | 0.53846157
 Fairfield Inn & Suites by Marriott New York Downtown Manhattan/World Trade Center Area | 0.53846157
 Residence Inn by Marriott New York Downtown Manhattan/World Trade Center Area          | 0.53846157
 AC Hotel by Marriott New York Times Square                                             |    0.53125
 Courtyard by Marriott World Trade Center, Abu Dhabi                                    |  0.5263158
(5 rows)

Time: 121519602.654969 ms (00:1209.152603)


EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS) SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name ORDER BY sml DESC LIMIT 5;
                                                                                          QUERY PLAN                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=502890cost=65390.0353..50289065391.6111 rows=5 width=27) (actual time=11119time=9619.666113..111269625.331482 rows=5 loops=1)
   Output: name, (word_similarity('trade center new york mariott'::text, (name)::text))
   Buffers: shared hit=1746167
   ->  Gather Merge  (cost=502890cost=65390.0353..50308865589.6111 rows=1702 width=27) (actual time=11063time=9619.630109..110709625.293474 rows=5 loops=1)
         Output: name, (word_similarity('trade center new york mariott'::text, (name)::text))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=1746167
         ->  Sort  (cost=501890cost=64390.0150..50189264392.1363 rows=851 width=27) (actual time=11006time=9612.845578..110069612.846580 rows=4 loops=3)
               Output: name, (word_similarity('trade center new york mariott'::text, (name)::text))
               Sort Key: (word_similarity('trade center new york mariott'::text, (hotels_hotel.name)::text)) DESC
               Sort Method: top-N heapsort  Memory: 25kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               Buffers: shared hit=1746167
               Worker 0: actual time=9610.098..9610.100 rows=5 loops=1
                 Buffers: shared hit=581861
               Worker 1: actual time=9609.314..9609.317 rows=5 loops=1
                 Buffers: shared hit=579828
               ->  Parallel SeqIndex Only Scan using hotels_hotel_name_a9005e17 on public.hotels_hotel  (cost=0.0043..50187564376.8737 rows=851 width=27) (actual time=82time=4.805040..109969606.658166 rows=15070 loops=3)
                     Output: name, word_similarity('trade center new york mariott'::text, (name)::text)
                     Filter: ('trade center new york mariott'::text %> (hotels_hotel.name)::text)
                     Rows Removed by Filter: 666002
 Planning Time: 0.921 ms
 JIT                Heap Fetches: 2
   Functions                  Buffers: 13shared hit=1746113
   Options                  Worker 0: Inliningactual true,time=0.281..9603.591 Optimizationrows=14890 true,loops=1
 Expressions true, Deforming true                   Buffers: shared hit=581834
   Timing                  Worker 1: Generationactual 3time=8.177157..9602.811 ms,rows=14678 Inliningloops=1
 185.660 ms, Optimization 72.270 ms, Emission 42.028 ms, Total 303             Buffers: shared hit=579801
 Planning Time: 0.134396 ms
 Execution Time: 111289625.345576 ms
(1831 rows)

Time: 9626.933 
 ms (00:09.627)

SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name ORDER BY sml DESC LIMIT 5;

                                          name                                          |    sml
----------------------------------------------------------------------------------------+------------
 Courtyard by Marriott New York Downtown Manhattan/World Trade Center Area              | 0.53846157
 Fairfield Inn & Suites by Marriott New York Downtown Manhattan/World Trade Center Area | 0.53846157
 Residence Inn by Marriott New York Downtown Manhattan/World Trade Center Area          | 0.53846157
 AC Hotel by Marriott New York Times Square                                             |    0.53125
 Courtyard by Marriott World Trade Center, Abu Dhabi                                    |  0.5263158
(5 rows)

Time: 12151.654 ms (00:12.152)


EXPLAIN ANALYZE SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name ORDER BY sml DESC LIMIT 5;

 Limit  (cost=502890.03..502890.61 rows=5 width=27) (actual time=11119.666..11126.331 rows=5 loops=1)
   ->  Gather Merge  (cost=502890.03..503088.61 rows=1702 width=27) (actual time=11063.630..11070.293 rows=5 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=501890.01..501892.13 rows=851 width=27) (actual time=11006.845..11006.846 rows=4 loops=3)
               Sort Key: (word_similarity('trade center new york mariott'::text, (name)::text)) DESC
               Sort Method: top-N heapsort  Memory: 25kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               ->  Parallel Seq Scan on hotels_hotel  (cost=0.00..501875.87 rows=851 width=27) (actual time=82.805..10996.658 rows=15070 loops=3)
                     Filter: ('trade center new york mariott'::text %> (name)::text)
                     Rows Removed by Filter: 666002
 Planning Time: 0.921 ms
 JIT:
   Functions: 13
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 3.177 ms, Inlining 185.660 ms, Optimization 72.270 ms, Emission 42.028 ms, Total 303.134 ms
 Execution Time: 11128.345 ms
(18 rows)  
 

SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name ORDER BY sml DESC LIMIT 5;

                                          name                                          |    sml
----------------------------------------------------------------------------------------+------------
 Courtyard by Marriott New York Downtown Manhattan/World Trade Center Area              | 0.53846157
 Fairfield Inn & Suites by Marriott New York Downtown Manhattan/World Trade Center Area | 0.53846157
 Residence Inn by Marriott New York Downtown Manhattan/World Trade Center Area          | 0.53846157
 AC Hotel by Marriott New York Times Square                                             |    0.53125
 Courtyard by Marriott World Trade Center, Abu Dhabi                                    |  0.5263158
(5 rows)

Time: 9602.969 ms (00:09.603)


EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS) SELECT name, word_similarity('trade center new york mariott', name) AS sml from hotels_hotel where 'trade center new york mariott' %> name ORDER BY sml DESC LIMIT 5;
                                                                                          QUERY PLAN                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=65390.53..65391.11 rows=5 width=27) (actual time=9619.113..9625.482 rows=5 loops=1)
   Output: name, (word_similarity('trade center new york mariott'::text, (name)::text))
   Buffers: shared hit=1746167
   ->  Gather Merge  (cost=65390.53..65589.11 rows=1702 width=27) (actual time=9619.109..9625.474 rows=5 loops=1)
         Output: name, (word_similarity('trade center new york mariott'::text, (name)::text))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=1746167
         ->  Sort  (cost=64390.50..64392.63 rows=851 width=27) (actual time=9612.578..9612.580 rows=4 loops=3)
               Output: name, (word_similarity('trade center new york mariott'::text, (name)::text))
               Sort Key: (word_similarity('trade center new york mariott'::text, (hotels_hotel.name)::text)) DESC
               Sort Method: top-N heapsort  Memory: 25kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               Buffers: shared hit=1746167
               Worker 0: actual time=9610.098..9610.100 rows=5 loops=1
                 Buffers: shared hit=581861
               Worker 1: actual time=9609.314..9609.317 rows=5 loops=1
                 Buffers: shared hit=579828
               ->  Parallel Index Only Scan using hotels_hotel_name_a9005e17 on public.hotels_hotel  (cost=0.43..64376.37 rows=851 width=27) (actual time=4.040..9606.166 rows=15070 loops=3)
                     Output: name, word_similarity('trade center new york mariott'::text, (name)::text)
                     Filter: ('trade center new york mariott'::text %> (hotels_hotel.name)::text)
                     Rows Removed by Filter: 666002
                     Heap Fetches: 2
                     Buffers: shared hit=1746113
                     Worker 0: actual time=0.281..9603.591 rows=14890 loops=1
                       Buffers: shared hit=581834
                     Worker 1: actual time=8.157..9602.811 rows=14678 loops=1
                       Buffers: shared hit=579801
 Planning Time: 0.396 ms
 Execution Time: 9625.576 ms
(31 rows)

Time: 9626.933 ms (00:09.627)
added explain analyze for query without sorting as per comment request
Source Link
Salami
Salami
  • 151
  • 6
Source Link
Salami
Salami
  • 151
  • 6