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

RESOLVED FIXED

Status

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

People

(Reporter: morgamic, Assigned: aravind)

Tracking

Details

(Reporter)

Description

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;

Thanks.
(Assignee)

Updated

9 years ago
Assignee: server-ops → aravind
(Assignee)

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)
Status: NEW → RESOLVED
Last Resolved: 9 years ago
Resolution: --- → FIXED
(Reporter)

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.