Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
> >> 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 you ask? > > Well, what it suggests is that gettimeofday() is only > returning a result good to the nearest millisecond. (Win32 > hackers, does that sound right?) Yes. The gettimeofday() implementation (in src/backend/port/gettimeofday.c). Actually, in reality you don't even get millisecond resolution it seems (after some reading up). More along the line of 10-millisecond-resolution. See for example http://msdn.microsoft.com/msdnmag/issues/04/03/HighResolutionTimer/. > Most modern machines seem to have clocks that can count > elapsed time down to near the microsecond level. Anyone know > if it's possible to get such numbers out of Windows, or are > we stuck with milliseconds? There are, see link above. But it's definitly not easy. I don't think we can just take the complete code from their exmaple (due to licensing). We could go with the "middle way", but it has a couple of pitfalls. 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. //Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
"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 care about differences not absolute time. If there's something like a microseconds-since-bootup counter, it would be fine. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
> > 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 not absolute > time. If there's something like a microseconds-since-bootup > counter, it would be fine. There is. I beleive QueryPerformanceCounter has sub-mirosecond resolution. Can we just replace gettimeofday() with a version that's basically: if (never_run_before) GetSystemTime() and get current timer for baseline. now = baseline + current timer - baseline timer; return now; Or do we need to make changes at the points where the function is actually called? //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
"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/instrument.c in particular. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Tuning, configuration for 7.3.5 on a Sun E4500
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 the table
definitions and row counts.
Below is an example of SQL and the explain plans.
Any help/pointers/tips/etc. for getting this speed up would be great!!
Cheers
SELECT C.component_id, I.cli,
BL.ncos_value, BL.description,
SG.switch_group_code, SG.servcom_name,
S.description AS status,
RC.description AS process_status,
OT.description AS order_type,
P.party_name,
RDCR.consumer_ref AS consumer_ref,
C.raised_dtm AS created_dtm,
(SELECT dtm FROM orders.communication WHERE
component_id = C.component_id ORDER BY dtm DESC LIMIT 1) AS status_dtm
FROM (SELECT * FROM parties.party WHERE
party_id = 143 AND is_active = true) P
JOIN orders.commercial_order CO ON
CO.party_id = P.party_id
JOIN (SELECT raised_dtm, component_id,
last_supplier_status, component_type_id, current_status_id_fr,
commercial_order_id FROM orders.component WHERE raised_dtm BETWEEN
'2003-01-01 00:00:00'::timestamp AND '2005-01-01 23:59:59'::timestamp
AND component_type_id IN (3, 2, 1)) C ON C.commercial_order_id =
CO.commercial_order_id
JOIN (SELECT * FROM orders.ida WHERE cli IS
NOT NULL ) I ON C.component_id = I.component_id
--Get the consumer reference if there is one
LEFT JOIN parties.consumer_ref RDCR ON
CO.consumer_ref = RDCR.consumer_ref_id
--May or may not have barring level or ncos
dependant on the order type
LEFT JOIN line_configs.ida_barring_level BL
ON I.ida_barring_level_id = BL.ida_barring_level_id
LEFT JOIN line_configs.switch_group SG ON
I.switchgroup_id = SG.switch_group_id
--Get the order type
JOIN business_rules.component_type CT ON
C.component_type_id = CT.component_type_id
JOIN business_rules.order_type OT ON
OT.order_type_id = CT.order_type_id
--Get the status
LEFT JOIN orders.status S ON S.status_id =
C.current_status_id_fr
--Get the process status
LEFT JOIN orders.response_code RC ON
RC.response_code_id = C.last_supplier_status
QUERY PLAN
-
Hash Join (cost=18.02..16067.46 rows=1158 width=277) (actual
time=639100.57..957020.42 rows=34638 loops=1)
Hash Cond: ("outer".last_supplier_status = "inner".response_code_id)
-> Hash Join (cost=9.29..16038.49 rows=1158 width=218) (actual
time=639084.27..937250.67 rows=34638 loops=1)
Hash Cond: ("outer".current_status_id_fr = "inner".status_id)
-> Hash Join (cost=8.17..16017.14 rows=1158 width=197)
(actual time=639083.19..931508.95 rows=34638 loops=1)
Hash Cond: ("outer".order_type_id = "inner".order_type_id)
-> Hash Join (cost=6.99..15995.69 rows=1158
width=180) (actual time=639082.01..926146.92 rows=34638 loops=1)
Hash Cond: ("outer".component_type_id =
"inner".component_type_id)
-> Hash Join (cost=5.47..15973.91 rows=1158
width=172) (actual time=639080.29..921574.75 rows=34638 loops=1)
Hash Cond: ("outer".switchgroup_id =
"inner".switch_group_id)
-> Hash Join (cost=1.49..15949.66
rows=1158 width=147) (actual time=639074.90..917437.55 rows=34638
loops=1)
Hash Cond:
("outer".ida_barring_level_id = "inner".ida_barring_level_id)
-> Merge Join (cost=0.00..15927.90
rows=1158 width=112) (actual time=639073.24..914042.15 rows=34638
loops=1)
Merge Cond:
("outer".consumer_ref = "inner".consumer_ref_id)
-> Nested Loop
(cost=0.00..2630554.06 rows=1158 width=91) (actual
time=639072.57..909395.62 rows=34638 loops=1)
-> Nested Loop
(cost=0.00..2626789.68 rows=1244 width=66) (actual
time=639053.64..902100.16 rows=34638
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
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/instrument.c in particular.
regards, tom lane
It seems that there are 2 possibilities. Leave gettimeofday as it is,
and then change code that calls it for deltas with a
"pg_get_high_res_delta_time()", which on most platforms is just
gettimeofday, but on win32 is a wrapper for QueryPerformanceCounter().
Or we modify the win32 gettimeofday call to something like:
gettimeofday(struct timeval *tv, struct timezone *tz)
{
static int initialized = 0;
static LARGE_INTEGER freq = {0};
static LARGE_INTEGER base = {0};
static struct time_t base_tm = {0};
LARGE_INTEGER now = {0};
int64_t delta_secs = 0;
if(!initialized) {
QueryPerformanceFrequency(&freq);
base_tm = time(NULL); // This can be any moderately accurate time
function, maybe getlocaltime if it exists
QueryPerformanceCounter(&base);
}
QueryPerformanceCounter(&now);
delta_secs = now.QuadPart - base.QuadPart;
tv->tv_sec = delta_secs / freq.QuadPart;
delta_secs -= *tv.tv_sec * freq.QuadPart;
tv->tv_usec = delta_secs * 100 / freq.QuadPart
tv->tv_sec += base_tm;
return 0;
}
signature.asc
Description: OpenPGP digital signature
[PERFORM] Help trying to tune query that executes 40x slower than in SqlServer
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 compared to the exact same database, with the same indexes and
same data running under SqlServer 2000, which takes 21seconds to run
the whole batch.
Any help would be extremely appreciated. I've also tried to tune up
the configuration
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 as C on (P.contxt_id=C.contxt_id) inner join
MRS_REPLICATION_OUT as S on S.ent_id=1029 and (CAST(P.contxt_id AS
numeric(18)) = S.pk1) inner join MRS_TRANSACTION TRANS on
TRANS.trans_id=514 left join NON_REPL_DATA_OWNER NRDO on
NRDO.non_repl_data_owner_id=C.owner_id left join REPL_DATA_OWNER_RSDNC
RDOR on RDOR.owner_id=C.owner_id and
RDOR.rsdnc_node_id=TRANS.recv_node_id left join MRS_REPLICATION_OUT
OUT on OUT.trans_id=514 and OUT.ent_id=1168 and ((CAST(C.contxt_id
AS numeric(18)) = OUT.pk1 AND CAST(C.contxt_elmt_ix AS numeric(18)) =
OUT.pk2)) inner join MRS_TRANSACTION RED_TRANS on
TRANS.prov_node_id=RED_TRANS.prov_node_id and
TRANS.recv_node_id=RED_TRANS.recv_node_id left join
MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT' and
RED_TRANS.trans_type in ('X01', 'X02') and
RED_TRANS.trans_id=RED_OUT.trans_id where S.age=0 and S.trans_id=514
and (NRDO.non_repl_data_owner_id is null) AND (RDOR.repl_data_owner_id
is null) AND (OUT.trans_id is null) AND (RED_OUT.trans_id is null);
This kind of inserts generate few rows. Between 8k and 15k for this particular
insert, and about 20k for the whole batch. If I try to run a batch
to generate about 50k rows, then I'll be stuck here for more that 45h.
Compare this to 12minutes when running SqlServer 2000.
Here is the result of explain analyze:
"Merge Left Join (cost=1338.32..1377.99 rows=45 width=32) (actual
time=719.000..26437.000 rows=14862 loops=1)"
" Merge Cond: ("outer".trans_id = "inner".trans_id)"
" Join Filter: (("outer".cat_code = 'OUT'::bpchar) AND
(("outer".trans_type = 'X01'::bpchar) OR ("outer".trans_type =
'X02'::bpchar)))"
" Filter: ("inner".trans_id IS NULL)"
" -> Sort (cost=1067.36..1067.47 rows=45 width=56) (actual
time=719.000..735.000 rows=14862 loops=1)"
"Sort Key: red_trans.trans_id"
"-> Merge Join (cost=851.66..1066.12 rows=45 width=56)
(actual time=407.000..673.000 rows=14862 loops=1)"
" Merge Cond: ("outer".recv_node_id = "inner".recv_node_id)"
" Join Filter: ("outer".prov_node_id = "inner".prov_node_id)"
" -> Nested Loop Left Join (cost=847.14..987.28
rows=3716 width=60) (actual time=407.000..610.000 rows=14862 loops=1)"
"Join Filter: ((("outer".contxt_id)::numeric(18,0)
= "inner".pk1) AND (("outer".contxt_elmt_ix)::numeric(18,0) =
"inner".pk2))"
"Filter: ("inner".trans_id IS NULL)"
"-> Merge Left Join (cost=718.22..746.87
rows=3716 width=60) (actual time=407.000..563.000 rows=14862 loops=1)"
" Merge Cond: (("outer".recv_node_id =
"inner".rsdnc_node_id) AND ("outer".owner_id = "inner".owner_id))"
" Filter: ("inner".repl_data_owner_id IS NULL)"
" -> Sort (cost=717.19..726.48 rows=3716
width=74) (actual time=407.000..423.000 rows=14862 loops=1)"
"Sort Key: trans.recv_node_id, c.owner_id"
"-> Nested Loop Left Join
(cost=1.01..496.84 rows=3716 width=74) (actual time=0.000..312.000
rows=14862 loops=1)"
" Join Filter:
("inner".non_repl_data_owner_id = "outer".owner_id)"
" Filter:
("inner".non_repl_data_owner_id IS NULL)"
" -> Nested Loop
(cost=0.00..412.22 rows=3716 width=74) (actual time=0.000..186.000
rows=14862 loops=1)"
"-> Seq Scan on
mrs_transaction trans (cost=0.00..2.05 rows=1 width=28) (actual
time=0.000..0.000 rows=1 loops=1)"
" Filter: (trans_id =
514::numeric)"
"-> Nested Loop
(cost=0.00..373.01 rows=3716 width=46) (actual time=0.000..139.000
rows=14862 loops=1)"
" Join Filter:
("outer".contxt_id = "inner".contxt_id)"
" -> Nested Loop
(cost=0.00..4.81 rows=1 width=16) (actual time=0.000..0.000 rows=4
loops=1)"
"Join Filter:
(("inner".contxt_id)::numeric(18
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
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 something like: That's what Magnus was talking about, but it's really no good because it would cause Postgres' now() function to fail to track post-boot-time changes in the system date setting. Which I think would rightly be considered a bug. The EXPLAIN ANALYZE instrumentation code will really be happier with a straight time-since-bootup counter; by using gettimeofday, it is vulnerable to giving wrong answers if someone changes the date setting while the EXPLAIN is running. But there is (AFAIK) no such call among the portable Unix syscalls. It seems reasonable to me to #ifdef that code to make use of QueryPerformanceCounter on Windows. This does not mean we want to alter the behavior of gettimeofday() where it's being used to find out the time of day. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
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 something like:
That's what Magnus was talking about, but it's really no good because
it would cause Postgres' now() function to fail to track post-boot-time
changes in the system date setting. Which I think would rightly be
considered a bug.
The EXPLAIN ANALYZE instrumentation code will really be happier with a
straight time-since-bootup counter; by using gettimeofday, it is
vulnerable to giving wrong answers if someone changes the date setting
while the EXPLAIN is running. But there is (AFAIK) no such call among
the portable Unix syscalls. It seems reasonable to me to #ifdef that
code to make use of QueryPerformanceCounter on Windows. This does not
mean we want to alter the behavior of gettimeofday() where it's being
used to find out the time of day.
regards, tom lane
What if you changed the "initialized" to
if (count & 0xFF == 0) {
count = 1;
// get the new time of day
}
++count;
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.
John
=:->
signature.asc
Description: OpenPGP digital signature
Re: [PERFORM] Help trying to tune query that executes 40x slower than in SqlServer
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 as C on (P.contxt_id=C.contxt_id) inner join > MRS_REPLICATION_OUT as S on S.ent_id=1029 and (CAST(P.contxt_id AS > numeric(18)) = S.pk1) inner join MRS_TRANSACTION TRANS on Can you *format* this query please, and re-submit it? Proper query format looks like: SELECT a.1, b.2 FROM a JOIN b ON a.1 = b.3 JOIN c ON b.4 = c.1 WHERE a.5 < 6 AND c.7 = '2005-01-01'; ... for maximum readability. Also, when asking others to help debug your queries, it helps them (and, frankly, you) if you can NOT use single-letter table aliases. Single-letter table aliases are evil for the same reason that single-letter variables in code are. Thanks! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Tuning, configuration for 7.3.5 on a Sun E4500
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. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Help trying to tune query that executes 40x slower than in SqlServer
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 as S on S.ent_id=1029
AND P.contxt_id = S.pk1
INNER JOIN MRS_TRANSACTION TRANS on TRANS.trans_id=514
LEFT JOIN ON_REPL_DATA_OWNER NRDO on
NRDO.non_repl_data_owner_id = C.owner_id
LEFT JOIN REPL_DATA_OWNER_RSDNC RDOR on RDOR.owner_id = C.owner_id
AND RDOR.rsdnc_node_id=TRANS.recv_node_id
LEFT JOIN MRS_REPLICATION_OUT OUT on OUT.trans_id = 514
AND OUT.ent_id=1168 and C.contxt_id = OUT.pk1
AND C.contxt_elmt_ix = OUT.pk2
INNER JOIN MRS_TRANSACTION RED_TRANS on
TRANS.prov_node_id=RED_TRANS.prov_node_id
AND TRANS.recv_node_id=RED_TRANS.recv_node_id
LEFT JOIN MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT'
AND RED_TRANS.trans_type in ('X01', 'X02')
AND RED_TRANS.trans_id = RED_OUT.trans_id
WHERE S.age=0 and S.trans_id=514
AND (NRDO.non_repl_data_owner_id is null)
AND (RDOR.repl_data_owner_id is null)
AND (OUT.trans_id is null)
AND (RED_OUT.trans_id is null);
Because GMAIL also cuts out text at 80 characters, I also send the
query in attachment.
Once again thanks for your help,
Hugo Ferreira
> Can you *format* this query please, and re-submit it? Proper query format
> looks like:
>
> SELECT a.1, b.2
> FROM a JOIN b ON a.1 = b.3
> JOIN c ON b.4 = c.1
> WHERE a.5 < 6
> AND c.7 = '2005-01-01';
>
> ... for maximum readability.
--
GPG Fingerprint: B0D7 1249 447D F5BB 22C5 5B9B 078C 2615 504B 7B85
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
AND P.contxt_id = S.pk1
INNER JOIN MRS_TRANSACTION TRANS on TRANS.trans_id=514
LEFT JOIN ON_REPL_DATA_OWNER NRDO on NRDO.non_repl_data_owner_id =
C.owner_id
LEFT JOIN REPL_DATA_OWNER_RSDNC RDOR on RDOR.owner_id = C.owner_id
AND RDOR.rsdnc_node_id=TRANS.recv_node_id
LEFT JOIN MRS_REPLICATION_OUT OUT on OUT.trans_id = 514
AND OUT.ent_id=1168 and C.contxt_id = OUT.pk1
AND C.contxt_elmt_ix = OUT.pk2
INNER JOIN MRS_TRANSACTION RED_TRANS on
TRANS.prov_node_id=RED_TRANS.prov_node_id
AND TRANS.recv_node_id=RED_TRANS.recv_node_id
LEFT JOIN MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT'
AND RED_TRANS.trans_type in ('X01', 'X02')
AND RED_TRANS.trans_id = RED_OUT.trans_id
WHERE S.age=0 and S.trans_id=514
AND (NRDO.non_repl_data_owner_id is null)
AND (RDOR.repl_data_owner_id is null)
AND (OUT.trans_id is null)
AND (RED_OUT.trans_id is null);
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
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 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 problem I see with this would be on multi-processor machines. (QueryPerformanceCounter does work properly on multi-processor machines, right?) -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help trying to tune query that executes 40x slower than in SqlServer
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
> AND P.contxt_id = S.pk1
>INNER JOIN MRS_TRANSACTION TRANS on TRANS.trans_id=514
>LEFT JOIN ON_REPL_DATA_OWNER NRDO on
> NRDO.non_repl_data_owner_id = C.owner_id
>LEFT JOIN REPL_DATA_OWNER_RSDNC RDOR on RDOR.owner_id = C.owner_id
> AND RDOR.rsdnc_node_id=TRANS.recv_node_id
>LEFT JOIN MRS_REPLICATION_OUT OUT on OUT.trans_id = 514
> AND OUT.ent_id=1168 and C.contxt_id = OUT.pk1
> AND C.contxt_elmt_ix = OUT.pk2
>INNER JOIN MRS_TRANSACTION RED_TRANS on
> TRANS.prov_node_id=RED_TRANS.prov_node_id
> AND TRANS.recv_node_id=RED_TRANS.recv_node_id
>LEFT JOIN MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT'
> AND RED_TRANS.trans_type in ('X01', 'X02')
> AND RED_TRANS.trans_id = RED_OUT.trans_id
I think the problem is that the intermix of inner and left joins forces
Postgres to do the joins in a particular order, per
http://www.postgresql.org/docs/8.0/static/explicit-joins.html
and this order is quite non optimal for your data. In particular it
looks like joining red_trans to red_out first, instead of last,
would be a good idea (I think but am not 100% certain that this
doesn't change the results).
It is possible but complicated to determine that reordering outer joins
is safe in some cases. We don't currently have such logic in PG. It
may be that SQL Server does have that capability and that's why it's
finding a much better plan ... but for now you have to do that by hand
in PG.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
> -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 > this query (with > [...] > 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 problem I see with this would be on multi-processor > machines. (QueryPerformanceCounter does work properly on > multi-processor machines, right?) I believe QueryPerformanceCounter() already does this. __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with explain analyze finally)
> -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 query > (with explain analyze finally) > > [...] > The EXPLAIN ANALYZE instrumentation code will really be happier with a > straight time-since-bootup counter; by using gettimeofday, it is > vulnerable to giving wrong answers if someone changes the date setting > while the EXPLAIN is running. But there is (AFAIK) no such call among > the portable Unix syscalls. It seems reasonable to me to #ifdef that > code to make use of QueryPerformanceCounter on Windows. This does not > mean we want to alter the behavior of gettimeofday() where it's being > used to find out the time of day. 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 Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
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, and
it had a resolution of 55clocks / s. When I just did this:
int main(int argc, char **argv)
{
int start = clock();
int now = start;
cout << "Clock: " << CLOCKS_PER_SEC << endl;
for(int i = 0; i < 10; ++i) {
while(now == clock()) {
// Do nothing
}
now = clock();
cout << now-start << "\t" << (now - start) / (double)
CLOCKS_PER_SEC << endl;
}
}
I got:
Clock: 1000
16 0.016
31 0.031
47 0.047
62 0.062
78 0.078
93 0.093
109 0.109
125 0.125
141 0.141
156 0.156
Which is about 1/0.016 = 62.5 clocks per second.
I'm pretty sure this is slightly worse than what we want. :)
It might be better on other platforms, but on win32 clock() is most
definitely *not* what you want.
John
=:->
signature.asc
Description: OpenPGP digital signature
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
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 which is probably more significant for our purposes. Which brings up a question: just what does QueryPerformanceCounter measure? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
"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 problem I see with this would be on multi-processor > > machines. (QueryPerformanceCounter does work properly on > > multi-processor machines, right?) > > I believe QueryPerformanceCounter() already does this. This would be a good example of why selectively quoting the part of the message to which you're responding to is more useful than just blindly echoing my message back to me. Already does what? Use rtdsc? In which case using it would be a mistake. Since rtdsc doesn't work across processors. And using it via QueryPerformanceCounter would be a non-portable approach to using rtdsc. Much better to devise a portable approach that works on any architecture where something equivalent is available. Or already works on multi-processor machines? In which case, uh, ok. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
> -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] [PERFORM] Help with tuning > this query > (with > > "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 problem I see with this would be on multi-processor > > > machines. (QueryPerformanceCounter does work properly on > > > multi-processor machines, right?) > > > > I believe QueryPerformanceCounter() already does this. > [...] > Already does what? > > Use rtdsc? Yes. > 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 something else otherwise. > And using it via QueryPerformanceCounter would be a non-portable > approach to using rtdsc. Much better to devise a portable > approach that works on any architecture where something equivalent > is available. How do you know that QueryPerformanceCounter doesn't use RDTSC where available, and something appropriate otherwise? I don't see how any strategy that explicitly executes RDTSC can be called "portable". > Or already works on multi-processor machines? In which case, uh, ok. According to MSDN it does work on MP systems, and they say that "it doesn't matter which CPU gets called". __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
> -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 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. Which is why I suggested QueryPerformanceCounter for Win32. I only suggested clock() for *nix. > The other problem is it measures process CPU time, not elapsed time > which is probably more significant for our purposes. Actually, the bigger problem is that a quick test of clock() on Linux shows that it only has a maximum resolution of 10ms on my hardware. Looks like gettimeofday() is the best choice. > Which brings up a question: just what does QueryPerformanceCounter > measure? I think it measures raw CPU cycles, roughly, which seems like it would more or less correspond to wall time. __ David B. Held Software Engineer/Array Services Group 200 14th Ave. East, Sartell, MN 56377 320.534.3637 320.253.7800 800.752.8129 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
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 something else otherwise. RDTSC is a bad source of information for this kind of thing, as the CPU frequency might vary. Check your QueryPerformanceFrequency() -- most likely it will not match your clock speed. I haven't tested on a lot of machines, but I've never seen QueryPerformanceFrequency() ever match the clock speed, which it most probably would if it was using RDTSC. (I've been told it uses some other kind of timer available on most motherboards, but I don't know the details.) /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
"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 believed such a counter for EXPLAIN, we'd severely understate the cost of disk I/O. I dunno if that is the case on any Windows hardware or not, but none of this thread is making me feel confident that we know what QueryPerformanceCounter does measure. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
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. > > I dunno if that is the case on any Windows hardware or not, but none > of this thread is making me feel confident that we know what > QueryPerformanceCounter does measure. I believe the counter is actually good in such a situation -- I'm not a Win32 guru, but I believe it is by far the best timer for measuring, well, performance of a process like this. After all, it's what it was designed to be :-) OBTW, I think I can name something like 15 or 20 different function calls to measure time in the Win32 API (all of them in use); it really is a giant mess. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
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: This enables the use of the HPET for the kernel's internal timer. HPET is the next generation timer replacing legacy 8254s. You can safely choose Y here. However, HPET will only be activated if the platform and the BIOS support this feature. Otherwise the 8254 will be used for timing services. Choose N to continue using the legacy 8254 timer. Symbol: HPET_TIMER [=y] Prompt: HPET Timer Support Defined at arch/i386/Kconfig:440 Location: -> Processor type and features CONFIG_X86_PM_TIMER: The Power Management Timer is available on all ACPI-capable, in most cases even if ACPI is unusable or blacklisted. This timing source is not affected by powermanagement features like aggressive processor idling, throttling, frequency and/or voltage scaling, unlike the commonly used Time Stamp Counter (TSC) timing source. So, if you see messages like 'Losing too many ticks!' in the kernel logs, and/or you are using this on a notebook which does not yet have an HPET, you should say "Y" here. Symbol: X86_PM_TIMER [=y] Prompt: Power Management Timer Support Defined at drivers/acpi/Kconfig:319 Depends on: !X86_VOYAGER && !X86_VISWS && !IA64_HP_SIM && (IA64 || X86) && X86 && ACPI && ACPI_ Location: -> Power management options (ACPI, APM)-> ACPI (Advanced Configuration and Power Interface) Support -> ACPI Support (ACPI [=y]) On Tue, 08 Mar 2005 03:06:24 +0100, Steinar H. Gunderson <[EMAIL PROTECTED]> wrote: 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. I dunno if that is the case on any Windows hardware or not, but none of this thread is making me feel confident that we know what QueryPerformanceCounter does measure. I believe the counter is actually good in such a situation -- I'm not a Win32 guru, but I believe it is by far the best timer for measuring, well, performance of a process like this. After all, it's what it was designed to be :-) OBTW, I think I can name something like 15 or 20 different function calls to measure time in the Win32 API (all of them in use); it really is a giant mess. /* Steinar */ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] adding 'limit' leads to very slow query
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 = 41; 7 * This query happens very quickly. explain select * from foo where barId = 412 order by id desc; Sort () Sort key= id -> Index scan using bar_index on foo () Index cond: barId = 412 But this query takes forever explain select * from foo where barId = 412 order by id desc limit 25; Limit () -> Index scan backward using primarykey_index Filter: barID = 412 Could anyone shed some light on what might be happening here? - Michael -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
> -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 > this query (with > > Which brings up a question: just what does QueryPerformanceCounter > measure? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/winui/w inui/windowsuserinterface/windowing/timers/abouttimers.asp says: If a high-resolution performance counter exists on the system, you can use the QueryPerformanceFrequency function to express the frequency, in counts per second. The value of the count is processor dependent. On some processors, for example, the count might be the cycle rate of the processor clock. The QueryPerformanceCounter function retrieves the current value of the high-resolution performance counter. By calling this function at the beginning and end of a section of code, an application essentially uses the counter as a high-resolution timer. For example, suppose that QueryPerformanceFrequency indicates that the frequency of the high-resolution performance counter is 50,000 counts per second. If the application calls QueryPerformanceCounter immediately before and immediately after the section of code to be timed, the counter values might be 1500 counts and 3500 counts, respectively. These values would indicate that .04 seconds (2000 counts) elapsed while the code executed. Regards, Dave. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Help with slow running query
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.startip and c.endip) Nested Loop (cost=167.59..7135187.85 rows=31701997 width=10) (actual time=63.000..776094.000 rows=5235 loops=1) Join Filter: ((inner.ip >= outer.startip) AND (inner.ip <= outer.endip)) -> Seq Scan on ip c (cost=0.00..2071.02 rows=54502 width=28) (actual time=0.000..313.000 rows=54502 loops=1) -> Materialize (cost=167.59..219.94 rows=5235 width=15) (actual time=0.000..2.973 rows=5235 loops=54502) -> Seq Scan on internal i (cost=0.00..162.35 rows=5235 width=15) (actual time=0.000..16.000 rows=5235 loops=1) Total runtime: 776110.000 ms -- data from ip-to-country.webhosting.info CREATE TABLE country.ip -- 54,502 rows ( startip inet NOT NULL, endip inet NOT NULL, code char(2) NOT NULL, CONSTRAINT ip_pkey PRIMARY KEY (startip, endip) ); -- 1, 192.168.1.10, 192.168.2.100, US -- 2, 192.168.3.0, 192.168.3.118, US CREATE TABLE local.internal -- 5000+ rows ( internalid serial NOT NULL, ip inet NOT NULL, port int2 NOT NULL, CONSTRAINT internal_pkey PRIMARY KEY (internalid) ); CREATE INDEX ip_idx ON local.internal (ip); -- 1, 10.0.0.100, 80 -- 2, 10.0.0.102, 80 -- 3, 10.0.0.103, 443 -- postgresql.conf have tried many settings with no improvement max_connections = 50 shared_buffers = 3 work_mem = 2048 sort_mem = 2048 Have tried many different indexes with no help: CREATE INDEX endip_idx ON country.ip; CREATE INDEX startip_idx ON country.ip; CREATE UNIQUE INDEX e_s_idx ON country.ip (endip, startip); Any suggestions would be greatly appreciated. __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] performance problems
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 needs ca. 20 ms All server options are still default !! Any idea on wich "screw" I can turn ?. Thanks for suggestions! Michael Zöphel e-Mail: [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [PERFORM] Help with tuning this query (with
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 CPU time, not elapsed time which is probably more significant for our purposes. Which brings up a question: just what does QueryPerformanceCounter measure? regards, tom lane clock() according to the Visual Studio Help measures wall clock time. But you're right, POSIX says it is approximation of processor time. The docs don't say specifically what QueryPerformanceCounter() measures, but states The *QueryPerformanceCounter* function retrieves the current value of the high-resolution performance counter. It also states: Remarks On a multiprocessor machine, it should not matter which processor is called. However, you can get different results on different processors due to bugs in the BIOS or the HAL. To specify processor affinity for a thread, use the *SetThreadAffinityMask* function. So it sounds like it is actually querying some counter independent of processing. In fact, there is also this statement: *QueryPerformanceFrequency* The QueryPerformanceFrequency function retrieves the frequency of the high-resolution performance counter, if one exists. The frequency cannot change while the system is running. If that is accurate, it would make QueryPerformanceCounter independent of things like speed stepping, etc. So again, it sounds independent of processing. John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] adding 'limit' leads to very slow query
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 Filter: barID = 412 Could anyone shed some light on what might be happening here? - Michael It is using the wrong index. The problem is that order by + limit generally means that you can use the index on the "+" to get the items in the correct order. In this case, however, you need it to find all of the barId=412 first, since apparently that is more selective than the limit. It really sounds like the postgres statistics are out of date. And either you haven't run vacuum analyze recently, or you need to keep higher statistics on either one or both of barId and id. John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] adding 'limit' leads to very slow query
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(*) from foo; > 1,000,000 > > select count(*) from foo where bar = 41; > 7 > > * This query happens very quickly. > > explain select * from foo where barId = 412 order by id desc; > Sort () >Sort key= id > -> Index scan using bar_index on foo () > Index cond: barId = 412 > > But this query takes forever > > > explain select * from foo where barId = 412 order by id desc limit 25; > Limit () > -> Index scan backward using primarykey_index >Filter: barID = 412 You didn't show the row estimates, but I'd guess that it's expecting either that ther are more rows that match barId=412 than there actually are (which may be solvable by raising the statistics target on the column and re-analyzing) such that going backwards on id in order to make 25 matching rows isn't a bad plan or that barId and id are correlated which is unfortunately not going to be recognized right now. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
