On Mon, 7 Mar 2005, Michael McFarland wrote:
>I'm trying to understand why a particular query is slow, and it seems
> like the optimizer is choosing a strange plan. See this summary:
>
>
> * I have a large table, with an index on the primary key 'id' and on a
> field 'foo'.
> > select count(*
Michael McFarland wrote:
I'm trying to understand why a particular query is slow, and it
seems like the optimizer is choosing a strange plan. See this summary:
...
explain select * from foo where barId = 412 order by id desc limit 25;
Limit ()
-> Index scan backward using primarykey_index
Tom Lane wrote:
John A Meinel <[EMAIL PROTECTED]> writes:
Dave Held wrote:
There is always clock().
My experience with clock() on win32 is that CLOCKS_PER_SEC was 1000, and
it had a resolution of 55clocks / s. When I just did this:
The other problem is it measures process CP
Hi,
I have performance problems with the
postmaster-process. The CPU usage is permanently
around 90 %.
My DB:
3,5 GB Size
largest table ca. 700 000
rows
Most access:
Finde a row - Update if exist
Insert else
Average access:
10 times a second
every Query ne
The following query takes approx. 3-5+ minutes
to complete. I would like to get this down to around
2-3 seconds. Other RDBMS complete it in <1 second.
I am running 8.0.1 on XP P4 2.6 1GB for dev work.
select i.internalid, c.code
from local.internal i
inner join country.ip c on
(i.ip between c.st
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf
> Of Tom Lane
> Sent: 07 March 2005 22:57
> To: John A Meinel
> Cc: Dave Held; pgsql-performance@postgresql.org;
> [EMAIL PROTECTED]
> Subject: Re: [pgsql-hackers-win32] [PERFORM] Help with tuning
>
I'm trying to understand why a particular query is slow, and it seems
like the optimizer is choosing a strange plan. See this summary:
* I have a large table, with an index on the primary key 'id' and on a
field 'foo'.
select count(*) from foo;
1,000,000
select count(*) from foo where bar =
From the Linux Kernel (make menuconfig) there seem to be two new reliable
sources for timing information. Note the remark about "Time Stamp Counter"
below. Question is, which one of these (or others) are your API functions
using ? I have absolutely no idea !
CONFIG_HPET_TIMER:
On Mon, Mar 07, 2005 at 09:02:38PM -0500, Tom Lane wrote:
> 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, we'd severely understate
> the cost of disk I/O.
>
"Steinar H. Gunderson" <[EMAIL PROTECTED]> writes:
> 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 bel
On Mon, Mar 07, 2005 at 06:11:34PM -0600, Dave Held wrote:
>> In which case using it would be a mistake. Since rtdsc doesn't
>> work across processors.
> It doesn't always use RDTSC. I can't find anything authoritative on
> when it does. I would assume that it would use RDTSC when available
> and
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 07, 2005 4:57 PM
> To: John A Meinel
> Cc: Dave Held; pgsql-performance@postgresql.org;
> [EMAIL PROTECTED]
> Subject: Re: [pgsql-hackers-win32] [PERFORM] Help with tuning
> this query
> (with
>
> John A
> -Original Message-
> From: Greg Stark [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 07, 2005 5:15 PM
> To: Dave Held
> Cc: Greg Stark; John A Meinel; Tom Lane; Magnus Hagander; Ken
> Egervari;
> pgsql-performance@postgresql.org; [EMAIL PROTECTED]
> Subject: Re: [pgsql-hackers-win32] [P
"Dave Held" <[EMAIL PROTECTED]> writes:
> > What would be really neato would be to use the rtdsc (sp?) or
> > equivalent assembly instruction where available. Most processors
> > provide such a thing and it would give much lower overhead and much
> > more accurate answers.
> >
> > The main prob
John A Meinel <[EMAIL PROTECTED]> writes:
> Dave Held wrote:
>> There is always clock().
> My experience with clock() on win32 is that CLOCKS_PER_SEC was 1000, and
> it had a resolution of 55clocks / s. When I just did this:
The other problem is it measures process CPU time, not elapsed time
whi
Dave Held wrote:
There is always clock(). It's mandated by ANSI C, but my docs say
that POSIX requires CLOCKS_PER_SEC == 100 regardless of actual
timer resolution, which seems a little brain-dead to me.
__
David B. Held
My experience with clock() on win32 is that CLOCKS_PER_SEC was 1000, an
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 07, 2005 10:39 AM
> To: John A Meinel
> Cc: Magnus Hagander; Ken Egervari; pgsql-performance@postgresql.org;
> [EMAIL PROTECTED]
> Subject: Re: [pgsql-hackers-win32] [PERFORM] Help with tuning
> this quer
> -Original Message-
> From: Greg Stark [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 07, 2005 12:06 PM
> To: John A Meinel
> Cc: Tom Lane; Magnus Hagander; Ken Egervari;
> pgsql-performance@postgresql.org; [EMAIL PROTECTED]
> Subject: Re: [pgsql-hackers-win32] [PERFORM] Help with tuning
Hugo Ferreira <[EMAIL PROTECTED]> writes:
> SELECT 514, 1168, C.contxt_id, C.contxt_elmt_ix, null, null,
> null, null, null, null, 1
> FROM CONTXT as P INNER JOIN CONTXT_ELMT as C on P.contxt_id = C.contxt_id
>INNER JOIN MRS_REPLICATION_OUT as S on S.ent_id=1029
>
John A Meinel <[EMAIL PROTECTED]> writes:
> Then we would only be wrong for 256 gettimeofday calls. I agree it isn't
> great, though. And probably better to just abstract (possibly just with
> #ifdef) the calls for accurate timing, from the calls that actually need
> the real time.
What would be
I'm sorry for my unpolite query alignment. Here is the query in a more
human-readable format:
SELECT 514, 1168, C.contxt_id, C.contxt_elmt_ix, null, null,
null, null, null, null, 1
FROM CONTXT as P INNER JOIN CONTXT_ELMT as C on P.contxt_id = C.contxt_id
INNER JOIN MRS_REPLICATION_OUT
Tsarevich,
> When running queries we are experiencing much bigger result times than
> anticipated.
>
> Attached is a copy of our postgresql.conf file and of our the table
> definitions and row counts.
Looks like you haven't run ANALYZE on the database anytime recently. Try that
and re-run.
--
Hugo,
> insert into MRS_REPLICATION_OUT select 514, 1168, C.contxt_id,
> C.contxt_elmt_ix, CAST(null as NUMERIC(18)), CAST(null as
> NUMERIC(18)), CAST(null as NUMERIC(18)), CAST(null as NUMERIC(18)),
> CAST(null as NUMERIC(18)), null, 1 from c2iedm.CONTXT as P inner join
> c2iedm.CONTXT_ELMT
Tom Lane wrote:
John A Meinel <[EMAIL PROTECTED]> writes:
Can we just replace gettimeofday() with a version that's basically:
No, because it's also used for actual time-of-day calls. It'd be
necessary to hack executor/instrument.c in particular.
Or we modify the win32 gettimeofday call to som
John A Meinel <[EMAIL PROTECTED]> writes:
>>> Can we just replace gettimeofday() with a version that's basically:
>>
>> No, because it's also used for actual time-of-day calls. It'd be
>> necessary to hack executor/instrument.c in particular.
> Or we modify the win32 gettimeofday call to somethi
Hi there :-)
I'm really, really having trouble with this query... It is a part of,
hmmm... 200 similar querys that I dinyamically build and run in a
stored procedure. This one, for example, takes 27seconds to run. The
whole stored procedure executes in about 15minutes. This is too much
when compar
Tom Lane wrote:
"Magnus Hagander" <[EMAIL PROTECTED]> writes:
There is. I beleive QueryPerformanceCounter has sub-mirosecond
resolution.
Can we just replace gettimeofday() with a version that's basically:
No, because it's also used for actual time-of-day calls. It'd be
necessary to hack executor
Env: Sun E4500 with 8 gig of RAM in total. Database is stored
locally (not on a network storage devise). A copy of the
postgresql.conf file is attached.
When running queries we are experiencing much bigger result times than
anticipated.
Attached is a copy of our postgresql.conf file and of our
"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> There is. I beleive QueryPerformanceCounter has sub-mirosecond
> resolution.
> Can we just replace gettimeofday() with a version that's basically:
No, because it's also used for actual time-of-day calls. It'd be
necessary to hack executor/instrumen
> > Do we need actual high precision time, or do we just need
> to be able
> > to get high precision differences? Getting the differences
> is fairly
> > easy, but if you need to "sync up" any drif then it becomes
> a bit more
> > difficult.
>
> You're right, we only care about differences n
"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> Do we need actual high precision time, or do we just need to be able to
> get high precision differences? Getting the differences is fairly easy,
> but if you need to "sync up" any drif then it becomes a bit more
> difficult.
You're right, we only ca
> >> What platform is this on? It seems very strange/fishy
> that all the
> >> actual-time values are exact integral milliseconds.
>
> > My machine is WinXP professional, athon xp 2100, but I get similar
> > results on my Intel P4 3.0Ghz as well (which is also
> running WinXP).
> > Why do y
32 matches
Mail list logo
