Very slow running query PostgreSQL 9.3.4

Поиск
Список
Период
Сортировка
От Burgess, Freddie
Тема Very slow running query PostgreSQL 9.3.4
Дата
Msg-id 3BBE635F64E28D4C899377A61DAA9FE03F08FF8E@NBSVR-MAIL01.radiantblue.local
обсуждение исходный текст
Ответы Re: Very slow running query PostgreSQL 9.3.4  (Victor Yegorov <vyegorov@gmail.com>)
Список pgsql-performance
Any suggestions on a query rewrite to speed this poor performing query up.

work_mem=164MB

Thanks

explain (analyze on, buffers on) select * from SARS_IMPACT_REPORT this_
  where this_.model_uid=1
    and this_.source_date_time between '2014-08-08 19:21:08.212'::timestamp without time zone and '2014-08-09 03:59:19.388'::timestamp without time zone
    and (ST_within (this_.clone_location,'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F')
      or ST_touches (this_.clone_location,'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'))
   order by source_date_time asc, source_uid asc, clone_report_uid
   limit 3000;
                                                                                                                                                                                                                        
                                                                                        QUERY PLAN          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5574.70..5574.70 rows=1 width=141) (actual time=33998.629..33999.142 rows=3000 loops=1)
   Buffers: shared hit=6 read=39347
   ->  Sort  (cost=5574.44..5574.70 rows=104 width=141) (actual time=33997.358..33998.246 rows=15000 loops=1)
         Sort Key: this_.source_date_time, this_.source_uid, this_.clone_report_uid
         Sort Method: top-N heapsort  Memory: 4753kB
         Buffers: shared hit=6 read=39347
         ->  Append  (cost=0.00..5570.95 rows=104 width=141) (actual time=8.302..33417.186 rows=710202 loops=1)
               Buffers: shared read=39347
               ->  Seq Scan on SARS_IMPACT_REPORT this_  (cost=0.00..0.00 rows=1 width=648) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: ((source_date_time >= '2014-08-08 19:21:08.212'::timestamp without time zone) AND (source_date_time <= '2014-08-09 03:59:19.388'::timestamp without time zone) AND (clone_location && '01030000000100000005000000
6787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry) AND (model_uid = 1) AND (_st_contains('010300000001000000
050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry, clone_location) OR _st_touches(clone_location, '0
10300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))
               ->  Seq Scan on SARS_IMPACT_REPORT_overflow this__1  (cost=0.00..72.40 rows=1 width=648) (actual time=0.000..0.000 rows=0 loops=1)
                     Filter: ((source_date_time >= '2014-08-08 19:21:08.212'::timestamp without time zone) AND (source_date_time <= '2014-08-09 03:59:19.388'::timestamp without time zone) AND (clone_location && '01030000000100000005000000
6787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry) AND (model_uid = 1) AND (_st_contains('010300000001000000
050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry, clone_location) OR _st_touches(clone_location, '0
10300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))
               ->  Index Scan using idx_clone_report_query_y201408 on SARS_IMPACT_REPORT_y2014m08 this__2  (cost=0.57..5570.95 rows=103 width=136) (actual time=8.300..33308.118 rows=710202 loops=1)
                     Index Cond: ((model_uid = 1::bigint) AND (source_date_time >= '2014-08-08 19:21:08.212'::timestamp without time zone) AND (source_date_time <= '2014-08-09 03:59:19.388'::timestamp without time zone))
                     Filter: ((clone_location && '010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)
                            AND _st_contains('010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry, clone_location)
                             OR _st_touches (clone_location, '010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))
                     Rows Removed by Filter: 912821
                     Buffers: shared read=39347
 Total runtime: 34000.160 ms   <-- Unacceptable runtime

Indexes:
    "idx_clone_report_y2014m08_pkey" PRIMARY KEY, btree (clone_report_uid)
    "idx_clone_report_query_y201408" btree (model_uid, source_date_time)
    "sidx_clone_report_y2014m08" gist (clone_location)


В списке pgsql-performance по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: Performance issue: index not used on GROUP BY...
Следующее
От: Victor Yegorov
Дата:
Сообщение: Re: Very slow running query PostgreSQL 9.3.4