Обсуждение: Postgres 8.3 vs. 8.4 - Query plans and performance
Hello, we have performance problems running several queries pon postgres 8.4 . Using the previous version (8.3) our queries performs well (The queries are quite complex, consisting of several sub-queries and various spatial functions). Using a new server with debian squeeze and postgres 8.4 from the aptitude packages our queries won't finish and uses a lot of CPU time (the used CPU runs mainly at 100%). Indizes and datasets of the two databases are the same. Regarding to the query plans there are some differences between the two versions (using the same queries and after running VACUUM). Are there some major changes from 8.3 to 8.4 that cause this bad performance? Regards, Jo
Jo, > we have performance problems running several queries pon postgres 8.4 . > Using the previous version (8.3) our queries performs well > (The queries are quite complex, consisting of several sub-queries and > various spatial functions). > > Are there some major changes from 8.3 to 8.4 that cause this bad > performance? Have you checked the release notes? http://www.postgresql.org/docs/8.4/static/release-8-4.html If you can, I'd also suggest that you show the execution plans on 8.3/8.4 on the list. One more thing: did you also check the server configuration (work_mem, ...)? -hannes
I set the work_mem to 100MB and the shared buffers are 2 GB The query plans are long and complex. I send the beginning of the two plans. Hope this helps to understand the differences. I assume the join strategy in 8.3 differs from the one in 8.4. ************************************* The beginning of the 8.4: ************************************* "Seq Scan on relations (cost=0.00..1502557856.52 rows=332613 width=24)" " Filter: (((SubPlan 36) OR (SubPlan 37)) AND (SubPlan 38))" " SubPlan 1" " -> Index Scan using idx_relation_tags_relation_id on relation_tags (cost=0.00..8.97 rows=1 width=0)" " Index Cond: (relation_id = $0)" " Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))" " SubPlan 2" " -> Seq Scan on relation_tags (cost=0.00..102686.97 rows=451600 width=8)" " Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))" " SubPlan 3" " -> Limit (cost=0.00..8.97 rows=1 width=14)" " -> Index Scan using idx_relation_tags_relation_id on relation_tags (cost=0.00..8.97 rows=1 width=14)" " Index Cond: (relation_id = $0)" " Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))" " SubPlan 4" " -> Index Scan using idx_relation_tags_relation_id on relation_tags (cost=0.00..8.97 rows=1 width=0)" " Index Cond: (relation_id = $0)" " Filter: ((k ~~* 'name:de'::text) AND (v !~~* ''::text))" " SubPlan 5" " -> Seq Scan on relation_tags (cost=0.00..102686.97 rows=52 width=8)" " Filter: ((k ~~* 'name:de'::text) AND (v !~~* ''::text))" ************************************* The beginning of the 8.3 query plan: ************************************* "Seq Scan on relations (cost=0.00..491771409147.20 rows=334529 width=24)" " Filter: ((subplan) AND ((subplan) OR (subplan)))" " SubPlan" " -> Seq Scan on relation_tags (cost=0.00..138595.94 rows=170556 width=0)" " Filter: ((k ~~* 'boundary'::text) OR ((k ~~* 'type'::text) AND (v ~~* 'boundary'::text) AND (relation_id = $0)))" " -> Nested Loop (cost=3960.20..112931.40 rows=1 width=0)" " -> HashAggregate (cost=3960.20..3960.69 rows=49 width=8)" " -> Index Scan using idx_relation_members_relation_id on relation_members (cost=0.00..3959.83 rows=148 width=8)" " Index Cond: (relation_id = $0)" " Filter: ((member_role ~~* 'outer'::text) AND (member_type = 'W'::bpchar))" " -> Index Scan using idx_way_tags_way_id on way_tags (cost=0.00..2223.88 rows=1 width=8)" " Index Cond: (public.way_tags.way_id = public.relation_members.member_id)" " Filter: ((public.way_tags.k ~~* 'boundary'::text) OR ((public.way_tags.k ~~* 'type'::text) AND (public.way_tags.v ~~* 'boundary'::text)))" " -> GroupAggregate (cost=0.00..29281.72 rows=1 width=8)" " Filter: (count(*) = 1)" " -> Index Scan using idx_relation_members_relation_id on relation_members (cost=0.00..29281.33 rows=74 width=8)" " Index Cond: (relation_id = $0)" " Filter: ((member_role = 'outer'::text) AND (member_type = 'W'::bpchar) AND (subplan))" " SubPlan" " -> Index Scan using idx_ways_id on ways (cost=0.00..22.75 rows=1 width=0)" " Index Cond: (id = $2)" " Filter: (st_isclosed(linestring) AND (st_numpoints(linestring) > 3))" " -> Nested Loop (cost=3957.48..5255.55 rows=6 width=978)" " -> HashAggregate (cost=3957.48..3958.05 rows=57 width=8)" " -> Index Scan using idx_relation_members_relation_id on relation_members (cost=0.00..3957.04 rows=173 width=8)" " Index Cond: (relation_id = $0)" " Filter: (member_role ~~* 'outer'::text)" Jo On 14.03.2011 14:15, Hannes Erven wrote: > Jo, > > >> we have performance problems running several queries pon postgres 8.4 . >> Using the previous version (8.3) our queries performs well >> (The queries are quite complex, consisting of several sub-queries and >> various spatial functions). >> >> Are there some major changes from 8.3 to 8.4 that cause this bad >> performance? > > Have you checked the release notes? > http://www.postgresql.org/docs/8.4/static/release-8-4.html > > If you can, I'd also suggest that you show the execution plans on > 8.3/8.4 on the list. > > One more thing: did you also check the server configuration (work_mem, ...)? > > > -hannes >
Hello 2011/3/14 Jo <jl.news@uni-bonn.de>: > I set the work_mem to 100MB and the shared buffers are 2 GB > > The query plans are long and complex. I send the beginning of the > two plans. Hope this helps to understand the differences. > I assume the join strategy in 8.3 differs from the one in 8.4. > these outputs are not too helpful - send a EXPLAIN ANALYZE result you can use a small aplication for storing plans http://explain.depesz.com/ regards Pavel Stehule > > ************************************* > The beginning of the 8.4: > ************************************* > "Seq Scan on relations (cost=0.00..1502557856.52 rows=332613 width=24)" > " Filter: (((SubPlan 36) OR (SubPlan 37)) AND (SubPlan 38))" > " SubPlan 1" > " -> Index Scan using idx_relation_tags_relation_id on relation_tags > (cost=0.00..8.97 rows=1 width=0)" > " Index Cond: (relation_id = $0)" > " Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))" > " SubPlan 2" > " -> Seq Scan on relation_tags (cost=0.00..102686.97 rows=451600 > width=8)" > " Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))" > " SubPlan 3" > " -> Limit (cost=0.00..8.97 rows=1 width=14)" > " -> Index Scan using idx_relation_tags_relation_id on > relation_tags (cost=0.00..8.97 rows=1 width=14)" > " Index Cond: (relation_id = $0)" > " Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))" > " SubPlan 4" > " -> Index Scan using idx_relation_tags_relation_id on relation_tags > (cost=0.00..8.97 rows=1 width=0)" > " Index Cond: (relation_id = $0)" > " Filter: ((k ~~* 'name:de'::text) AND (v !~~* ''::text))" > " SubPlan 5" > " -> Seq Scan on relation_tags (cost=0.00..102686.97 rows=52 width=8)" > " Filter: ((k ~~* 'name:de'::text) AND (v !~~* ''::text))" > > ************************************* > The beginning of the 8.3 query plan: > ************************************* > "Seq Scan on relations (cost=0.00..491771409147.20 rows=334529 width=24)" > " Filter: ((subplan) AND ((subplan) OR (subplan)))" > " SubPlan" > " -> Seq Scan on relation_tags (cost=0.00..138595.94 rows=170556 > width=0)" > " Filter: ((k ~~* 'boundary'::text) OR ((k ~~* 'type'::text) AND (v > ~~* 'boundary'::text) AND (relation_id = $0)))" > " -> Nested Loop (cost=3960.20..112931.40 rows=1 width=0)" > " -> HashAggregate (cost=3960.20..3960.69 rows=49 width=8)" > " -> Index Scan using idx_relation_members_relation_id on > relation_members (cost=0.00..3959.83 rows=148 width=8)" > " Index Cond: (relation_id = $0)" > " Filter: ((member_role ~~* 'outer'::text) AND > (member_type = 'W'::bpchar))" > " -> Index Scan using idx_way_tags_way_id on way_tags > (cost=0.00..2223.88 rows=1 width=8)" > " Index Cond: (public.way_tags.way_id = > public.relation_members.member_id)" > " Filter: ((public.way_tags.k ~~* 'boundary'::text) OR > ((public.way_tags.k ~~* 'type'::text) AND (public.way_tags.v ~~* > 'boundary'::text)))" > " -> GroupAggregate (cost=0.00..29281.72 rows=1 width=8)" > " Filter: (count(*) = 1)" > " -> Index Scan using idx_relation_members_relation_id on > relation_members (cost=0.00..29281.33 rows=74 width=8)" > " Index Cond: (relation_id = $0)" > " Filter: ((member_role = 'outer'::text) AND (member_type = > 'W'::bpchar) AND (subplan))" > " SubPlan" > " -> Index Scan using idx_ways_id on ways > (cost=0.00..22.75 rows=1 width=0)" > " Index Cond: (id = $2)" > " Filter: (st_isclosed(linestring) AND > (st_numpoints(linestring) > 3))" > " -> Nested Loop (cost=3957.48..5255.55 rows=6 width=978)" > " -> HashAggregate (cost=3957.48..3958.05 rows=57 width=8)" > " -> Index Scan using idx_relation_members_relation_id on > relation_members (cost=0.00..3957.04 rows=173 width=8)" > " Index Cond: (relation_id = $0)" > " Filter: (member_role ~~* 'outer'::text)" > > Jo > > On 14.03.2011 14:15, Hannes Erven wrote: >> >> Jo, >> >> >>> we have performance problems running several queries pon postgres 8.4 . >>> Using the previous version (8.3) our queries performs well >>> (The queries are quite complex, consisting of several sub-queries and >>> various spatial functions). >>> >>> Are there some major changes from 8.3 to 8.4 that cause this bad >>> performance? >> >> Have you checked the release notes? >> http://www.postgresql.org/docs/8.4/static/release-8-4.html >> >> If you can, I'd also suggest that you show the execution plans on >> 8.3/8.4 on the list. >> >> One more thing: did you also check the server configuration (work_mem, >> ...)? >> >> >> -hannes >> > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
> Hello > > 2011/3/14 Jo <jl.news@uni-bonn.de>: >> I set the work_mem to 100MB and the shared buffers are 2 GB >> >> The query plans are long and complex. I send the beginning of the >> two plans. Hope this helps to understand the differences. >> I assume the join strategy in 8.3 differs from the one in 8.4. >> > > these outputs are not too helpful - send a EXPLAIN ANALYZE result > > you can use a small aplication for storing plans > http://explain.depesz.com/ I'm afraid he won't be able to provide EXPLAIN ANALYZE as the second query (on 8.4) does not finish. But providing a full EXPLAIN output is definitely a good idea - there's no way to solve that using just a piece of it. regards Tomas
>> we have performance problems running several queries pon postgres 8.4 .
>> Using the previous version (8.3) our queries performs well
>> (The queries are quite complex, consisting of several sub-queries and
>> various spatial functions).
Two things that frequently bite people during an upgrade:
1. Forgetting to ANALYZE the database after reload. Autovacuum would
probably fix that for you eventually, but it's better to just issue one
manually.
2. Creating the new database with the wrong locale. I see a bunch of
LIKE operators in the query plans you show later, so I'm wondering if
you went from C locale to a non-C locale and that defeated LIKE
optimizations that used to work.
regards, tom lane
Hello,
that's the 8.4 query plan:
http://explain.depesz.com/s/dO7
The locale of the two databases is the same:
SHOW LC_COLLATE command gives the locale: "de_DE.UTF-8".
Regards,
Jo
On 14.03.2011 16:04, tv@fuzzy.cz wrote:
>> Hello
>>
>> 2011/3/14 Jo<jl.news@uni-bonn.de>:
>>> I set the work_mem to 100MB and the shared buffers are 2 GB
>>>
>>> The query plans are long and complex. I send the beginning of the
>>> two plans. Hope this helps to understand the differences.
>>> I assume the join strategy in 8.3 differs from the one in 8.4.
>>>
>>
>> these outputs are not too helpful - send a EXPLAIN ANALYZE result
>>
>> you can use a small aplication for storing plans
>> http://explain.depesz.com/
>
> I'm afraid he won't be able to provide EXPLAIN ANALYZE as the second query
> (on 8.4) does not finish. But providing a full EXPLAIN output is
> definitely a good idea - there's no way to solve that using just a piece
> of it.
>
> regards
> Tomas
>
>
On Mon, Mar 14, 2011 at 9:48 AM, Jo <jl.news@uni-bonn.de> wrote:
> I set the work_mem to 100MB and the shared buffers are 2 GB
>
> The query plans are long and complex. I send the beginning of the
> two plans. Hope this helps to understand the differences.
> I assume the join strategy in 8.3 differs from the one in 8.4.
>
>
> *************************************
> The beginning of the 8.4:
> *************************************
> "Seq Scan on relations (cost=0.00..1502557856.52 rows=332613 width=24)"
> " Filter: (((SubPlan 36) OR (SubPlan 37)) AND (SubPlan 38))"
> " SubPlan 1"
> " -> Index Scan using idx_relation_tags_relation_id on relation_tags
> (cost=0.00..8.97 rows=1 width=0)"
> " Index Cond: (relation_id = $0)"
> " Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))"
> " SubPlan 2"
well, regardless of the version, you're doing a gazillion sequential
scans on relation tags. This looks like the primary culprit (I had to
look up the ~~* operator...it's 'ilike'):
(
(k ~~* 'boundary'::text) OR
(
(k ~~* 'type'::text)
AND (v ~~* 'boundary'::text)
AND (relation_id = $0)
)
)
1. do we really. really need to be using ~~* here? how about '~~' (like) or '='
2. can we see definition and indexes on relation_tags? In particular,
have you considered an index on (k,v,relation_id), or maybe one on
(relation_id, v, k) and one on k?
3. can we see the source query?
merlin
I know the intensity of ilikes but I don't see another way to solve it. But that shouldn't be the problem because the query runs on another server (not as powerful as the actual machine) with postgres 8.3 in acceptable time (same data, same query). Each of the collumns of the relation table has an index on it (relation_id, v and k). I thought about differences in joining strategy between 8.3 and 8.4. Becaus there are some posts in this group about join problems with > 8.4 (but not sure)? As mentioned before the 8.4 query plan differs from the 8.3 query plan (same query). 8.3 query plan: http://explain.depesz.com/s/KdF (no problem) 8.4 query plan: http://explain.depesz.com/s/dO7 (problem query) Jo On 15.03.2011 17:24, Merlin Moncure wrote: > On Mon, Mar 14, 2011 at 9:48 AM, Jo<jl.news@uni-bonn.de> wrote: >> I set the work_mem to 100MB and the shared buffers are 2 GB >> >> The query plans are long and complex. I send the beginning of the >> two plans. Hope this helps to understand the differences. >> I assume the join strategy in 8.3 differs from the one in 8.4. >> >> >> ************************************* >> The beginning of the 8.4: >> ************************************* >> "Seq Scan on relations (cost=0.00..1502557856.52 rows=332613 width=24)" >> " Filter: (((SubPlan 36) OR (SubPlan 37)) AND (SubPlan 38))" >> " SubPlan 1" >> " -> Index Scan using idx_relation_tags_relation_id on relation_tags >> (cost=0.00..8.97 rows=1 width=0)" >> " Index Cond: (relation_id = $0)" >> " Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))" >> " SubPlan 2" > > > well, regardless of the version, you're doing a gazillion sequential > scans on relation tags. This looks like the primary culprit (I had to > look up the ~~* operator...it's 'ilike'): > ( > (k ~~* 'boundary'::text) OR > ( > (k ~~* 'type'::text) > AND (v ~~* 'boundary'::text) > AND (relation_id = $0) > ) > ) > > 1. do we really. really need to be using ~~* here? how about '~~' (like) or '=' > 2. can we see definition and indexes on relation_tags? In particular, > have you considered an index on (k,v,relation_id), or maybe one on > (relation_id, v, k) and one on k? > 3. can we see the source query? > > merlin >
> On 15.03.2011 17:24, Merlin Moncure wrote:
>>
>>
>> well, regardless of the version, you're doing a gazillion sequential
>> scans on relation tags. This looks like the primary culprit (I had to
>> look up the ~~* operator...it's 'ilike'):
>> (
>> (k ~~* 'boundary'::text) OR
>> (
>> (k ~~* 'type'::text)
>> AND (v ~~* 'boundary'::text)
>> AND (relation_id = $0)
>> )
>> )
>>
>> 1. do we really. really need to be using ~~* here? how about '~~' (like)
>> or '='
>> 2. can we see definition and indexes on relation_tags? In particular,
>> have you considered an index on (k,v,relation_id), or maybe one on
>> (relation_id, v, k) and one on k?
>> 3. can we see the source query?
your index can't be used to it's full effect because you are doing
case insensitive matching. if you read the plan, the index is only
matching on relation_id I'm extremely skeptical you really need to
be matching this way -- either:
1. change ilike to like
2. change to ilike like, but add lower(), or upper() around matching
terms as needed: k like lower('boundary') etc
3. adjust your index like this:
create index on relation_tags(relation_id, lower(v), lower(k))
and your matching to this:
lower(k) like lower('type'), lower(v) like lower('boundary'), etc
1 is simplest and preferred if it meets your requirements, 2 next simplest, etc
also an index on just k will probably help. OR, you might see great
benefit from reversing the terms, so your index is on k,v,relation_id.
but fix the insensitive issue first.
It's hard to say for sure, because we don't have a full explain
analyze for either version so we can't know for sure what's going
wrong. 8.4 is generating what it thinks is a better plan, and it's
difficult to see where the problems lies without being able to see
mis-estimates which are often a major contributor to bad plans. that
said: with small changes your query can likely be made to go
*significantly* faster.
I'd also like to see the query.
merlin