[PERFORM] please comment on cpu 32 bit or 64 bit
please help me , comment on postgresql (8.x.x) performance on cpu AMD, INTEL and why i should use 32 bit or 64 cpu ? (what the performance difference) thank you _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Too slow query, do you have an idea to optimize?
I need to generate unused random id with format is ID[0-9]{4}
so i write below query but it seems to be too slow
SELECT * FROM (
SELECT user_id FROM (
SELECT 'ID' || LPAD(r, 4, '0') AS user_id
FROM generate_series(1, ) as r) AS s
EXCEPT
SELECT user_id FROM account ) AS t
ORDER BY random()
LIMIT 1
and I execute explain analyze query.
--
Limit (cost=318.17..318.17 rows=1 width=32) (actual
time=731.703..731.707 rows=1 loops=1)
- Sort (cost=318.17..318.95 rows=312 width=32) (actual
time=731.693..731.693 rows=1 loops=1)
Sort Key: random()
- Subquery Scan t (cost=285.79..305.24 rows=312 width=32)
(actual time=424.299..659.193 rows= loops=1)
- SetOp Except (cost=285.79..301.35 rows=311
width=16) (actual time=424.266..566.254 rows= loops=1)
- Sort (cost=285.79..293.57 rows=3112
width=16) (actual time=424.139..470.529 rows=12111 loops=1)
Sort Key: user_id
- Append (cost=0.00..105.24 rows=3112
width=16) (actual time=5.572..276.485 rows=12111 loops=1)
- Subquery Scan *SELECT* 1
(cost=0.00..30.00 rows=1000 width=4) (actual time=5.565..149.615
rows= loops=1)
- Function Scan on
generate_series r (cost=0.00..20.00 rows=1000 width=4) (actual
time=5.553..63.224 rows= loops=1)
- Subquery Scan *SELECT* 2
(cost=0.00..75.24 rows=2112 width=16) (actual time=0.030..28.473
rows=2112 loops=1)
- Seq Scan on account
(cost=0.00..54.12 rows=2112 width=16) (actual time=0.019..10.155
rows=2112 loops=1)
Total runtime: 738.809 ms
do you have any idea for optimize?
--
Choe, Cheng-Dae(최정대)
Blog: http://www.comdongin.com/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] Too slow query, do you have an idea to optimize?
Generate them all into a table and just delete them as you use them.
It's only 1 rows...
Chris
Choe, Cheng-Dae wrote:
I need to generate unused random id with format is ID[0-9]{4}
so i write below query but it seems to be too slow
SELECT * FROM (
SELECT user_id FROM (
SELECT 'ID' || LPAD(r, 4, '0') AS user_id
FROM generate_series(1, ) as r) AS s
EXCEPT
SELECT user_id FROM account ) AS t
ORDER BY random()
LIMIT 1
and I execute explain analyze query.
--
Limit (cost=318.17..318.17 rows=1 width=32) (actual
time=731.703..731.707 rows=1 loops=1)
- Sort (cost=318.17..318.95 rows=312 width=32) (actual
time=731.693..731.693 rows=1 loops=1)
Sort Key: random()
- Subquery Scan t (cost=285.79..305.24 rows=312 width=32)
(actual time=424.299..659.193 rows= loops=1)
- SetOp Except (cost=285.79..301.35 rows=311
width=16) (actual time=424.266..566.254 rows= loops=1)
- Sort (cost=285.79..293.57 rows=3112
width=16) (actual time=424.139..470.529 rows=12111 loops=1)
Sort Key: user_id
- Append (cost=0.00..105.24 rows=3112
width=16) (actual time=5.572..276.485 rows=12111 loops=1)
- Subquery Scan *SELECT* 1
(cost=0.00..30.00 rows=1000 width=4) (actual time=5.565..149.615
rows= loops=1)
- Function Scan on
generate_series r (cost=0.00..20.00 rows=1000 width=4) (actual
time=5.553..63.224 rows= loops=1)
- Subquery Scan *SELECT* 2
(cost=0.00..75.24 rows=2112 width=16) (actual time=0.030..28.473
rows=2112 loops=1)
- Seq Scan on account
(cost=0.00..54.12 rows=2112 width=16) (actual time=0.019..10.155
rows=2112 loops=1)
Total runtime: 738.809 ms
do you have any idea for optimize?
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] please comment on cpu 32 bit or 64 bit
wisan watcharinporn [EMAIL PROTECTED] writes: comment on postgresql (8.x.x) performance on cpu AMD, INTEL and why i should use 32 bit or 64 cpu ? (what the performance difference) For most database applications, you're better off spending your money on faster disk drives and/or more RAM than on a sexier CPU. Maybe your application doesn't follow that general rule --- but since you told us exactly zero about what your application is, this advice is worth what you paid for it ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] CHECK vs REFERENCES
Which is faster, where the list involved is fixed? My thought is that since it doesn't have to check a seperate table, the CHECK itself should be the faster of the two, but I can't find anything that seems to validate that theory ... The case is where I just want to check that a value being inserted is one of a few possible values, with that list of values rarely (if ever) changing, so havng a 'flexible list' REFERENCED seems relatively overkill ... Thoughts, or pointers to a doc that disproves, or proves, what I believe? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] CHECK vs REFERENCES
On Sat, Sep 10, 2005 at 12:23:19AM -0300, Marc G. Fournier wrote: Which is faster, where the list involved is fixed? My thought is that since it doesn't have to check a seperate table, the CHECK itself should be the faster of the two, but I can't find anything that seems to validate that theory ... Why not just benchmark each method as you intend to use them? Here's a simplistic example: CREATE TABLE test_none ( val integer NOT NULL ); CREATE TABLE test_check ( val integer NOT NULL CHECK (val IN (1, 2, 3, 4, 5)) ); CREATE TABLE test_vals ( id integer PRIMARY KEY ); INSERT INTO test_vals SELECT * FROM generate_series(1, 5); CREATE TABLE test_fk ( val integer NOT NULL REFERENCES test_vals ); \timing INSERT INTO test_none SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3109.089 ms INSERT INTO test_check SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3492.344 ms INSERT INTO test_fk SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 23578.853 ms -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] CHECK vs REFERENCES
On Fri, 9 Sep 2005, Michael Fuhr wrote: On Sat, Sep 10, 2005 at 12:23:19AM -0300, Marc G. Fournier wrote: Which is faster, where the list involved is fixed? My thought is that since it doesn't have to check a seperate table, the CHECK itself should be the faster of the two, but I can't find anything that seems to validate that theory ... Why not just benchmark each method as you intend to use them? Here's a simplistic example: CREATE TABLE test_none ( val integer NOT NULL ); CREATE TABLE test_check ( val integer NOT NULL CHECK (val IN (1, 2, 3, 4, 5)) ); CREATE TABLE test_vals ( id integer PRIMARY KEY ); INSERT INTO test_vals SELECT * FROM generate_series(1, 5); CREATE TABLE test_fk ( val integer NOT NULL REFERENCES test_vals ); \timing INSERT INTO test_none SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3109.089 ms INSERT INTO test_check SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 3492.344 ms INSERT INTO test_fk SELECT 1 FROM generate_series(1, 10); INSERT 0 10 Time: 23578.853 ms Yowch, I expected CHECK to be better ... but not so significantly ... I figured I'd be saving milliseconds, which, on a busy server, would add up fast ... but not 10k' of milliseconds ... Thanks, that definitely shows a major benefit ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] please comment on cpu 32 bit or 64 bit
[EMAIL PROTECTED] (wisan watcharinporn) writes: please help me , comment on postgresql (8.x.x) performance on cpu AMD, INTEL and why i should use 32 bit or 64 cpu ? (what the performance difference) Generally speaking, the width of your I/O bus will be more important to performance than the width of the processor bus. That is, having more and better disk will have more impact on performance than getting a better CPU. That being said, if you plan to have a system with significantly more than 2GB of memory, there seem to be pretty substantial benefits to the speed of AMD memory bus access, and that can be quite significant, given that if you have a lot of memory, and thus are often operating out of cache, and are slinging around big queries, THAT implies a lot of shoving data around in memory. AMD/Opteron has a way faster memory bus than the Intel/Xeon systems. But this is only likely to be significant if you're doing processing intense enough that you commonly have 4GB of memory in use. If not, then you'd better focus on I/O speed, which is typically pretty independent of the CPU... -- (format nil [EMAIL PROTECTED] cbbrowne ntlug.org) http://www.ntlug.org/~cbbrowne/rdbms.html Anyway I know how to not be bothered by consing on the fly. -- Dave Moon ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
