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)
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 loops=1)
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,0) = outer.pk1)
- Index
Scan using
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: [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: 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
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
[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])
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
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
