RES: RES: [PERFORM] Degradation of postgres 7.4.5 on FreeBSD/CygWin
Hi all, I Got more improvements using vacuumdb utility and the size of my database was decreasead from 1.3gb to 900mb. Only one thing is not right yeat. My procedure perform others 7 subprocedures and with reimported database, it's took about 5 minutes to complete. With old vacuumed database, the same process took 20minutes, it's much better than the 4 hours before, but there is little diference. Now, i have scheduled the vacuumdb --analyze once a day and vacuumdb --analyze --all --full once a week, i think this is enough. Now i'll check for reindexes tables and i'll perform analyze in each query in procedure. When i get more results, i post here. Thanks a Lot Rodrigo Moreno ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] bad performances using hashjoin
Hi all,
I'm stuck in a select that use the hash join where should not:
6 seconds vs 0.3 ms !!
If you need other info in order to improve the planner,
let me know.
Regards
Gaetano Mendola
empdb=# explain analyze SELECT id_sat_request
empdb-#FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-#WHERE- JOIN
empdb-# sr.id_package = vs.id_package AND
empdb-# ---
empdb-# id_user = 29416 AND
empdb-# id_url = 329268 AND
empdb-# vs.estimated_start > now() AND
empdb-# id_sat_request_status = sp_lookup_id('sat_request_status',
'Scheduled');
QUERY PLAN
---
Hash Join (cost=272.95..276.61 rows=1 width=4) (actual
time=6323.107..6323.107 rows=0 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vs (cost=269.91..272.10 rows=292 width=4) (actual
time=6316.534..6317.398 rows=407 loops=1)
-> Sort (cost=269.91..270.64 rows=292 width=263) (actual
time=6316.526..6316.789 rows=407 loops=1)
Sort Key: vs.estimated_start
-> Hash Join (cost=227.58..257.95 rows=292 width=263) (actual
time=6302.480..6313.982 rows=407 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vpk (cost=141.82..150.04 rows=1097
width=218) (actual time=6106.020..6113.038 rows=1104 loops=1)
-> Sort (cost=141.82..144.56 rows=1097 width=162)
(actual time=6106.006..6106.745 rows=1104 loops=1)
Sort Key: p.id_publisher, p.name
-> Hash Left Join (cost=15.54..86.42
rows=1097 width=162) (actual time=2.978..6087.608 rows=1104 loops=1)
Hash Cond: ("outer".id_package =
"inner".id_package)
-> Seq Scan on packages p
(cost=0.00..53.48 rows=1097 width=146) (actual time=0.011..7.978 rows=1104
loops=1)
-> Hash (cost=13.69..13.69 rows=738
width=20) (actual time=2.061..2.061 rows=0 loops=1)
-> Seq Scan on package_security
ps (cost=0.00..13.69 rows=738 width=20) (actual time=0.027..1.289 rows=747
loops=1)
-> Hash (cost=85.63..85.63 rows=54 width=49) (actual
time=196.022..196.022 rows=0 loops=1)
-> Merge Join (cost=82.83..85.63 rows=54 width=49)
(actual time=192.898..195.565 rows=407 loops=1)
Merge Cond: ("outer".id_program =
"inner".id_program)
-> Subquery Scan vs (cost=72.27..73.97
rows=226 width=16) (actual time=6.867..7.872 rows=407 loops=1)
-> Sort (cost=72.27..72.84 rows=226
width=20) (actual time=6.851..7.114 rows=407 loops=1)
Sort Key: sequences.id_program,
sequences.internal_position
-> Seq Scan on sequences
(cost=0.00..63.44 rows=226 width=20) (actual time=0.295..3.370 rows=407 loops=1)
Filter: ((estimated_start IS
NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Sort (cost=10.56..10.68 rows=47 width=37)
(actual time=186.013..186.296 rows=439 loops=1)
Sort Key: vpr.id_program
-> Subquery Scan vpr (cost=8.90..9.25
rows=47 width=37) (actual time=185.812..185.928 rows=48 loops=1)
-> Sort (cost=8.90..9.02 rows=47
width=61) (actual time=185.806..185.839 rows=48 loops=1)
Sort Key:
programs.id_publisher, programs.id_program
-> Seq Scan on programs
(cost=0.00..7.60 rows=47 width=61) (actual time=9.592..185.634 rows=48 loops=1)
Filter: (id_program <>
0)
-> Hash (cost=3.04..3.04 rows=1 width=8) (actual time=4.862..4.862 rows=0
loops=1)
-> Index Scan using idx_id_url_sat_request on sat_request sr
(cost=0.00..3.04 rows=1 width=8) (actual time=4.851..4.851 rows=0 loops=1)
Index Cond: (id_url = 329268)
Filter: ((id_user = 29416) AND (id_sat_request_status = 1))
Total runtime: 6324.435 ms
(35 rows)
empdb=# set enable_hashjoin = false;
SET
empdb=# explain analyze SELECT id_sat_request
empdb-#FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-#WHERE- JOIN
empdb-# sr.id_package = vs.id_package
[PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function
I newly installed the postgresql 7.4.5 and FC 3 in my server and transfer the data from 7.3.2 with just a few problems. After I use the webmin 1.8 to config the grant previllage to the users ,I found that there is an error in the grant previlege . postgresql --> Grant Previlege --> error select relname, relacl from pg_class where (relkind = 'r' OR relkind = 'S') and relname !~ '^pg_' order by relname : Unknown DBI error What is the cause of this error and how could I handle this order? Please make any comment, Thanks. Amrit , Thailand ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] seq scan cache vs. index cache smackdown
>> I don't think that's correct either. Scatter/Gather I/O is >used to SQL >> Server can issue reads for several blocks from disks into it's own >> buffer cache with a single syscall even if these buffers are not >> sequential. It did make significant performance improvements >when they >> added it, though. >> >> (For those not knowing - it's ReadFile/WriteFile where you >pass an array >> of "this many bytes to this address" as parameters) > >Isn't that like the BSD writev()/readv() that Linux supports also? Is >that something we should be using on Unix if it is supported by the OS? Yes, they certainly seem very similar. The win32 functions are explicitly designed for async I/O (they were after all created specifically for SQL Server), so they put harder requirements on the parameters. Specifically, it writes complete system pages only, and each pointer has to point to only one page. In a file opened without buffering it will also write all buffers out and then wait for I/O completion from the device instead of one for each. Not sure what the writev/readv ones do (not clear from my linux man page). Now wether this is something we could make use of - I'll leave that up to those who know the buffer manager a lot better than I do. //Magnus ---(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] bad performances using hashjoin
Gaetano Mendola <[EMAIL PROTECTED]> writes: > If you need other info in order to improve the planner, ... like, say, the PG version you are using, or the definitions of the views involved? It's difficult to say much of anything about this. However: the reason the second plan wins is because there are zero rows fetched from sat_request, and so the bulk of the plan is never executed at all. I doubt the second plan would win if there were any matching sat_request rows. If this is the case you actually need to optimize, probably the thing to do is to get rid of the ORDER BY clauses you evidently have in your views, so that there's some chance of building a fast-start plan. BTW, I believe in 8.0 the first plan would be about as fast as the second, because we added some code to hash join to fall out without scanning the left input if the right input is empty. 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: [PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function
[EMAIL PROTECTED] wrote: I newly installed the postgresql 7.4.5 and FC 3 in my server and transfer the data from 7.3.2 with just a few problems. After I use the webmin 1.8 to config the grant previllage to the users ,I found that there is an error in the grant previlege . postgresql --> Grant Previlege --> error select relname, relacl from pg_class where (relkind = 'r' OR relkind = 'S') and relname !~ '^pg_' order by relname : Unknown DBI error What is the cause of this error and how could I handle this order? Please make any comment, Thanks. I would suspect a DBI/DBD installation issue, either perl DBI cannot find DBD-Pg (not installed ?) or DBD-Pg cannot find your Pg 7.4.5. I note that FC3 comes with Pg 7.4.6 - did you installed 7.4.5 from source? If so this could be why the perl database modules cannot find it (you may need to rebuild DBD-Pg, telling it where your Pg install is). regards Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] bad performances using hashjoin
Tom Lane wrote:
> Gaetano Mendola <[EMAIL PROTECTED]> writes:
>
>>If you need other info in order to improve the planner,
>
>
> ... like, say, the PG version you are using, or the definitions of the
> views involved? It's difficult to say much of anything about this.
That is true, sorry I forgot it :-(
The engine is a 7.4.5 and these are the views definitions:
sat_request is just a table
CREATE OR REPLACE VIEW v_sc_packages AS
SELECT *
FROM
v_programs vpr,
v_packages vpk,
v_sequences vs
WHERE
JOIN -
vpr.id_program = vs.id_program AND
vpk.id_package = vs.id_package AND
---
vs.estimated_start IS NOT NULL
ORDER BY vs.estimated_start;
CREATE OR REPLACE VIEW v_programs AS
SELECT *
FROM programs
WHERE id_program<>0
ORDER BY id_publisher, id_program
;
CREATE OR REPLACE VIEW v_packages AS
SELECT *
FROM packages p LEFT OUTER JOIN package_security ps USING (id_package)
ORDER BY p.id_publisher, p.name
;
CREATE OR REPLACE VIEW v_sequences AS
SELECT id_packageAS id_package,
id_programAS id_program,
internal_position AS internal_position,
estimated_start AS estimated_start
FROM sequences
ORDER BY id_program, internal_position
;
> However: the reason the second plan wins is because there are zero rows
> fetched from sat_request, and so the bulk of the plan is never executed
> at all. I doubt the second plan would win if there were any matching
> sat_request rows. If this is the case you actually need to optimize,
> probably the thing to do is to get rid of the ORDER BY clauses you
> evidently have in your views, so that there's some chance of building
> a fast-start plan.
Removed all order by from that views, this is the comparison between the two
(orderdered and not ordered):
empdb=# explain analyze SELECT id_sat_request
empdb-#FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-#WHERE- JOIN
empdb-# sr.id_package = vs.id_package AND
empdb-# ---
empdb-# id_user = 29416 AND
empdb-# id_url = 424364 AND
empdb-# vs.estimated_start > now() AND
empdb-# id_sat_request_status = sp_lookup_id('sat_request_status',
'Scheduled');
QUERY PLAN
---
Hash Join (cost=280.98..284.74 rows=1 width=4) (actual time=895.344..895.344
rows=0 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vs (cost=277.94..280.19 rows=301 width=4) (actual
time=893.191..894.396 rows=569 loops=1)
-> Sort (cost=277.94..278.69 rows=301 width=263) (actual
time=893.184..893.546 rows=569 loops=1)
Sort Key: vs.estimated_start
-> Hash Join (cost=232.61..265.54 rows=301 width=263) (actual
time=868.980..889.643 rows=569 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vpk (cost=150.29..159.26 rows=1196
width=218) (actual time=822.281..834.063 rows=1203 loops=1)
-> Sort (cost=150.29..153.28 rows=1196 width=159)
(actual time=822.266..823.190 rows=1203 loops=1)
Sort Key: p.id_publisher, p.name
-> Hash Left Join (cost=16.14..89.16
rows=1196 width=159) (actual time=3.504..809.262 rows=1203 loops=1)
Hash Cond: ("outer".id_package =
"inner".id_package)
-> Seq Scan on packages p
(cost=0.00..53.98 rows=1196 width=143) (actual time=0.018..13.869 rows=1203
loops=1)
-> Hash (cost=14.09..14.09 rows=818
width=20) (actual time=2.395..2.395 rows=0 loops=1)
-> Seq Scan on package_security
ps (cost=0.00..14.09 rows=818 width=20) (actual time=0.020..1.520 rows=845
loops=1)
-> Hash (cost=82.19..82.19 rows=51 width=49) (actual
time=46.402..46.402 rows=0 loops=1)
-> Merge Join (cost=79.54..82.19 rows=51 width=49)
(actual time=39.435..45.376 rows=569 loops=1)
Merge Cond: ("outer".id_program =
"inner".id_program)
-> Subquery Scan vs (cost=70.98..72.59
rows=214 width=16) (actual time=16.834..19.102 rows=569 loops=1)
-> Sort (cost=70.98..71.52 rows=214
width=20) (actual time=16.824..17.338 rows=569 loops=1)
Sort Key: sequences.id_program,
sequences.internal_position
-> Seq Sca
Re: [PERFORM] Effects of IDLE processes
JM wrote: > Hi ALL, > > I was wondering if there is a DB performance reduction if there are a > lot of > IDLE processes. > > 30786 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 32504 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 32596 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 1722 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 1724 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 3881 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 6332 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 6678 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 6700 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 6768 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 8544 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 8873 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 8986 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 9000 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 9010 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 9013 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 9016 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 9019 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > 9020 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle > In my experience not at all, you have to wonder if some of that are "idle in transaction" that are really a pain in the @#$ Regards Gaetano Mendola ---(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] bad performances using hashjoin
On Sun, 20 Feb 2005 13:46:10 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > sat_request rows. If this is the case you actually need to optimize, > probably the thing to do is to get rid of the ORDER BY clauses you > evidently have in your views, so that there's some chance of building > a fast-start plan. Is having an order by in a view legal? In sybase ASA, mssql it throws a syntax error if there's an order by. If so, does it do 2 sorts when you sort by something else? i.e. if view is create view v1 as select * from table order by 1; and the statment select * from v1 order by 2; is run does it sort by 1 then resort by 2? klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function
> I would suspect a DBI/DBD installation issue, either perl DBI cannot > find DBD-Pg (not installed ?) or DBD-Pg cannot find your Pg 7.4.5. > > I note that FC3 comes with Pg 7.4.6 - did you installed 7.4.5 from > source? If so this could be why the perl database modules cannot find it > (you may need to rebuild DBD-Pg, telling it where your Pg install is). > > regards > > Mark > I installed FC3 from rpm kernel 2.6.9 which already included postgresql 7.4.5 . Suppose that there were some missing component , what should be the missing rpm component which I forgot to install ? Amrit , Thailand ---(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] bad performances using hashjoin
Klint Gore <[EMAIL PROTECTED]> writes: > Is having an order by in a view legal? Not according to the SQL spec, but PG has allowed it for several releases. (The same goes for ORDER BY in a sub-select, which is actually pretty much the same thing ...) > If so, does it do 2 sorts when you sort by something else? Yup. It's something you'd only want for the topmost view in a stack, IMHO. A sort at a lower level is likely to be wasted effort. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] bad performances using hashjoin
Tom Lane wrote: However: the reason the second plan wins is because there are zero rows fetched from sat_request, and so the bulk of the plan is never executed at all. I doubt the second plan would win if there were any matching sat_request rows. That's what I thought at first, but if you look more closely, that's having very little impact on either the cost or actual time: -> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.04 rows=1 width=8) (actual time=0.031..0.031 rows=0 loops=1) The real problem appears to be here: -> Hash Left Join (cost=16.14..89.16 rows=1196 width=159) (actual time=3.504..809.262 rows=1203 loops=1) As Gaetano points out in his follow-up post, the problem still exists after he removed the sorts: -> Hash Left Join (cost=16.14..80.19 rows=1196 width=4) (actual time=7.291..13.620 rows=1203 loops=1) The planner is not breaking up the outer join in his v_packages view: SELECT * FROM packages p LEFT OUTER JOIN package_security ps USING (id_package) It's not being selective at all with packages, despite id_package being the link to sat_request. If this is too complex for the planner, could he re-arrange his outer join so that's it's processed later? If he puts it in his actual query, for instance, will the planner flatten it out anyway? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Effects of IDLE processes
After a long battle with technology, Gaetano Mendola <[EMAIL PROTECTED]>, an earthling, wrote: > JM wrote: >> Hi ALL, >> >> I was wondering if there is a DB performance reduction if >> there are a lot of IDLE processes. >> >> 30786 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 32504 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 32596 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 1722 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 1724 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 3881 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 6332 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 6678 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 6700 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 6768 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 8544 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 8873 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 8986 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 9000 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 9010 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 9013 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 9016 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 9019 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> 9020 ?S 0:00 postgres: user1 gmadb 10.10.10.1 idle >> > In my experience not at all, you have to wonder if some of that are > "idle in transaction" that are really a pain in the @#$ I'd be concerned about "idle" processes insofar as they are holding on to _some_ memory that isn't shared. "idle in transaction" is quite another matter; long-running transactions certainly do lead to evil. When running Slony-I, for instance, "idle in transaction" means that pg_listener entries are being held onto so they cannot be vacuumed out, and that's only one example of a possible evil... -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://linuxdatabases.info/info/languages.html You know how most packages say "Open here". What is the protocol if the package says, "Open somewhere else"? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] bad performances using hashjoin
David Brown <[EMAIL PROTECTED]> writes:
> The planner is not breaking up the outer join in his v_packages view:
The planner doesn't make any attempt to rearrange join order of outer
joins. There are some cases where such a rearrangement is OK, but there
are other cases where it isn't, and we don't currently have the logic
needed to tell which is which.
In the particular case at hand here, 8.0's hack to suppress evaluating
the outer side of a hash join after finding the inner side is empty
would eliminate the complaint.
In the original message, it did seem that the packages-to-
package_security join is taking a lot longer than one would expect:
-> Hash Left Join (cost=15.54..86.42 rows=1097 width=162) (actual
time=2.978..6087.608 rows=1104 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..53.48 rows=1097 width=146)
(actual time=0.011..7.978 rows=1104 loops=1)
-> Hash (cost=13.69..13.69 rows=738 width=20) (actual
time=2.061..2.061 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..13.69 rows=738
width=20) (actual time=0.027..1.289 rows=747 loops=1)
but this behavior isn't reproduced in the later message, so I wonder if
it wasn't an artifact of something else taking a chunk of time.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
