On 2013-10-07, at 12:31 PM, David E. Wheeler <david@kineticode.com> wrote:
> On Oct 6, 2013, at 2:27 PM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> Just to be clear, the question in question would now be:
>>
>> EXPLAIN ANALYZE SELECT e.id, t.id, e.usr__id, e.obj_id, e.timestamp, t.key_name, t.name, t.description, c.pkg_name, CASE WHEN e.id IN (SELECT event__id FROM alert) THEN 1 ELSE 0 END, ta.name, ea.value FROM event e LEFT JOIN event_attr ea ON e.id = ea.event__id LEFT JOIN event_type_attr ta ON ea.event_type_attr__id = ta.id, class c, event_type t WHERE e.event_type__id = t.id AND t.class__id = c.id AND e.obj_id = $1 AND LOWER(c.pkg_name) LIKE LOWER($2) ORDER BY e.timestamp DESC, e.id DESC;
>>
>> … and I'd need to figure out what "real values" would be in this query?
>
> Yeah. Replace $1 with a valid object ID and $2 with the search query.
>
>> I'm not sure what operation is creating the query, so I'm not 100% clear on where I'd find said real values.
>
> If you submit a form, either the URL or the form data should have values.
Hmmm, well, if I'm reading the query correctly, it's looking for an object and a class package name. I'm kinda' unclear on what this query is being generated by, so I'm not very confident about what values to use… however, just using two arbitrary values, a story object and 'Bric::Util::Job' as the class package name, here's what I got:
Sort (cost=4400.67..4400.73 rows=27 width=267) (actual time=241.439..241.441 rows=1 loops=1)
Sort Key: e."timestamp", e.id
Sort Method: quicksort Memory: 25kB
-> Hash Left Join (cost=11.40..4400.02 rows=27 width=267) (actual time=94.270..241.383 rows=1 loops=1)
Hash Cond: (ea.event_type_attr__id = ta.id)
-> Nested Loop Left Join (cost=8.35..4396.53 rows=27 width=125) (actual time=93.955..241.065 rows=1 loops=1)
-> Hash Join (cost=8.35..4230.42 rows=27 width=116) (actual time=55.056..202.163 rows=1 loops=1)
Hash Cond: (e.event_type__id = t.id)
-> Index Scan using idx_event__obj_id on event e (cost=0.00..4213.72 rows=2155 width=24) (actual time=0.184..161.611 rows=30989 loops=1)
Index Cond: (obj_id = 20403)
-> Hash (cost=8.32..8.32 rows=2 width=96) (actual time=0.543..0.543 rows=5 loops=1)
-> Hash Join (cost=3.20..8.32 rows=2 width=96) (actual time=0.376..0.531 rows=5 loops=1)
Hash Cond: (t.class__id = c.id)
-> Seq Scan on event_type t (cost=0.00..4.53 rows=153 width=76) (actual time=0.019..0.213 rows=153 loops=1)
-> Hash (cost=3.19..3.19 rows=1 width=28) (actual time=0.103..0.103 rows=1 loops=1)
-> Seq Scan on class c (cost=0.00..3.19 rows=1 width=28) (actual time=0.075..0.097 rows=1 loops=1)
Filter: (lower((pkg_name)::text) ~~ 'bric::util::job'::text)
-> Index Scan using fkx_event__event_attr on event_attr ea (cost=0.00..6.14 rows=1 width=13) (actual time=38.890..38.890 rows=0 loops=1)
Index Cond: (e.id = ea.event__id)
-> Hash (cost=1.39..1.39 rows=39 width=150) (actual time=0.169..0.169 rows=39 loops=1)
-> Seq Scan on event_type_attr ta (cost=0.00..1.39 rows=39 width=150) (actual time=0.060..0.104 rows=39 loops=1)
SubPlan 1
-> Seq Scan on alert (cost=0.00..1.14 rows=14 width=4) (actual time=0.013..0.032 rows=15 loops=1)
Total runtime: 241.689 ms
Ideas on where I can find more realistic values to use?
--
Phillip Smith
http://phillipadsmith.com