Hello.
I have a query that performs very poor because there is a limit on join
column that is not applied to other columns:
select * from company this_ left outer join company_tag this_1_ on
this_.id=this_1_.company_id left outer join company_measures companymea2_ on
Віталій Тимчишин wrote:
Hello.
I have a query that performs very poor because there is a limit on
join column that is not applied to other columns:
select * from company this_ left outer join company_tag this_1_ on
this_.id=this_1_.company_id left outer join company_measures
companymea2_
16 квітня 2010 р. 11:31 Yeb Havinga yebhavi...@gmail.com написав:
Віталій Тимчишин wrote:
Hello.
I have a query that performs very poor because there is a limit on join
column that is not applied to other columns:
select * from company this_ left outer join company_tag this_1_ on
16 квітня 2010 р. 11:25 Hannu Krosing ha...@2ndquadrant.com написав:
On Fri, 2010-04-16 at 11:02 +0300, Віталій Тимчишин wrote:
Hello.
I have a query that performs very poor because there is a limit on
join column that is not applied to other columns:
select * from company this_
Віталій Тимчишин wrote:
BTW: Changing slow query to inner joins do not make it fast
I'm interested to see the query andplan of the slow query with inner joins.
Another thing is it seems that the number of rows guessed is far
off from the actual number of rows, is the number 500
16 квітня 2010 р. 16:21 Yeb Havinga yebhavi...@gmail.com написав:
Віталій Тимчишин wrote:
BTW: Changing slow query to inner joins do not make it fast
I'm interested to see the query andplan of the slow query with inner joins.
Here you are. The query:
select * from company this_ inner
=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= tiv...@gmail.com writes:
I've thought and someone in this list've told me that this should be done
automatically.
No, that's not true. We do make deductions about transitive equalities,
ie, given WHERE a=b AND b=c the planner will infer a=c and use that if
On Thu, Apr 15, 2010 at 6:31 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Chris li...@deksai.com writes:
I have a lot of centos servers which are running postgres. Postgres isn't
used
that heavily on any of them, but lately, the stats collector process keeps
causing tons of IO load. It seems to
Josh Kupershmidt schmi...@gmail.com writes:
I'm not sure whether this is related to the stats collector problems
on this machine, but I noticed alarming table bloat in the catalog
tables pg_attribute, pg_attrdef, pg_depend, and pg_type.
Hmm. That makes me wonder if autovacuum is functioning
Chris li...@deksai.com writes:
After the file was made larger and I stopped the vacuum process, I started
seeing the problem. All other postgress processes were quiet, but the stats
collector was constantly causing anywhere from 20-60 of the IO on the server.
Since all the other postgres
On Fri, Apr 16, 2010 at 11:23 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Josh Kupershmidt schmi...@gmail.com writes:
I'm not sure whether this is related to the stats collector problems
on this machine, but I noticed alarming table bloat in the catalog
tables pg_attribute, pg_attrdef, pg_depend,
Tom Lane wrote:
=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= tiv...@gmail.com writes:
I've thought and someone in this list've told me that this should be done
automatically.
No, that's not true. We do make deductions about transitive equalities,
ie, given WHERE a=b AND b=c the planner will
Josh Kupershmidt schmi...@gmail.com writes:
On Fri, Apr 16, 2010 at 11:23 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Hmm. That makes me wonder if autovacuum is functioning properly at all.
What does pg_stat_all_tables show for the last vacuum and analyze times
of those tables? Try something like
Yeb Havinga yebhavi...@gmail.com writes:
New expensive planner infrastructure to support from ab and bc infer
ac, yes.
But I wonder if something like Leibniz's principle of identity holds for
members of the same equivalence class, e.g. like if x,y are both members
of the same EC, then for
Josh Kupershmidt schmi...@gmail.com writes:
I made a small half-empty table like this:
CREATE TABLE test_vacuum (i int PRIMARY KEY);
INSERT INTO test_vacuum (i) SELECT a FROM generate_series(1,50) AS a;
DELETE FROM test_vacuum WHERE RANDOM() 0.5;
and then ran:
VACUUM
On Fri, Apr 16, 2010 at 11:41 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Wow. Well, we have a smoking gun here: for some reason, autovacuum
isn't running, or isn't doing its job if it is. If it's not running
at all, that would explain failure to prune the stats collector's file
too.
Hrm, well
Tom,
Neither database has and per-table autovacuum settings.
However, since this is a production database, I had to try something,
and set vacuum_cost_limit up to 1000. The issue with vacuuming one page
at a time went away, or at least I have not seen it repeat in the last
16 hours.
--
I wrote:
So this *should* have resulted in the stats file shrinking. Did you
happen to notice if it did, after you did this?
Oh, never mind that --- I can see that it did shrink, just from counting
the write() calls in the collector's strace. So what we have here is a
demonstration that the
Josh Kupershmidt schmi...@gmail.com writes:
On Fri, Apr 16, 2010 at 11:41 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Wow. Well, we have a smoking gun here: for some reason, autovacuum
isn't running, or isn't doing its job if it is. If it's not running
at all, that would explain failure to prune
Josh Berkus wrote:
Tom,
Neither database has and per-table autovacuum settings.
However, since this is a production database, I had to try
something, and set vacuum_cost_limit up to 1000. The issue with
vacuuming one page at a time went away, or at least I have not seen
it repeat in the
How many autovac workers are there?
Max_workers is set to 3. However, I've never seen more than one active
at a time.
--
-- Josh Berkus
PostgreSQL Experts Inc.
On Apr 16, 2010, at 9:48 AM, Tom Lane wrote:
Josh Kupershmidt schmi...@gmail.com writes:
On Fri, Apr 16, 2010 at 11:41 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Wow. Well, we have a smoking gun here: for some reason, autovacuum
isn't running, or isn't doing its job if it is. If it's not
I have had some 'idle in transaction' connections hanging out from time to
time that have caused issues on this machine that could explain the above
perma-bloat. That is one thing that could affect the case reported here as
well. The worst thing about those, is you can't even force kill
On Fri, Apr 16, 2010 at 12:48 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Josh Kupershmidt schmi...@gmail.com writes:
Hrm, well autovacuum is at least trying to do work: it's currently
stuck on those bloated pg_catalog tables, of course. Another developer
killed an autovacuum of pg_attribute (or
Josh Kupershmidt wrote:
SELECT name, current_setting(name), source FROM pg_settings WHERE
source != 'default' AND name ILIKE '%vacuum%';
name | current_setting | source
--+-+
vacuum_cost_delay| 200ms |
On Fri, Apr 16, 2010 at 2:14 PM, Greg Smith g...@2ndquadrant.com wrote:
Josh Kupershmidt wrote:
SELECT name, current_setting(name), source FROM pg_settings WHERE
source != 'default' AND name ILIKE '%vacuum%';
name | current_setting | source
Josh Kupershmidt wrote:
And it seems the only way
to compact the pg_catalog tables is VACUUM FULL + REINDEX on 8.3 -- I
had tried the CLUSTER on my 9.0 machine and wrongly assumed it would
work on 8.3, too.
Right; that just got implemented a couple of months ago. See the news
from
Josh Kupershmidt schmi...@gmail.com writes:
name | current_setting | source
--+-+
vacuum_cost_delay| 200ms | configuration file
vacuum_cost_limit| 100 | configuration file
On Fri, Apr 16, 2010 at 3:22 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Josh Kupershmidt schmi...@gmail.com writes:
name | current_setting | source
--+-+
vacuum_cost_delay | 200ms | configuration file
29 matches
Mail list logo
