If you think a bug might affect users in the 57 release, please set the correct tracking and status flags for Release Management.

Need explain analyze out put for search query



Infrastructure & Operations
WebOps: Other
9 years ago
4 years ago


(Reporter: morgamic, Assigned: aravind)





9 years ago
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;



9 years ago
Assignee: server-ops → aravind

Comment 1

9 years ago
                                                                                         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)
Last Resolved: 9 years ago
Resolution: --- → FIXED

Comment 2

9 years ago
Thanks, this helps.
Component: Server Operations: Web Operations → WebOps: Other
Product: mozilla.org → Infrastructure & Operations
You need to log in before you can comment on or make changes to this bug.