455907 - Need explain analyze out put for search query

Status: RESOLVED FIXED

(Infrastructure & Operations Graveyard :: WebOps: Other, task)

Description

[Michael Morgan [:morgamic]]

Need to debug the queries from the socorro search page to understand how the production database is planning this query.  Can someone run this and send me the output:

explain analyze SELECT reports.signature, count(reports.id), count(CASE WHEN (reports.os_name = 'Windows NT') THEN 1 END) AS is_windows, count(CASE WHEN (reports.os_name = 'Mac OS X') THEN 1 END) AS is_mac, count(CASE WHEN (reports.os_name = 'Linux') THEN 1 END) AS is_linux, count(CASE WHEN (reports.os_name = 'Solaris') THEN 1 END) AS is_solaris FROM reports WHERE reports.signature IS NOT NULL AND reports.date BETWEEN now() - CAST('1 weeks' AS INTERVAL) AND now() GROUP BY reports.signature ORDER BY count(reports.id) DESC LIMIT 100;

Thanks.

Comment 1

[Aravind Gottipati [:aravind]]

                                                                                         
                                                                                         QUERY PLAN                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=44639.02..44639.27 rows=100 width=738) (actual time=2402688.525..2402688.718 rows=100 loops=1)
   ->  Sort  (cost=44639.02..44681.38 rows=16944 width=738) (actual time=2402688.521..2402688.587 rows=100 loops=1)
         Sort Key: count(public.reports.id)
         ->  HashAggregate  (cost=42898.15..43448.83 rows=16944 width=738) (actual time=2402416.552..2402531.712 rows=58068 loops=1)          
               ->  Append  (cost=0.01..42643.99 rows=16944 width=738) (actual time=284.928..2400845.333 rows=236906 loops=1)
                     ->  Index Scan using idx_reports_date on reports  (cost=0.01..4.83 rows=1 width=738) (actual time=33.456..33.456 rows=0 loops=1)     
                           Index Cond: ((date >= (now() - '7 days'::interval)) AND (date <= now()))
                           Filter: (signature IS NOT NULL)
                     ->  Index Scan using idx_reports_part1_date on reports_part1 reports  (cost=0.01..42639.16 rows=16943 width=44) (actual time=251.469..2400319.110 rows=236906 loops=1)
                           Index Cond: ((date >= (now() - '7 days'::interval)) AND (date <= now()))
                           Filter: (signature IS NOT NULL)
 Total runtime: 2402794.356 ms
(12 rows)

Comment 2

[Michael Morgan [:morgamic]]

Thanks, this helps.