Mailing List Archive

Slow running PostgreSQL queries advise
Anyone seeing queries like this causing a big slowdown?

843140190 | datname | 18874 | 16386 | username | | f | 2013-09-19 14:26:09.941879-07 | 2013-09-19 14:26:10.146926-07 | 2013-09-19 14:23:17.340985-07 | 127.0.0.1 | 41034
: 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
: ;


Thoughts on how to tune things to avoid these?

Phillip.

--
Phillip Smith
http://phillipadsmith.com
Re: Slow running PostgreSQL queries advise [ In reply to ]
On Sep 19, 2013, at 2:29 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

> Thoughts on how to tune things to avoid these?

Would you run this query with EXPLAIN ANALYZE? Replace $1 and $2 with real values.

Thanks,

David
Re: Slow running PostgreSQL queries advise [ In reply to ]
On 2013-09-19, at 5:21 PM, David E. Wheeler <david@kineticode.com> wrote:

> On Sep 19, 2013, at 2:29 PM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> Thoughts on how to tune things to avoid these?
>
> Would you run this query with EXPLAIN ANALYZE? Replace $1 and $2 with real values.

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?

I'm not sure what operation is creating the query, so I'm not 100% clear on where I'd find said real values.

Phillip.

--
Phillip Smith
http://phillipadsmith.com
Re: Slow running PostgreSQL queries advise [ In reply to ]
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.

D
Re: Slow running PostgreSQL queries advise [ In reply to ]
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
Re: Slow running PostgreSQL queries advise [ In reply to ]
On Oct 8, 2013, at 5:50 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

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

Please include the query you ran in the paste. In fact, paste the whole thing here:

http://explain.depesz.com

Then send us the link.

Thanks,

David
Re: Slow running PostgreSQL queries advise [ In reply to ]
On 2013-10-09, at 3:31 PM, "David E. Wheeler" <david@kineticode.com> wrote:

> On Oct 8, 2013, at 5:50 PM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> 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:
>
> Please include the query you ran in the paste. In fact, paste the whole thing here:
>
> http://explain.depesz.com
>
> Then send us the link.

http://explain.depesz.com/s/08i

--
Phillip Smith
http://phillipadsmith.com
Re: Slow running PostgreSQL queries advise [ In reply to ]
On 2013-10-09, at 3:40 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

>
> On 2013-10-09, at 3:31 PM, "David E. Wheeler" <david@kineticode.com> wrote:
>
>> On Oct 8, 2013, at 5:50 PM, Phillip Smith <ps@phillipadsmith.com> wrote:
>>
>>> 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:
>>
>> Please include the query you ran in the paste. In fact, paste the whole thing here:
>>
>> http://explain.depesz.com
>>
>> Then send us the link.
>

Try this one instead: http://explain.depesz.com/s/GrcZ

--
Phillip Smith
http://phillipadsmith.com
Re: Slow running PostgreSQL queries advise [ In reply to ]
On Oct 9, 2013, at 1:40 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

> http://explain.depesz.com/s/08i

You didn’t include the query. And it looks like you had the site anonymize the query, so I can’t tell what indexes correspond to what tables. But anyway, your statistics are way off. Do you have autovacuum enabled? Try running this:

ANALYZE event;
ANALYZE event_attr;
ANALYZE event_type_attr;

Then run the query again and paste the plan.

David
Re: Slow running PostgreSQL queries advise [ In reply to ]
On 2013-10-09, at 6:16 PM, "David E. Wheeler" <david@kineticode.com> wrote:

> On Oct 9, 2013, at 1:40 PM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> http://explain.depesz.com/s/08i
>
> You didn’t include the query.

It's there. Click the 'text' tab.


> And it looks like you had the site anonymize the query

Look at this link (I goofed the first one):
http://explain.depesz.com/s/GrcZ


> , so I can’t tell what indexes correspond to what tables. But anyway, your statistics are way off. Do you have autovacuum enabled?

Yep.


> Try running this:
>
> ANALYZE event;
> ANALYZE event_attr;
> ANALYZE event_type_attr;

No result other than "OK" for those. 202 sec query time.


--
Phillip Smith
http://phillipadsmith.com
http://twitter.com/phillipadsmith
http://linkedin.com/in/phillipadsmith

Oaxaca: (011 52 1) 951 154 4620
Toronto: (647) 361-8248
Toll-free: (888) 418-1868

If your email inbox is out of control, check out http://sanebox.com/t/s0q7m

Save our in-boxes! http://emailcharter.org
Re: Slow running PostgreSQL queries advise [ In reply to ]
On Oct 9, 2013, at 6:01 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

>> You didn’t include the query.
>
> It's there. Click the 'text' tab.

I did. The query is not there, just the plan.

>> And it looks like you had the site anonymize the query
>
> Look at this link (I goofed the first one):
> http://explain.depesz.com/s/GrcZ

There we go.

> No result other than "OK" for those. 202 sec query time.

Did you EXPLAIN ANALYZE the query again after running those?

David