Re: Very slow running query PostgreSQL 9.3.4

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2014-08-29 7:28 GMT+03:00 Burgess, Freddie <FBurgess@xxxxxxxxxxxxxxx>:
               ->  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


First, I think your stats are off, note this line:

->  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)

Real rows returned are 3 orders of magnituded higher then expected.

Also, given almost a million rows were removed by the filter, it'd be worth trying to select on `clone_location` first.


Could you do the following:

VACUUM ANALYZE sars_impact_report_y2014m08;
VACUUM ANALYZE sars_impact_report;
explain (analyze, buffers)
WITH clone AS (
    SELECT * FROM SARS_IMPACT_REPORT
WHERE ST_within(this_.clone_location,'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F') 
   OR ST_touches (this_.clone_location,'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F')
)
select * from clone 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 
 order by source_date_time asc, source_uid asc, clone_report_uid
 limit 3000;


--
Victor Y. Yegorov

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux