[PERFORM] Re: autovacuum vacuum creates bad statistics for planner when it log index scans: 0

[tim_wilson]

Yes I can create a simpler version that exhibits the problem:
postgres_bug_simpler.sql

  

This only now involves one smaller table 60K rows, and a linked table with
20M rows. I tried with 6K and 1M but could not get problem to occur. Both
are now unchanging in size. The smaller table gets updated frequently, and
then starts exhibiting the bad query plan, it seems especially after the 2nd
auto vacuum and auto analyze. When the dead_rows goes to zero in the stats
the live_tup can stay at an huge factor larger than the table really is for
some time.

In my system the smaller table that is updated frequently grows only
slightly if at all. I never want a table scan to happen of the big table,
but even with enable_seq_scan=false set in functions that query these tables
I can get the bad query plan.

Would it be possible to have a setting on a table that gave an expression
for determining the table size? IE For key highly updated tables I could set
and maintain the meta-data for the size table and even shape of the data.
Then for these tables autovac would not need to make the effort of having to
estimate size.

regards
Tim




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/autovacuum-vacuum-creates-bad-statistics-for-planner-when-it-log-index-scans-0-tp5804416p5806320.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] CPU load spikes when CentOS tries to reclaim 'cached' memory

[Merlin Moncure]

On Thu, Jun 5, 2014 at 6:57 PM, Vincent Lasmarias
 wrote:
> Thanks for the informative responses and suggestions. My responses below:
>
> * Sorry for the double post. I posted the original message using my gmail
> account and got a "is not a member of any of the restrict_post groups"
> response and when I didn't see it for a day, I ended up wondering if it was
> due to my use of a gmail account - so I tried using my company email account
> instead to post an updated version of the original post.
>
> * Our system is not virtualized.
>
> * Jeff, the output format of the load and free/cached memory did not come
> from a tool but came from my script. My script does OEuptime; free ­m¹, and
> then another script massages the data to only grab date, 1-minute load
> average, free, and cached.
>
> * For the 'top' outputs, I don't have the actual 'top' output, but I have
> the following:

All right; top is pretty clearly indicating a problem in the kernel.
Maybe this is numa or something else.  If you can reproduce this on
the 'newer' kernel, it might be useful to try and get a 'perf' going;
it was introduced in 2.6.31.  This might fail for one reason or
another though but if you could get it rigged it might give some clues
as to where in the kernel you're getting bound up.

Long term, looking into upgrading the O/S might be a smart move.

merlin


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM]

[Scott Marlowe]

Well it's me again, with another performance regression. We have this query:

SELECT *
FROM users u
WHERE (u.user_group_id IN
(SELECT ug.id
  FROM user_groups ug, pro_partners p
  WHERE ug.pro_partner_id = p.id
  AND p.tree_sortkey BETWEEN
E'00010101010001010001100101101010'
AND
tree_right(E'00010101010001010001100101101010')
OFFSET 0)
AND u.deleted_time IS NULL)
ORDER BY u.id LIMIT 1000;

OK so on 8.4.2 it runs fast. If I take out the offset 0 it runs slow.

If I run this on 8.4.15. 8.4.19 or 8.4.21 it also runs slow.

If I drop the limit 1000 it runs fast again. Query plans:

8.4.2 with offset 0: http://explain.depesz.com/s/b3G
8.4.2 without offset 0: http://explain.depesz.com/s/UFAl
8.4.2 without offset 0 and with no limit: http://explain.depesz.com/s/krdf
8.4.21 with or without offset 0 and no limit: http://explain.depesz.com/s/9m1
8.4.21 with limit: http://explain.depesz.com/s/x2G

A couple of points: The with limit on 8.4.21 never returns. It runs
for hours and we just have to kill it. 8.4.2 without the offset and
with a limit never returns. Tables are analyzed, data sets are the
same (slony replication cluster) and I've tried cranking up stats
target to 1000 with no help.

tree_sortkey is defined here: http://rubick.com/openacs/tree_sortkey
but I don't think it's the neus of the problem, it looks like join
estimations are way off here.

-- 
To understand recursion, one must first understand recursion.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM]

[Tom Lane]

Scott Marlowe  writes:
> Well it's me again, with another performance regression. We have this query:
> SELECT *
> FROM users u
> WHERE (u.user_group_id IN
> (SELECT ug.id
>   FROM user_groups ug, pro_partners p
>   WHERE ug.pro_partner_id = p.id
>   AND p.tree_sortkey BETWEEN
> E'00010101010001010001100101101010'
> AND
> tree_right(E'00010101010001010001100101101010')
> OFFSET 0)
> AND u.deleted_time IS NULL)
> ORDER BY u.id LIMIT 1000;

> OK so on 8.4.2 it runs fast. If I take out the offset 0 it runs slow.
> If I run this on 8.4.15. 8.4.19 or 8.4.21 it also runs slow.

This seems to be about misestimation of the number of rows out of a
semijoin, so I'm thinking that the reason for the behavior change is
commit 899d7b00e9 or 46f775144e.  It's unfortunate that your example
ends up on the wrong side of that change, but the original 8.4.x behavior
was definitely pretty bogus; I think it's only accidental that 8.4.2
manages to choose a better plan.  (The fact that you need the crutch
of the "OFFSET 0" to get it to do so is evidence that it doesn't
really know what its doing ;-).)

One thing you might try is back-patching commit 4c2777d0b733, as I
suspect that you're partially getting burnt by that in this scenario.
I was afraid to back-patch that because of the API change possibly
breaking third-party code, but in a private build that's unlikely
to be an issue.

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM]

[Scott Marlowe]

Thanks we'll give that a try.

On Fri, Jun 6, 2014 at 7:38 PM, Tom Lane  wrote:
> Scott Marlowe  writes:
>> Well it's me again, with another performance regression. We have this query:
>> SELECT *
>> FROM users u
>> WHERE (u.user_group_id IN
>> (SELECT ug.id
>>   FROM user_groups ug, pro_partners p
>>   WHERE ug.pro_partner_id = p.id
>>   AND p.tree_sortkey BETWEEN
>> E'00010101010001010001100101101010'
>> AND
>> tree_right(E'00010101010001010001100101101010')
>> OFFSET 0)
>> AND u.deleted_time IS NULL)
>> ORDER BY u.id LIMIT 1000;
>
>> OK so on 8.4.2 it runs fast. If I take out the offset 0 it runs slow.
>> If I run this on 8.4.15. 8.4.19 or 8.4.21 it also runs slow.
>
> This seems to be about misestimation of the number of rows out of a
> semijoin, so I'm thinking that the reason for the behavior change is
> commit 899d7b00e9 or 46f775144e.  It's unfortunate that your example
> ends up on the wrong side of that change, but the original 8.4.x behavior
> was definitely pretty bogus; I think it's only accidental that 8.4.2
> manages to choose a better plan.  (The fact that you need the crutch
> of the "OFFSET 0" to get it to do so is evidence that it doesn't
> really know what its doing ;-).)
>
> One thing you might try is back-patching commit 4c2777d0b733, as I
> suspect that you're partially getting burnt by that in this scenario.
> I was afraid to back-patch that because of the API change possibly
> breaking third-party code, but in a private build that's unlikely
> to be an issue.
>
> regards, tom lane



-- 
To understand recursion, one must first understand recursion.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance