Analyze has been run on the database quite frequently during the
course of us trying to figure out this performance issue. It is also
a task that is crontabbed nightly.
On Mon, 7 Mar 2005 09:31:06 -0800, Josh Berkus wrote:
> Tsarevich,
>
> > When running queries we are experiencing much bigger
> > RDTSC is a bad source of information for this kind of thing, as the
> > CPU frequency might vary.
>
> One thought that was bothering me was that if the CPU goes
> idle while waiting for disk I/O, its clock might stop or slow
> down dramatically.
> If we believed such a counter for EXPLAIN,
I posted this on hackers, but I had to post it here.
===
Hi all,
running a 7.4.5 engine, I'm facing this bad plan:
empdb=# explain analyze SELECT
name,url,descr,reques
Gaetano Mendola wrote:
running a 7.4.5 engine, I'm facing this bad plan:
empdb=# explain analyze SELECT
name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
empdb-#FROM v_sc_user_request
empdb-#WHERE
empdb-#
Hi, I have the following strange situation:
oocms=# vacuum full analyze;
VACUUM
oocms=# \df+ class_get_number_of_objects
ÐÐÐÑÐÐ ÑÑÐÐÑÐÐ
ÐÑÐÐÐ | ÐÐÑ | ÐÐÐ ÑÑ
ÑÐÐÑÐÑÑ
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Richard Huxton wrote:
> Gaetano Mendola wrote:
>
>> running a 7.4.5 engine, I'm facing this bad plan:
>>
>> empdb=# explain analyze SELECT
>> name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
>>
>> empdb-#
Markus Bertheau â wrote:
oocms=# explain analyze select count(1) from objects where class = 'Picture';
QUERY PLAN
Aggregate (cost=278
Gaetano Mendola wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Richard Huxton wrote:
Gaetano Mendola wrote:
running a 7.4.5 engine, I'm facing this bad plan:
empdb=# explain analyze SELECT
name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
empdb-#
Markus Bertheau â wrote:
Hi, I have the following strange situation:
...
oocms=# explain analyze select count(1) from objects where class = 'Picture';
QUERY PLAN
Tsarevich,
> Analyze has been run on the database quite frequently during the
> course of us trying to figure out this performance issue. It is also
> a task that is crontabbed nightly.
Hmmm. Then you probably need to up the STATISTICS levels on the target
column, because PG is mis-estimating
Richard Huxton wrote:
> OK, so looking at the original EXPLAIN the order of processing seems to be:
> 1. v_sat_request is evaluated and filtered on login='...' (lines 7..15)
> This gives us 31 rows
> 2. The left-join from v_sat_request to v_sc_packages is processed (lines
> 5..6)
> This involves t
I have two index questions. The first is about an
issue that has been recently discussed,
and I just wanted to be sure of my understanding.
Functions like count(), max(), etc. will
use sequential scans instead of index scans because the
index doesn’t know which rows
are actually visibl
Your hypothesis about index usage of count() and max() is correct.
As for why you see index usage in your first example query and not your
second: compare the number of rows in question. An index is extremely
useful if 19 rows will be returned. But when 62350411 rows will be
returned, you're
Rick Schumeyer wrote:
I have two index questions. The first is about an issue that has been
recently discussed,
and I just wanted to be sure of my understanding. Functions like
count(), max(), etc. will
use sequential scans instead of index scans because the index doesn’t
know which rows
are actual
That makes a lot of sense. Sure enough, if I change the query from
WHERE x > 0 (which return a lot of rows) to
WHERE x > 0 AND x < 1
I now get an index scan.
> As for why you see index usage in your first example query and not your
> second: compare the number of rows in question. An index is e
On Tue, 8 Mar 2005, Rick Schumeyer wrote:
> =# explain select * from data where x = 0;
> -
> Index Scan using data_x_ix on data (cost=0.00..78.25 rows=19 width=34)
>Index Cond: (x = 0::double precision)
>
> But this co
Gaetano Mendola wrote:
Richard Huxton wrote:
OK, so looking at the original EXPLAIN the order of processing seems to be:
1. v_sat_request is evaluated and filtered on login='...' (lines 7..15)
This gives us 31 rows
2. The left-join from v_sat_request to v_sc_packages is processed (lines
5..6)
This
Gaetano Mendola <[EMAIL PROTECTED]> writes:
>> Since your query is so simple, I'm guessing v_sc_user_request is a view.
>> Can you provide the definition?
> Of course:
I don't think you've told us the whole truth about the v_sc_packages
view. The definition as given doesn't work at all (it'll ha
PG Hackers,
What follows is iostat output from a TPC-H test on Solaris 10.The machine
is creating indexes on a table which is 50G in size, so it needs to use
pgsql_tmp for internal swapping:
ttymd15 sd1 sd2 sd3cpu
tin tout kps tps serv k
Tom Lane wrote:
Putting ORDER BYs in views that you intend to use as components of other
views is a bad practice from a performance perspective...
There are also a lot of views involved here for very few output columns.
Tom - is the planner smart enough to optimise-out unneeded columns from
a SEL
Richard Huxton writes:
> There are also a lot of views involved here for very few output columns.
> Tom - is the planner smart enough to optimise-out unneeded columns from
> a SELECT * view if it's part of a join/subquery and you only use one or
> two columns?
If the view gets flattened, yes,
All,
I hope that this is the right place to post. I am relatively new to
PostgreSQL (i.e., < 1 year in coding) and am just starting to
delve into the issues of query optimization. I have hunted around
the web for the basics of query optimization, but I have not had
much success in interpreting th
People:
> As you can see, we're getting a nice 23mb/s peak for WAL (thanks to
> forcedirectio) and database writes peak at 6mb/s. However, pgsql_tmp,
> which is being used heavily, hovers around 1mb/s, and never goes above
> 1.5mb/s. This seems to be throttling the whole system.
Never mind, I'm
H.
> > As you can see, we're getting a nice 23mb/s peak for WAL (thanks to
> > forcedirectio) and database writes peak at 6mb/s. However, pgsql_tmp,
> > which is being used heavily, hovers around 1mb/s, and never goes above
> > 1.5mb/s. This seems to be throttling the whole system.
>
> Ne
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tom Lane wrote:
> Gaetano Mendola <[EMAIL PROTECTED]> writes:
>
>>>Since your query is so simple, I'm guessing v_sc_user_request is a view.
>>>Can you provide the definition?
>
>
>>Of course:
>
>
> I don't think you've told us the whole truth abou
Markus Bertheau â wrote:
> Hi, I have the following strange situation:
that is no so strange. I have an example where:
SELECT * FROM my_view WHERE field1 = 'New'; ==> 800 seconds
SELECT * FROM my_view; ==> 2 seconds
the only solution I had was to write a function table with
the second select i
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all,
this is the third email that I post but I do not see it in archives,
the email was too long I believe so this time I will limit the rows.
Basically I'm noticing that a simple vacuum full is not enough to
shrink completelly the table:
# vacuum
> Maybe I'm not an idiot (really!) even with almost 2GB of maintenance_mem, PG
> still writes to pgsql_tmp no faster than 2MB/s.I think there may be an
> artificial bottleneck there. Question is, PostgreSQL, OS or hardware?
I'm curious: what is your cpu usage while this is happening? I've
n
Gaetano Mendola wrote:
Markus Bertheau â wrote:
Hi, I have the following strange situation:
that is no so strange. I have an example where:
SELECT * FROM my_view WHERE field1 = 'New'; ==> 800 seconds
SELECT * FROM my_view; ==> 2 seconds
the only solution I had was to write a function table
James G Wilkinson wrote:
All,
...
Firstly, I am frankly mystified on how to interpret all this. If anyone
could point me to a document or two that will help me decipher this,
I will greatly appreciate it.
I assume you have looked at:
http://www.postgresql.org/docs/8.0/static/performance-tips.html
On Wed, Mar 09, 2005 at 02:02:13AM +0100, Gaetano Mendola wrote:
> Basically I'm noticing that a simple vacuum full is not enough to
> shrink completelly the table:
>
> # vacuum full verbose url;
> INFO: vacuuming "public.url"
> INFO: "url": found 268392 removable, 21286 nonremovable row versio
Hi folks,
I'm about to start testing PGv8 on an Opteron 64bit box with 12GB Ram
running RedHat.
A bunch of raided drives in the backend.
Expecting 50GB of data per month (100GB+ initial load).
I do not see any example config settings. Have some MySql experience
and and for it there are config se
On Tue, 2005-03-08 at 19:07 -0800, David B wrote:
> I do not see any example config settings. Have some MySql experience
> and and for it there are config settings for small or large server
> operations.
For starters, this might be helpful:
http://www.powerpostgresql.com/PerfList
Then this:
http
On Tue, Mar 08, 2005 at 13:35:53 -0500,
Rick Schumeyer <[EMAIL PROTECTED]> wrote:
> I have two index questions. The first is about an issue that has been
> recently discussed,
>
> and I just wanted to be sure of my understanding. Functions like count(),
> max(), etc. will
>
> use sequential s
Josh Berkus writes:
> Maybe I'm not an idiot (really!) even with almost 2GB of maintenance_mem, PG
> still writes to pgsql_tmp no faster than 2MB/s.I think there may be an
> artificial bottleneck there. Question is, PostgreSQL, OS or hardware?
AFAIR that's just fwrite() ...
Tom,
> > Maybe I'm not an idiot (really!) even with almost 2GB of
> > maintenance_mem, PG still writes to pgsql_tmp no faster than 2MB/s.I
> > think there may be an artificial bottleneck there. Question is,
> > PostgreSQL, OS or hardware?
>
> AFAIR that's just fwrite() ...
Well, are there a
Josh Berkus writes:
>> AFAIR that's just fwrite() ...
> Well, are there any hacks to speed it up? It's about doubling the amount of
> time it takes to create an index on a very large table.
Huh? Doubled compared to what?
regards, tom lane
---(
On Tue, Mar 08, 2005 at 10:38:21PM -0600, Bruno Wolff III wrote:
> Not exactly. If the number of rows to be examined is on the order of 5%
> of the table, an index scan will probably be slower than a sequential
> scan. The visibility issue makes index scans slower in the case that
Shouldn't that b
On Tue, Mar 08, 2005 at 22:55:19 -0600,
"Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> On Tue, Mar 08, 2005 at 10:38:21PM -0600, Bruno Wolff III wrote:
> > Not exactly. If the number of rows to be examined is on the order of 5%
> > of the table, an index scan will probably be slower than a sequentia
Tom,
> Huh? Doubled compared to what?
Compared to how much data writing I can do to the database when pgsql_tmp
isn't engaged.
In other words, when pgsql_tmp isn't being written, database writing is 9mb/s.
When pgsql_tmp gets engaged, that drops to 4mb/s.
Alternatively, the WAL drive, wh
40 matches
Mail list logo
