Re: [PERFORM] Performance problem with Sarge compared with Woody
El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió: > Also also, you should be running at LEAST 7.4.13, the latest release of > 7.4. It's possible there's a fix between 7.4.7 and 7.4.13 that fixes > your problem. Doubt it, but it could be. However, the more important > point is that there are REAL data eating bugs in 7.4.7 that may take a > bite out of your data. First, thanks for all your answers. About your comments: * Yes, i have executed VACUUM FULL ANALYZE VERBOSE after the dump, and after all my tries to solve this. * About another ERP: this ERP is one developed by us, we are developing the next version, but until this is finished we need to maintain the old one, with all his problems (as the "montrous" selects). * About Postgre version: you advice me to upgrade from 7.4.7 (postgre version at sarge) to 8.2. Well, I don't want to be a troll, but I upgrade from 7.2.1 (woody) to 7.4.7 and I get worse, do you really think that upgrade to 8.1 will solve something? About the indices: I comment previously that I think that the problem could be at the indices. Well, at the woody postgre version we add all the indices by hand, including the primary key index. The dump takes all these and inserts at the sarge version, but sarge inserts an implicit index using the primary key, so at the sarge version we have duplicate indices. There are any difference between 7.2.1 and 7.4.2 versions about this? With the 7.4.2 there are more indices, or there was duplicated indices with the woody version too? (before you comment this: yes I try to remove the duplicate indices to check if this was the problem) -- Piñeiro <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Poor performance on seq scan
Hello, I have a big table called products. Table size: 1123MB. Toast table size: 32MB. Indexes size: 380MB. I try to do a query like this: select id,name from products where name like '%Mug%'; Yes, I know that tsearch2 is better for this, but please read on. The above query gives this plan: Seq Scan on product (cost=0.00..153489.52 rows=31390 width=40) Filter: (name ~~ '%Mug%'::text) When I use this with explain analyze: "Seq Scan on product (cost=0.00..153489.52 rows=31390 width=40) (actual time=878.873..38300.588 rows=72567 loops=1)" " Filter: (name ~~ '%Mug%'::text)" "Total runtime: 38339.026 ms" Meanwhile, "iostat 5" gives something like this: tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0 0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0 0 12 125.66 128 15.75 125.26 128 15.68 10 0 85 6 0 0 12 124.66 129 15.67 124.39 129 15.64 12 0 85 3 0 0 12 117.13 121 13.87 117.95 121 13.96 12 0 84 5 0 0 12 104.84 118 12.05 105.84 118 12.19 10 0 87 2 0 130 transfers per second with 12-15MB/sec transfer speed. (FreeBSD 6.1 with two STATA150 drives in gmirror RAID1) I made another test. I create a file with the identifiers and names of the products: psql#\o products.txt psql#select id,name from product; Then I can search using grep: grep "Mug" products.txt | cut -f1 -d\| There is a huge difference. This command runs within 0.5 seconds. That is, at least 76 times faster than the seq scan. It is the same if I vacuum, backup and restore the database. I thought that the table is stored in one file, and the seq scan will be actually faster than grepping the file. Can you please tell me what am I doing wrong? I'm not sure if I can increase the performance of a seq scan by adjusting the values in postgresql.conf. I do not like the idea of exporting the product table periodically into a txt file, and search with grep. :-) Another question: I have a btree index on product(name). It contains all product names and the identifiers of the products. Wouldn't it be easier to seq scan the index instead of seq scan the table? The index is only 66MB, the table is 1123MB. I'm new to this list and also I just recently started to tune postgresql so please forgive me if this is a dumb question. Regards, Laszlo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Poor performance on seq scan
Laszlo Nagy wrote: I made another test. I create a file with the identifiers and names of the products: psql#\o products.txt psql#select id,name from product; Then I can search using grep: grep "Mug" products.txt | cut -f1 -d\| There is a huge difference. This command runs within 0.5 seconds. That is, at least 76 times faster than the seq scan. It is the same if I vacuum, backup and restore the database. I thought that the table is stored in one file, and the seq scan will be actually faster than grepping the file. Can you please tell me what am I doing wrong? I'm not sure if I can increase the performance of a seq scan by adjusting the values in postgresql.conf. I do not like the idea of exporting the product table periodically into a txt file, and search with grep. :-) Is there any other columns besides id and name in the table? How big is products.txt compared to the heap file? Another question: I have a btree index on product(name). It contains all product names and the identifiers of the products. Wouldn't it be easier to seq scan the index instead of seq scan the table? The index is only 66MB, the table is 1123MB. Probably, but PostgreSQL doesn't know how to do that. Even if it did, it depends on how many matches there is. If you scan the index and then fetch the matching rows from the heap, you're doing random I/O to the heap. That becomes slower than scanning the heap sequentially if you're going to get more than a few hits. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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
Re: [PERFORM] Poor performance on seq scan
Lazlo, > Meanwhile, "iostat 5" gives something like this: > > tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id >1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0 >0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0 This is your problem. Do the following and report the results here: Take the number of GB of memory you have (say 2 for 2GB), multiply it by 25. This is the number of 8KB pages you can fit in twice your ram. Let's say you have 2GB - the result is 500,000. Use that number to do the following test on your database directory: time bash -c "dd if=/dev/zero of=//bigfile bs=8k count= && sync" Then do this: time bash -c "dd if=//bigfile of=/dev/null bs=8k" > > I made another test. I create a file with the identifiers and > names of the products: > > psql#\o products.txt > psql#select id,name from product; > > Then I can search using grep: > > grep "Mug" products.txt | cut -f1 -d\| > > There is a huge difference. This command runs within 0.5 > seconds. That is, at least 76 times faster than the seq scan. The file probably fits in the I/O cache. Your disks will at most go between 60-80MB/s, or from 5-7 times faster than what you see now. RAID 1 with one query will only deliver one disk worth of bandwidth. - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Poor performance on seq scan
Luke Lonergan írta: Lazlo, Meanwhile, "iostat 5" gives something like this: tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0 0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0 This is your problem. Do the following and report the results here: Take the number of GB of memory you have (say 2 for 2GB), multiply it by 25. This is the number of 8KB pages you can fit in twice your ram. Let's say you have 2GB - the result is 500,000. Use that number to do the following test on your database directory: time bash -c "dd if=/dev/zero of=//bigfile bs=8k count= && sync" I have 1GB RAM. The data directory is in /usr/local/pgsql/data. The root of this fs is /usr. time sh -c "dd if=/dev/zero of=/usr/test/bigfile bs=8k count=25 && sync " 25+0 records in 25+0 records out 204800 bytes transferred in 48.030627 secs (42639460 bytes/sec) 0.178u 8.912s 0:48.31 18.7% 9+96k 37+15701io 0pf+0w Then do this: time bash -c "dd if=//bigfile of=/dev/null bs=8k" time sh -c "dd if=/usr/test/bigfile of=/dev/null bs=8k" 25+0 records in 25+0 records out 204800 bytes transferred in 145.293473 secs (14095609 bytes/sec) 0.110u 5.857s 2:25.31 4.1% 10+99k 32923+0io 0pf+0w At this point I thought there was another process reading doing I/O so I retried: 25+0 records in 25+0 records out 204800 bytes transferred in 116.395211 secs (17595226 bytes/sec) 0.137u 5.658s 1:56.51 4.9% 10+103k 29082+0io 0pf+1w and again: 25+0 records in 25+0 records out 204800 bytes transferred in 120.198224 secs (17038521 bytes/sec) 0.063u 5.780s 2:00.21 4.8% 10+98k 29776+0io 0pf+0w This is a mirrored disk with two SATA disks. In theory, writing should be slower than reading. Is this a hardware problem? Or is it that "sync" did not do the sync? Laszlo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Poor performance on seq scan
Heikki Linnakangas wrote: Is there any other columns besides id and name in the table? How big is products.txt compared to the heap file? Yes, many other columns. The products.txt is only 59MB. It is similar to the size of the index size (66MB). Another question: I have a btree index on product(name). It contains all product names and the identifiers of the products. Wouldn't it be easier to seq scan the index instead of seq scan the table? The index is only 66MB, the table is 1123MB. Probably, but PostgreSQL doesn't know how to do that. Even if it did, it depends on how many matches there is. If you scan the index and then fetch the matching rows from the heap, you're doing random I/O to the heap. That becomes slower than scanning the heap sequentially if you're going to get more than a few hits. I have 700 000 rows in the table, and usually there are less than 500 hits. So probably using a "seq index scan" would be faster. :-) Now I also tried this: create table test(id int8 not null primary key, name text); insert into test select id,name from product; And then: zeusd1=> explain analyze select id,name from test where name like '%Tiffany%'; QUERY PLAN - Seq Scan on test (cost=0.00..26559.62 rows=79 width=40) (actual time=36.595..890.903 rows=117 loops=1) Filter: (name ~~ '%Tiffany%'::text) Total runtime: 891.063 ms (3 rows) But this might be coming from the disk cache. Thank you for your comments. We are making progress. Laszlo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Poor performance on seq scan
Laszlo Nagy writes: > This is a mirrored disk with two SATA disks. In theory, writing should > be slower than reading. Is this a hardware problem? Or is it that > "sync" did not do the sync? SATA disks are supposed to be capable of lying to pg's fsync (pg asking the kernel to synchronize a write and waiting until it is finished). Same can probably happen to the "sync" command. -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Poor performance on seq scan
Laszlo Nagy writes: > > Probably, but PostgreSQL doesn't know how to do that. Even if it > > did, it depends on how many matches there is. If you scan the index > > and then fetch the matching rows from the heap, you're doing random > > I/O to the heap. That becomes slower than scanning the heap > > sequentially if you're going to get more than a few hits. > I have 700 000 rows in the table, and usually there are less than 500 > hits. So probably using a "seq index scan" would be faster. :-) Now I You can confirm this idea by temporarily disabling sequential scans. Have a look at this chapter: http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-QUERY -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Poor performance on seq scan
Heikki Linnakangas wrote: > Guillaume Cottenceau wrote: > >Laszlo Nagy writes: > > > >>>Probably, but PostgreSQL doesn't know how to do that. Even if it > >>>did, it depends on how many matches there is. If you scan the index > >>>and then fetch the matching rows from the heap, you're doing random > >>>I/O to the heap. That becomes slower than scanning the heap > >>>sequentially if you're going to get more than a few hits. > >>> > >>I have 700 000 rows in the table, and usually there are less than 500 > >>hits. So probably using a "seq index scan" would be faster. :-) Now I > >> > > > >You can confirm this idea by temporarily disabling sequential > >scans. Have a look at this chapter: > > I don't think it will anyway do a "seq index scan" as Laszlo envisions. > PostgreSQL cannot do "fetch index tuple and apply %Mug% to it. If it > matches, fetch heap tuple". Even if you disable sequential scans, it's > still going to fetch every heap tuple to see if it matches "%Mug%". It's > just going to do it in index order, which is slower than a seq scan. Are you saying that an indexscan "Filter" only acts after getting the heap tuple? If that's the case, then there's room for optimization here, namely if the affected column is part of the index key, then we could do the filtering before fetching the heap tuple. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Poor performance on seq scan
Alvaro Herrera wrote: Are you saying that an indexscan "Filter" only acts after getting the heap tuple? If that's the case, then there's room for optimization here, namely if the affected column is part of the index key, then we could do the filtering before fetching the heap tuple. That's right. Yes, there's definitely room for optimization. In general, it seems we should detach the index scan and heap fetch more. Perhaps make them two different nodes, like the bitmap index scan and bitmap heap scan. It would allow us to do the above. It's also going to be necessary if we ever get to implement index-only scans. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: 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] Poor performance on seq scan
Guillaume Cottenceau wrote: Laszlo Nagy writes: Probably, but PostgreSQL doesn't know how to do that. Even if it did, it depends on how many matches there is. If you scan the index and then fetch the matching rows from the heap, you're doing random I/O to the heap. That becomes slower than scanning the heap sequentially if you're going to get more than a few hits. I have 700 000 rows in the table, and usually there are less than 500 hits. So probably using a "seq index scan" would be faster. :-) Now I You can confirm this idea by temporarily disabling sequential scans. Have a look at this chapter: I don't think it will anyway do a "seq index scan" as Laszlo envisions. PostgreSQL cannot do "fetch index tuple and apply %Mug% to it. If it matches, fetch heap tuple". Even if you disable sequential scans, it's still going to fetch every heap tuple to see if it matches "%Mug%". It's just going to do it in index order, which is slower than a seq scan. BTW: in addition to setting enable_seqscan=false, you probably have to add a dummy where-clause like "name > ''" to force the index scan. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance problem with Sarge compared with Woody
On Tue, 2006-09-12 at 02:18, Piñeiro wrote: > El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió: > > > Also also, you should be running at LEAST 7.4.13, the latest release of > > 7.4. It's possible there's a fix between 7.4.7 and 7.4.13 that fixes > > your problem. Doubt it, but it could be. However, the more important > > point is that there are REAL data eating bugs in 7.4.7 that may take a > > bite out of your data. > First, thanks for all your answers. > > About your comments: >* Yes, i have executed VACUUM FULL ANALYZE VERBOSE after the dump, > and after all my tries to solve this. > >* About another ERP: this ERP is one developed by us, we are > developing the next version, but until this is finished we need to > maintain the old one, with all his problems (as the "montrous" selects). I feel your pain. I've written a few apps that created queries on the fly that quickly grew into monstrosities that stomped my pg servers into the ground. >* About Postgre version: you advice me to upgrade from 7.4.7 (postgre > version at sarge) to 8.2. Well, I don't want to be a troll, but I > upgrade from 7.2.1 (woody) to 7.4.7 and I get worse, do you really think > that upgrade to 8.1 will solve something? It's likely that something in 7.4.7 is happening as a side effect. The 7.2.x query planner, if I remember correctly, did ALL The join ons first, then did the joins in the where clause in whatever order it thought best. Starting with 7.3 or 7.4 (not sure which) the planner was able to try and decide which tables in both the join on() syntax and with where clauses it wanted to run. Is it possible to fix the strangness of the ERP so it doesn't do that thing where it puts a lot of unconstrained tables in the middle of the from list? Also, moving where clause join condititions into the join on() syntax is usually a huge win. I'd probably put 8.1.4 (or the latest 8.2 snapshot) on a test box and see what it could do with this query for an afternoon. It might run just as slow, or it might "get it right" and run it in a few seconds. While there are the occasions where a query does run slower when migrating from an older version to a newer version, the opposite is usually true. From 7.2 to 7.4 there was a lot of work done in "getting things right" and some of this caused some things to go slower, although not much. >From 7.4 to 8.1 (and now 8.2) a lot of focus has been on optimizing the query planner and adding methods of joining that have made huge strides in performance. However, running 7.4.7 instead of 7.4.13 is a mistake, 100%. Updates happen for a reason, reasons like your data could get eaten, or the query planner makes a really stupid decision that causes it to take hours to run a query... You can upgrade from 7.4.7 to 7.4.13 in place, no need to dump and restore (take a backup just in case, but that's a given). > About the indices: > I comment previously that I think that the problem could be at the > indices. Well, at the woody postgre version we add all the indices by > hand, including the primary key index. The dump takes all these and > inserts at the sarge version, but sarge inserts an implicit index using > the primary key, so at the sarge version we have duplicate indices. Probably not a big issue. > There are any difference between 7.2.1 and 7.4.2 versions about this? > With the 7.4.2 there are more indices, or there was duplicated indices > with the woody version too? > (before you comment this: yes I try to remove the duplicate indices to > check if this was the problem) Wait, are you running 7.4.2 or 7.4.7? 7.4.7 is bad enough, but 7.4.2 is truly dangerous. Upgrade to 7.4.13 whichever version you're running. ---(end of broadcast)--- TIP 1: 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] Performance problem with Sarge compared with Woody
On Mon, 2006-09-11 at 20:53, Merlin Moncure wrote: > On 9/11/06, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > I'd suggest two things. > > > > one: Get a better ERP... :) or at least one you can inject some > > intelligence into, and two: upgrade to postgresql 8.1, or even 8.2 which > > will be released moderately soon, and if you won't be going into > > production directly, might be ready about the time you are. > > for 3 months I ran a 400M$ manufacturing company's erp off of a > pre-beta 8.0 windows pg server converted from cobol using some hacked > out c++ middleware. I remember having to change how the middleware > handled transactions when Alvaro changed them to a checkpoint > mechanism. I also remember being relieved when I no longer had to > manually edit pg_config.h so nobody would notice they would notice > they were running a beta version of postgresql had one of the > technical people casually logged into psql. I scraped out almost > completely unscathed except for a nasty crash due to low stack > allocation of the compiler on windows. > > the point of all this? get onto a recent version of postgresql, what > could possbily go wrong? You did notice I mentioned that it would only make sense if they weren't going into production right away. I.e. develop the app while pgdg develops the database, and release at about the same time. I wouldn't put 8.2 into production just yet, but if I had a launch date of next spring, I'd certainly consider developing on it now. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Poor performance on seq scan
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Are you saying that an indexscan "Filter" only acts after getting the > heap tuple? Correct. > If that's the case, then there's room for optimization > here, namely if the affected column is part of the index key, then we > could do the filtering before fetching the heap tuple. Only if the index is capable of disgorging the original value of the indexed column, a fact not in evidence in general (counterexample: polygons indexed by their bounding boxes in an r-tree). But yeah, it's interesting to think about applying filters at the index fetch step for index types that can hand back full values. This has been discussed before --- I think we had gotten as far as speculating about doing joins with just index values. See eg here: http://archives.postgresql.org/pgsql-hackers/2004-05/msg00944.php A lot of the low-level concerns have already been dealt with in order to support bitmap indexscans, but applying non-indexable conditions before fetching from the heap is still not done. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Reg - Autovacuum
Hi All I have installed a application with postgres-8.1.4 , I have to optimize the performance, As a measure i thought of enabling Auto commit , is it a right decision to take , If correct please suggest the steps that i need to follow in order to implement the Auto Vacuum. And also please suggest other steps that i need to improve the performance . Thanks and Regards Kris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance problem with Sarge compared with Woody
=?ISO-8859-1?Q?Pi=F1eiro?= <[EMAIL PROTECTED]> writes: >* About Postgre version: you advice me to upgrade from 7.4.7 (postgre > version at sarge) to 8.2. Well, I don't want to be a troll, but I > upgrade from 7.2.1 (woody) to 7.4.7 and I get worse, do you really think > that upgrade to 8.1 will solve something? If you really want informed answers rather than speculation, show us EXPLAIN ANALYZE reports for the problem query on both machines. I don't offhand know why 7.4 would be slower, but I speculate that it's picking a worse plan for some reason. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Reg - Autovacuum
> Hi All > I have installed a application with postgres-8.1.4 , I > have to optimize the performance, As a measure i thought of enabling > Auto commit , is it a right decision to take , If correct please suggest > the steps that i need to follow in order to implement the Auto Vacuum. http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM > > And also please suggest other steps that i need to > improve the performance . > http://www.powerpostgresql.com/PerfList Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance problem with Sarge compared with Woody
On 9/12/06, Scott Marlowe <[EMAIL PROTECTED]> wrote: On Mon, 2006-09-11 at 20:53, Merlin Moncure wrote: > for 3 months I ran a 400M$ manufacturing company's erp off of a > pre-beta 8.0 windows pg server converted from cobol using some hacked > out c++ middleware. I remember having to change how the middleware > handled transactions when Alvaro changed them to a checkpoint > mechanism. I also remember being relieved when I no longer had to > manually edit pg_config.h so nobody would notice they would notice > they were running a beta version of postgresql had one of the > technical people casually logged into psql. I scraped out almost > completely unscathed except for a nasty crash due to low stack > allocation of the compiler on windows. > > the point of all this? get onto a recent version of postgresql, what > could possbily go wrong? You did notice I mentioned that it would only make sense if they weren't going into production right away. I.e. develop the app while pgdg develops the database, and release at about the same time. I wouldn't put 8.2 into production just yet, but if I had a launch date of next spring, I'd certainly consider developing on it now. right, very good advice :) I was giving more of a "don't try this at home" type post. To the OP, though, I would advise that each version of PostgreSQL is much faster (sometimes, drastically so). Once in a while you get a query that you have to rethink but the engine improves with each release. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[Fwd: Re: [PERFORM] Performance problem with Sarge compared with Woody]
Sorry I answer the message only to Scott Marlowe. I re-send the response - Mensaje reenviado De: Piñeiro <[EMAIL PROTECTED]> Para: Scott Marlowe <[EMAIL PROTECTED]> Asunto: Re: [PERFORM] Performance problem with Sarge compared with Woody Fecha: Tue, 12 Sep 2006 17:36:41 +0200 El mar, 12-09-2006 a las 09:27 -0500, Scott Marlowe escribió: > On Tue, 2006-09-12 at 02:18, Piñeiro wrote: > > El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió: > The 7.2.x query planner, if I remember correctly, did ALL The join ons > first, then did the joins in the where clause in whatever order it > thought best. > > Starting with 7.3 or 7.4 (not sure which) the planner was able to try > and decide which tables in both the join on() syntax and with where > clauses it wanted to run. > > Is it possible to fix the strangness of the ERP so it doesn't do that > thing where it puts a lot of unconstrained tables in the middle of the > from list? Also, moving where clause join condititions into the join > on() syntax is usually a huge win. Well, I'm currently one of the new version of this ERP developer, but I'm a "recent adquisition" at the staff. I don't take part at the developing of the old version, and manage how the application creates this huge query could be a madness. > > I'd probably put 8.1.4 (or the latest 8.2 snapshot) on a test box and > see what it could do with this query for an afternoon. It might run > just as slow, or it might "get it right" and run it in a few seconds. > While there are the occasions where a query does run slower when > migrating from an older version to a newer version, the opposite is > usually true. From 7.2 to 7.4 there was a lot of work done in "getting > things right" and some of this caused some things to go slower, although > not much. I tried recently to execute this query on a database installed on a laptop with 256 MB RAM, ubuntu, and the 8.0.7 postgreSQL version, and I don't solve nothing... well the next try will be use 8.1.4 > > There are any difference between 7.2.1 and 7.4.2 versions about this? > > With the 7.4.2 there are more indices, or there was duplicated indices > > with the woody version too? > > (before you comment this: yes I try to remove the duplicate indices to > > check if this was the problem) > > Wait, are you running 7.4.2 or 7.4.7? 7.4.7 is bad enough, but 7.4.2 is > truly dangerous. Upgrade to 7.4.13 whichever version you're running. > Sorry a mistmatch, we are using the sarge postgre version, 7.4.7 -- Piñeiro <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [Fwd: Re: [PERFORM] Performance problem with Sarge compared
On Tue, 2006-09-12 at 11:06, Piñeiro wrote: > - Mensaje reenviado > De: Piñeiro <[EMAIL PROTECTED]> > Para: Scott Marlowe <[EMAIL PROTECTED]> > Asunto: Re: [PERFORM] Performance problem with Sarge compared with Woody > Fecha: Tue, 12 Sep 2006 17:36:41 +0200 > El mar, 12-09-2006 a las 09:27 -0500, Scott Marlowe escribió: > > On Tue, 2006-09-12 at 02:18, Piñeiro wrote: > > > El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió: > > > > The 7.2.x query planner, if I remember correctly, did ALL The join ons > > first, then did the joins in the where clause in whatever order it > > thought best. > > > > Starting with 7.3 or 7.4 (not sure which) the planner was able to try > > and decide which tables in both the join on() syntax and with where > > clauses it wanted to run. > > > > Is it possible to fix the strangness of the ERP so it doesn't do that > > thing where it puts a lot of unconstrained tables in the middle of the > > from list? Also, moving where clause join condititions into the join > > on() syntax is usually a huge win. > Well, I'm currently one of the new version of this ERP developer, but > I'm a "recent adquisition" at the staff. I don't take part at the > developing of the old version, and manage how the application creates > this huge query could be a madness. > > > > > I'd probably put 8.1.4 (or the latest 8.2 snapshot) on a test box and > > see what it could do with this query for an afternoon. It might run > > just as slow, or it might "get it right" and run it in a few seconds. > > While there are the occasions where a query does run slower when > > migrating from an older version to a newer version, the opposite is > > usually true. From 7.2 to 7.4 there was a lot of work done in "getting > > things right" and some of this caused some things to go slower, although > > not much. > > I tried recently to execute this query on a database installed on a > laptop with 256 MB RAM, ubuntu, and the 8.0.7 postgreSQL version, and I > don't solve nothing... well the next try will be use 8.1.4 OK, I'm gonna guess that 8.1 or 8.2 will likely not fix your problem, as it's likely that somewhere along the line the planner is making some inefficient unconstrained join on your data in some intermediate step. As Tom asked, post the explain analyze output for this query. I'm guessing there'll be a stage that is creating millions (possibly upon millions) of rows from a cross product. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Poor performance on seq scan
Lazlo, You can ignore tuning postgres and trying to use indexes, your problem is a bad hardware / OS configuration. The disks you are using should read 4-5 times faster than they are doing. Look to the SATA chipset driver in your FreeBSD config - perhaps upgrading your kernel would help. Still, the most you should expect is 5-6 times faster query than before. The data in your table is slightly larger than RAM. When you took it out of the DBMS it was smaller than RAM, so it fit in the I/O cache. With a text scan query you are stuck with a seqscan unless you use a text index like tsearch. Buy more disks and a Raid controller and use Raid5 or Raid10. - Luke Msg is shrt cuz m on ma treo -Original Message- From: Laszlo Nagy [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 12, 2006 08:16 AM Eastern Standard Time To: Luke Lonergan; pgsql-performance@postgresql.org Subject:Re: [PERFORM] Poor performance on seq scan Luke Lonergan írta: > Lazlo, > > >> Meanwhile, "iostat 5" gives something like this: >> >> tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id >>1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0 >>0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0 >> > > This is your problem. Do the following and report the results here: > > Take the number of GB of memory you have (say 2 for 2GB), multiply it by > 25. This is the number of 8KB pages you can fit in twice your ram. > Let's say you have 2GB - the result is 500,000. > > Use that number to do the following test on your database directory: > time bash -c "dd if=/dev/zero of=//bigfile bs=8k > count= && sync" > I have 1GB RAM. The data directory is in /usr/local/pgsql/data. The root of this fs is /usr. time sh -c "dd if=/dev/zero of=/usr/test/bigfile bs=8k count=25 && sync " 25+0 records in 25+0 records out 204800 bytes transferred in 48.030627 secs (42639460 bytes/sec) 0.178u 8.912s 0:48.31 18.7% 9+96k 37+15701io 0pf+0w > Then do this: > time bash -c "dd if=//bigfile of=/dev/null bs=8k" > time sh -c "dd if=/usr/test/bigfile of=/dev/null bs=8k" 25+0 records in 25+0 records out 204800 bytes transferred in 145.293473 secs (14095609 bytes/sec) 0.110u 5.857s 2:25.31 4.1% 10+99k 32923+0io 0pf+0w At this point I thought there was another process reading doing I/O so I retried: 25+0 records in 25+0 records out 204800 bytes transferred in 116.395211 secs (17595226 bytes/sec) 0.137u 5.658s 1:56.51 4.9% 10+103k 29082+0io 0pf+1w and again: 25+0 records in 25+0 records out 204800 bytes transferred in 120.198224 secs (17038521 bytes/sec) 0.063u 5.780s 2:00.21 4.8% 10+98k 29776+0io 0pf+0w This is a mirrored disk with two SATA disks. In theory, writing should be slower than reading. Is this a hardware problem? Or is it that "sync" did not do the sync? Laszlo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Poor performance on seq scan
Laszlo Nagy <[EMAIL PROTECTED]> writes: > Meanwhile, "iostat 5" gives something like this: > tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id >1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0 >0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0 >0 12 125.66 128 15.75 125.26 128 15.68 10 0 85 6 0 >0 12 124.66 129 15.67 124.39 129 15.64 12 0 85 3 0 >0 12 117.13 121 13.87 117.95 121 13.96 12 0 84 5 0 >0 12 104.84 118 12.05 105.84 118 12.19 10 0 87 2 0 Why is that showing 85+ percent *system* CPU time?? I could believe a lot of idle CPU if the query is I/O bound, or a lot of user time if PG was being a hog about doing the ~~ comparisons (not too unlikely BTW). But if the kernel is eating all the CPU, there's something very wrong, and I don't think it's Postgres' fault. regards, tom lane ---(end of broadcast)--- TIP 1: 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
[PERFORM] tsearch2 question (was: Poor performance on seq scan)
Tom Lane wrote:
Only if the index is capable of disgorging the original value of the
indexed column, a fact not in evidence in general (counterexample:
polygons indexed by their bounding boxes in an r-tree). But yeah,
it's interesting to think about applying filters at the index fetch
step for index types that can hand back full values. This has been
discussed before --- I think we had gotten as far as speculating about
doing joins with just index values. See eg here:
http://archives.postgresql.org/pgsql-hackers/2004-05/msg00944.php
A lot of the low-level concerns have already been dealt with in order to
support bitmap indexscans, but applying non-indexable conditions before
fetching from the heap is still not done.
To overcome this problem, I created a smaller "shadow" table:
CREATE TABLE product_search
(
id int8 NOT NULL,
name_desc text,
CONSTRAINT pk_product_search PRIMARY KEY (id)
);
insert into product_search
select
id,
name || ' ' || coalesce(description,'')
from product;
Obviously, this is almost like an index, but I need to maintain it
manually. I'm able to search with
zeusd1=> explain analyze select id from product_search where name_desc
like '%Mug%';
QUERY PLAN
Seq Scan on product_search (cost=0.00..54693.34 rows=36487 width=8)
(actual time=20.036..2541.971 rows=91399 loops=1)
Filter: (name_desc ~~ '%Mug%'::text)
Total runtime: 2581.272 ms
(3 rows)
The total runtime remains below 3 sec in all cases. Of course I still
need to join the main table to the result:
explain analyze select s.id,p.name from product_search s inner join
product p on (p.id = s.id) where s.name_desc like '%Tiffany%'
QUERY PLAN
Nested Loop (cost=0.00..55042.84 rows=58 width=40) (actual
time=164.437..3982.610 rows=117 loops=1)
-> Seq Scan on product_search s (cost=0.00..54693.34 rows=58
width=8) (actual time=103.651..2717.914 rows=117 loops=1)
Filter: (name_desc ~~ '%Tiffany%'::text)
-> Index Scan using pk_product_id on product p (cost=0.00..6.01
rows=1 width=40) (actual time=10.793..10.796 rows=1 loops=117)
Index Cond: (p.id = "outer".id)
Total runtime: 4007.283 ms
(6 rows)
Took 4 seconds. Awesome! With the original table, it used to be one or
two minutes!
Now you can ask, why am I not using tsearch2 for this? Here is answer:
CREATE TABLE product_search
(
id int8 NOT NULL,
ts_name_desc tsvector,
CONSTRAINT pk_product_search PRIMARY KEY (id)
);
insert into product_search
select
id,
to_tsvector(name || ' ' coalesce(description,''))
from product;
CREATE INDEX idx_product_search_ts_name_desc ON product_search USING
gist (ts_name_desc);
VACUUM product_search;
zeusd1=> explain analyze select id from product_search where
ts_name_desc @@ to_tsquery('mug');
QUERY
PLAN
---
Bitmap Heap Scan on product_search (cost=25.19..3378.20 rows=912
width=8) (actual time=954.669..13112.009 rows=91434 loops=1)
Filter: (ts_name_desc @@ '''mug'''::tsquery)
-> Bitmap Index Scan on idx_product_search_ts_name_desc
(cost=0.00..25.19 rows=912 width=0) (actual time=932.455..932.455
rows=91436 loops=1)
Index Cond: (ts_name_desc @@ '''mug'''::tsquery)
Total runtime: 13155.724 ms
(5 rows)
zeusd1=> explain analyze select id from product_search where
ts_name_desc @@ to_tsquery('tiffany');
QUERY PLAN
Bitmap Heap Scan on product_search (cost=25.19..3378.20 rows=912
width=8) (actual time=13151.725..13639.112 rows=76 loops=1)
Filter: (ts_name_desc @@ '''tiffani'''::tsquery)
-> Bitmap Index Scan on idx_product_search_ts_name_desc
(cost=0.00..25.19 rows=912 width=0) (actual time=13123.705..13123.705
rows=81 loops=1)
Index Cond: (ts_name_desc @@ '''tiffani'''::tsquery)
Total runtime: 13639.478 ms
(5 rows)
At least 13 seconds, and the main table is not joined yet. Can anybody
explain to me, why the seq scan is faster than the bitmap index? In the
last example there were only 81 rows returned, but it took more than 13
seconds. :( Even if the whole table can be cached into memory (which
isn't the case), the bitmap index should be much faster. Probably t
Re: [PERFORM] Poor performance on seq scan
Tom Lane wrote: Why is that showing 85+ percent *system* CPU time?? I could believe a lot of idle CPU if the query is I/O bound, or a lot of user time if PG was being a hog about doing the ~~ comparisons (not too unlikely BTW). I'm sorry, this was really confusing. I don't know what it was - probably a background system process, started from cron (?). I retried the same query and I got this: zeusd1=> explain analyze select id,name from product where name like '%Mug%'; QUERY PLAN Seq Scan on product (cost=0.00..206891.34 rows=36487 width=40) (actual time=17.188..44585.176 rows=91399 loops=1) Filter: (name ~~ '%Mug%'::text) Total runtime: 44631.150 ms (3 rows) tty ad4 ad6 cpu tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 0 62 115.25 143 16.06 116.03 143 16.17 3 0 9 3 85 0 62 122.11 144 17.12 121.78 144 17.07 6 0 3 2 89 0 62 126.18 158 19.45 125.86 157 19.28 5 0 11 6 79 0 62 126.41 131 16.13 127.52 132 16.39 5 0 9 6 80 0 62 127.80 159 19.81 126.89 158 19.55 5 0 9 0 86 0 62 125.29 165 20.15 126.26 165 20.30 5 0 14 2 80 0 62 127.22 164 20.32 126.74 165 20.37 5 0 9 0 86 0 62 121.34 150 17.75 120.76 149 17.54 1 0 13 3 82 0 62 121.40 143 16.92 120.33 144 16.89 5 0 11 3 82 0 62 127.38 154 19.12 127.17 154 19.09 8 0 8 5 80 0 62 126.88 129 15.95 127.00 128 15.84 5 0 9 5 82 0 62 118.48 121 13.97 119.28 121 14.06 6 0 17 3 74 0 62 127.23 146 18.10 126.79 146 18.04 9 0 20 2 70 0 62 127.27 153 18.98 128.00 154 19.21 5 0 17 0 79 0 62 127.02 130 16.09 126.28 130 16.00 10 0 16 3 70 0 62 123.17 125 15.00 122.40 125 14.91 5 0 14 2 80 0 62 112.37 130 14.24 112.62 130 14.27 0 0 14 3 83 0 62 115.83 138 15.58 113.97 138 15.33 3 0 18 0 79 A bit better transfer rate, but nothing serious. Regards, Laszlo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [Fwd: Re: [PERFORM] Performance problem with Sarge compared
El mar, 12-09-2006 a las 11:20 -0500, Scott Marlowe escribió:
> As Tom asked, post the explain analyze output for this query. I'm
> guessing there'll be a stage that is creating millions (possibly upon
> millions) of rows from a cross product.
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
Well, yes, it is a friend, but as the select at postgre Sarge version
never finished I can't use a explain analyze. I show you the explain,
with the hope that someone has any idea, but i think that this is almost
indecipherable (if you want the Woody ones i can post the explain
analyze). Thanks in advance.
*
**
QUERY PLAN
Unique (cost=91324.61..91324.88 rows=3 width=294)
-> Sort (cost=91324.61..91324.62 rows=3 width=294)
Sort Key: numerofacturafactura, codigofacturafactura,
codigoempresafactura, codigotiendafactura, estadofactura,
fechaemisionfactura, tipoivafactura, baseimponiblemodificadafactura,
baseimponiblenuevafactura, refacturafactura, codigopartyparticipantshop,
nombreparticipantshop, codigopartyparticipantpagador,
nickparticipantpagador, shortnameparticipantpagador,
cifparticipantpagador, codigoreparacionrepair, codigotiendarepair,
codigoclienterepair, codigocompaniarepair, codigoautoarteshop,
codigopartyparticipantenter, nombreparticipantcompany,
shortnameparticipantcompany, codigopartyparticipantcompany,
cifparticipantcompany, codigopagopago, codigobancopago,
codigooficinapago, numerocuentapago, esaplazospago, pagosrealizadospago,
numerovencimientospago, fechainiciopago, esdomiciliacionpago
-> Append (cost=27613.94..91324.59 rows=3 width=294)
-> Subquery Scan "*SELECT* 1" (cost=27613.94..27613.96
rows=1 width=294)
-> Sort (cost=27613.94..27613.95 rows=1
width=294)
Sort Key: participantecompany.nombre,
facturaabono.numerofactura
-> Nested Loop (cost=21240.09..27613.93
rows=1 width=294)
-> Hash Join (cost=21240.09..27609.14
rows=1 width=230)
Hash Cond: (("outer".codigotienda
= "inner".codigoparty) AND ("outer".codigoempresa =
"inner".codigoempresa) AND ("outer".codigoreparacion =
"inner".codigoreparacion))
-> Merge Right Join
(cost=2381.66..8569.33 rows=12091 width=119)
Merge Cond:
(("outer".codigoempresa = "inner".codigoempresa) AND
("outer".codigotienda = "inner".codigotienda) AND ("outer".codigopago =
"inner".codigopago))
-> Index Scan using
codigopago_pk on pago (cost=0.00..5479.51 rows=77034 width=56)
Re: [PERFORM] tsearch2 question (was: Poor performance on seq
Lazlo, On 9/12/06 10:01 AM, "Laszlo Nagy" <[EMAIL PROTECTED]> wrote: > zeusd1=> explain analyze select id from product_search where name_desc > like '%Mug%'; >QUERY PLAN > -- > -- > Seq Scan on product_search (cost=0.00..54693.34 rows=36487 width=8) > (actual time=20.036..2541.971 rows=91399 loops=1) >Filter: (name_desc ~~ '%Mug%'::text) > Total runtime: 2581.272 ms > (3 rows) > > The total runtime remains below 3 sec in all cases. By creating a table with only the name field you are searching, you have just reduced the size of rows so that they fit in memory. That is why your query runs faster. If your searched data doesn't grow, this is fine. If it does, you will need to fix your disk drive OS problem. - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [Fwd: Re: [PERFORM] Performance problem with Sarge compared
> From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Piñeiro > > TIP 6: explain analyze is your friend > Well, yes, it is a friend, but as the select at postgre Sarge version > never finished I can't use a explain analyze. I show you the explain, > with the hope that someone has any idea, but i think that > this is almost > indecipherable (if you want the Woody ones i can post the explain > analyze). Thanks in advance. Does the machine run out of disk space every time? Is it possible to try the query on a different machine with more hard drive room? An explain analyze of the slow plan will be much more helpful than an explain, even if its from a different machine. If its generating a large temp file, it is another sign that the query is doing some kind of large cross product. ---(end of broadcast)--- TIP 1: 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: [Fwd: Re: [PERFORM] Performance problem with Sarge compared
Piñeiro wrote: > El mar, 12-09-2006 a las 11:20 -0500, Scott Marlowe escribió: > > As Tom asked, post the explain analyze output for this query. I'm > > guessing there'll be a stage that is creating millions (possibly upon > > millions) of rows from a cross product. > > > Well, yes, it is a friend, but as the select at postgre Sarge version > never finished I can't use a explain analyze. I show you the explain, > with the hope that someone has any idea, but i think that this is almost > indecipherable (if you want the Woody ones i can post the explain > analyze). Thanks in advance. The only advice I can give you at this point is to provide both the EXPLAIN output and the query itself in formats more easily readable for those that could help you. This EXPLAIN you post below is totally whitespace-mangled, making it much harder to read than it should be; and the query you posted, AFAICS, is a continuous stream of lowercase letters. The EXPLAIN would be much better if you posted it as an attachment; and the query would be much better if you separated the logically distinct clauses in different lines, with clean indentation, using uppercase for the SQL keywords (SELECT, FROM, WHERE, etc). That way you're more likely to get useful responses. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM]
Hello All I am getting this message in my log files for my database. LOG: out of file descriptors: Too many open files; release and retry. At some point the memomy didn't get released and the postmaster reset itself terminating all client connections. I am not sure what direction to go. I can increase the file-max in the kernel but it looks reasonably sized already . Or decrease the max_file_per_process. Has anyone on the list encountered this issue. I am running Postgres 7.4.7. Thanks John Allgood ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Poor performance on seq scan
Laszlo Nagy <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Why is that showing 85+ percent *system* CPU time?? > I'm sorry, this was really confusing. I don't know what it was - > probably a background system process, started from cron (?). I retried > the same query and I got this: > [ around 80% idle CPU, 10% system, < 10% user ] OK, so then the thing really is I/O bound, and Luke is barking up the right tree. The system CPU percentage still seems high though. I wonder if there is a software aspect to your I/O speed woes ... could the thing be doing PIO instead of DMA for instance? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Bad plan for join to aggregate of join.
PG 8.0.3 is choosing a bad plan between a query.
I'm going to force the plan (by making one join into a function).
I'd like to know if this is unexpected; in general,
can PG see that a join on an grouped-by field
can be pushed down into the query as an indexable filter?
The query below joins a table "message", to an aggregate of
"message_recipient" joined to "recipient". The joins are all on
indexed PK-FK columns. "message_recipient" is an intersect table.
message :<: message_recipient :>: recipient
In the query plan below, the right side of the join returns one row of "message",
and PG knows it.
The left side of the join compute the entire aggregate of "message_recipient"
(est 700K rows), then does a merge join against the single message row.
I would have hoped for a nested-loop join, where the message "id"
field would be used to index-scan "message_recipient",
which in turn would index-scan "recipient" by recipient "id".
This is PG 8.0.3. All tables have been (very) recently analyzed.
The query plans estimated rowcounts all look bang-on.
"message" and "message_recipient" are tables of about 3M rows each.
As usual, this is on a system to which I only have restricted access.
But I'd be happy to expand on the info below with anything short of
the pg_dump.
---
EXPLAIN
SELECT message.id AS m_db_id, message.m_global_id AS id, m_global_id,
m_queue_id, h_message_id,
m_date AS c_date_iso, m_date, c_subject_utf8, message.reason_id AS
reason_id,
m_reason.name AS m_reason, m_spam_probability, m_spam_level, h_to,
m_message_size,
m_header_size, date_part('epoch', message.m_date) AS c_qdate_time,
h_from_local || '@' || h_from_domain AS h_from,
env_from_local || '@' || env_from_domain AS env_from,
env_from_local || '@' || env_from_domain AS m_envelope_from,
location_name AS location,
m_milter_host, m_relay, virus_name AS m_virus_name, m_all_recipients
FROM message
JOIN m_reason ON message.reason_id = m_reason.reason_id
JOIN message_all_recipients ON message.id = message_all_recipients.m_id
WHERE message.m_global_id = '2211000-1';
QUERY PLAN
---
Nested Loop (cost=254538.42..283378.44 rows=1 width=425)
Join Filter: ("outer".reason_id = "inner".reason_id)
-> Merge Join (cost=254538.42..283377.33 rows=1 width=416)
Merge Cond: ("outer".m_id = "inner".id)
-> Subquery Scan message_all_recipients (cost=254535.40..281604.95
rows=707735 width=40)
-> GroupAggregate (cost=254535.40..274527.60 rows=707735 width=36)
-> Sort (cost=254535.40..258250.57 rows=1486069 width=36)
Sort Key: message_recipient.message_id
-> Merge Join (cost=0.00..78970.52 rows=1486069 width=36)
Merge Cond: ("outer".id = "inner".recipient_id)
-> Index Scan using pk_recipient on recipient
(cost=0.00..5150.65 rows=204514 width=36)
-> Index Scan using pk_message_recipient on
message_recipient (cost=0.00..56818.25 rows=1486069 width=16)
Filter: (is_mapped = 1)
-> Sort (cost=3.02..3.03 rows=1 width=384)
Sort Key: message.id
-> Index Scan using unq_message_m_global_id on message
(cost=0.00..3.01 rows=1 width=384)
Index Cond: ((m_global_id)::text = '2211000-1'::text)
-> Seq Scan on m_reason (cost=0.00..1.04 rows=4 width=13)
--- Relevant tables and view:
# \d message
Table "public.message"
Column |Type |
Modifiers
+-+-
id | bigint | not null default
nextval('public.message_id_seq'::text)
m_global_id| character varying(255) | not null
reason_id | smallint| not null
location_name | character varying(255) | not null
m_date | timestamp without time zone |
m_queue_id | character varying(255) |
h_message_id | character varying(255) |
c_subject_utf8 | character varying(255) |
env_from_local | character varying(255) |
env_from_domain| character varying(255) |
h_from_local | character varying(255) |
h_from_domain | character varying(255) |
h_from | character varying(255) |
h_to | character varying(255) |
m_milter_host | character varying(255) |
m_relay| character varying(255) |
m_spam_probability | double precision|
m_message_size | integer |
m_header_size
Re: [PERFORM] Poor performance on seq scan
tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0 0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0 0 12 125.66 128 15.75 125.26 128 15.68 10 0 85 6 0 0 12 124.66 129 15.67 124.39 129 15.64 12 0 85 3 0 0 12 117.13 121 13.87 117.95 121 13.96 12 0 84 5 0 0 12 104.84 118 12.05 105.84 118 12.19 10 0 87 2 0 Why is that showing 85+ percent *system* CPU time?? I could believe a lot of idle CPU if the query is I/O bound, or a lot of user time if PG was being a hog about doing the ~~ comparisons (not too unlikely BTW). But if the kernel is eating all the CPU, there's something very wrong, and I don't think it's Postgres' fault. There IS a bug for SATA disk drives in some versions of the Linux kernel. On a lark I ran some of the I/O tests in this thread, and much to my surprise discovered my write speed was 6 MB/sec ... ouch! On an identical machine, different kernel, the write speed was 54 MB/sec. A couple of hours of research turned up this: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=168363 The fix for me was to edit /boot/grub/grub.conf, like this: kernel /vmlinuz-2.6.12-1.1381_FC3 ro root=LABEL=/ rhgb quiet \ ramdisk_size=1200 ide0=noprobe ide1=noprobe Notice the "ideX=noprobe". Instant fix -- after reboot the disk write speed jumped to what I expected. Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Poor performance on seq scan
Craig A. James wrote: There IS a bug for SATA disk drives in some versions of the Linux kernel. On a lark I ran some of the I/O tests in this thread, and much to my surprise discovered my write speed was 6 MB/sec ... ouch! On an identical machine, different kernel, the write speed was 54 MB/sec. My disks are running in SATA150 mode. Whatever it means. I'm using FreeBSD, and not just because it dynamically alters the priority of long running processes. :-) Laszlo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Performance With Joins on Large Tables
I am having problems performing a join on two large tables. It seems to only
want to use a sequential scan on the join, but that method seems to be slower
than an index scan. I've never actually had it complete the sequential scan
because I stop it after 24+ hours. I've run joins against large tables before
and an index scan was always faster (a few hours at the most).
Here is some information on the two tables:
data=# analyze view_505;
ANALYZE
data=# analyze r3s169;
ANALYZE
data=# \d view_505
Table "public.view_505"
Column | Type | Modifiers
--+---+---
dsiacctno| numeric |
name | boolean |
title| boolean |
company | boolean |
zip4 | boolean |
acceptcall | boolean |
phonedirect | smallint |
phonetollfree| smallint |
fax | smallint |
editdrop | boolean |
postsuppress | boolean |
firstnameinit| boolean |
prefix | integer |
crrt | boolean |
dpbc | boolean |
executive| integer |
addressline | integer |
multibuyer | integer |
activemultibuyer | integer |
active | boolean |
emails | integer |
domains | integer |
zip1 | character varying(1) |
zip3 | character varying(3) |
gender | character varying(1) |
topdomains | bit varying |
city | character varying(35) |
state| character varying(35) |
zip | character varying(20) |
country | character varying(30) |
selects | bit varying |
files| integer[] |
sics | integer[] |
custdate | date |
Indexes:
"view_505_city" btree (city)
"view_505_dsiacctno" btree (dsiacctno)
"view_505_state" btree (state)
"view_505_zip" btree (zip)
"view_505_zip1" btree (zip1)
"view_505_zip3" btree (zip3)
data=# \d r3s169
Table "public.r3s169"
Column| Type | Modifiers
-++---
dsiacctno | numeric|
fileid | integer|
customerid | character varying(20) |
email | character varying(100) |
sic2| character varying(2) |
sic4| character varying(4) |
sic6| character varying(6) |
custdate| date |
inqdate | date |
eentrydate | date |
esubdate| date |
efaildate | date |
eunlistdate | date |
pentrydate | date |
psubdate| date |
punlistdate | date |
pexpiredate | date |
lastupdate | date |
emaildrop | numeric|
sic8| character varying(8) |
Indexes:
"r3s169_dsiacctno" btree (dsiacctno)
data=# select count(*) from view_505;
count
---
112393845
(1 row)
data=# select count(*) from r3s169;
count
---
285230264
(1 row)
Here is what EXPLAIN says:
data=# EXPLAIN SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (YEAR FROM s.custdate) || '-' || EXTRACT (MONTH
FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s ON v.dsiacctno =
s.dsiacctno;
QUERY PLAN
---
Merge Join (cost=293767607.69..305744319.52 rows=285392608 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Sort (cost=127304933.87..127585815.71 rows=112352736 width=20)
Sort Key: v.dsiacctno
-> Seq Scan on view_505 v (cost=1.00..104604059.36
rows=112352736 width=20)
-> Sort (cost=166462673.82..167176155.34 rows=285392608 width=17)
Sort Key: s.dsiacctno
-> Seq Scan on r3s169 s (cost=1.00..106875334.08
rows=285392608 width=17)
(8 rows)
I can't really do and EXPLAIN ANALYZE because the query never really finishes.
Also, I use a cursor to loop through the data. view_505 isn't a pgsql view, its
just how we decided to name the table. There is a one to many
relationship between
view_505 and r3s169.
Since enable_seqscan is off, my understanding is that in order for the query
planner to user a sequential scan it must think there is no other alternative.
Both sides are indexed and anaylzed, so that confuses me a little.
I tried it on a smaller sample set of the data and it works fine:
data=# select * into r3s169_test from r3s169 limit 100;
SELE
Re: [PERFORM] Poor performance on seq scan
Lazlo, On 9/12/06 2:49 PM, "Laszlo Nagy" <[EMAIL PROTECTED]> wrote: > I'm using FreeBSD, and not just because it dynamically alters the > priority of long running processes. :-) Understood. Linux and FreeBSD often share some driver technology. I have had extremely bad performance historically with onboard SATA chipsets on Linux. The one exception has been with the Intel based chipsets (not the CPU, the I/O chipset). It is very likely that you are having problems with the driver for the chipset. Are you running RAID1 in hardware? If so, turn it off and see what the performance is. The onboard hardware RAID is worse than useless, it actually slows the I/O down. If you want RAID with onboard chipsets, use software RAID, or buy an adapter from 3Ware or Areca for $200. - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Bad plan for join to aggregate of join.
Mischa Sandberg <[EMAIL PROTECTED]> writes: > can PG see that a join on an grouped-by field > can be pushed down into the query as an indexable filter? No. The GROUP BY serves as a partial optimization fence. If you're concerned about the speed of this query, I recommend making a different view in which 'message' is joined inside the GROUP BY. regards, tom lane ---(end of broadcast)--- TIP 1: 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] Poor performance on seq scan
Laszlo Nagy wrote: Craig A. James wrote: There IS a bug for SATA disk drives in some versions of the Linux kernel. On a lark I ran some of the I/O tests in this thread, and much to my surprise discovered my write speed was 6 MB/sec ... ouch! On an identical machine, different kernel, the write speed was 54 MB/sec. My disks are running in SATA150 mode. Whatever it means. I'm using FreeBSD, and not just because it dynamically alters the priority of long running processes. :-) I dunno if this has been suggested, but try changing the sysctl vfs.read_max. The default is 8 and results in horrible RAID performance (having said that, not sure if RAID1 is effected, only striped RAID levels...), anyway try 16 or 32 and see if you seq IO rate improves at all (tho the underlying problem does look like a poor SATA chipset/driver combination). I also found that building your ufs2 filesystems with 32K blocks and 4K fragments improved sequential performance considerably (even for 8K reads). Cheers Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Bad plan for join to aggregate of join.
Tom Lane wrote: Mischa Sandberg <[EMAIL PROTECTED]> writes: can PG see that a join on an grouped-by field can be pushed down into the query as an indexable filter? No. The GROUP BY serves as a partial optimization fence. If you're concerned about the speed of this query, I recommend making a different view in which 'message' is joined inside the GROUP BY. Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM]
On Tue, Sep 12, 2006 at 03:33:08PM -0400, [EMAIL PROTECTED] wrote: > Hello All > > I am getting this message in my log files for my database. > > LOG: out of file descriptors: Too many open files; release and retry. > > At some point the memomy didn't get released and the postmaster reset itself > terminating all client connections. I am not sure what direction to go. I can > increase the file-max in the kernel but it looks reasonably sized already . > Or decrease the max_file_per_process. Has anyone on the list encountered this > issue. I am running Postgres 7.4.7. PostgreSQL could be using somewhere around as much as max_files_per_process * ( max_connections + 5 ), so make sure that matches file-max (the + 5 is because there are non-connection processes such as the bgwriter). If that looks OK, some file descriptors might have been left around from the crash... I know this can happen with shared memory segments. It normally won't happen with file descriptors, but perhaps it is possible. If that's the case, a reboot would certainly fix it. BTW, you should upgrade to the latest 7.4 release. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance With Joins on Large Tables
On Tue, Sep 12, 2006 at 04:17:34PM -0600, Joshua Marsh wrote:
> data=# EXPLAIN SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
> v.custdate), EXTRACT (YEAR FROM s.custdate) || '-' || EXTRACT (MONTH
> FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s ON v.dsiacctno =
> s.dsiacctno;
> QUERY PLAN
> ---
> Merge Join (cost=293767607.69..305744319.52 rows=285392608 width=11)
> Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
> -> Sort (cost=127304933.87..127585815.71 rows=112352736 width=20)
> Sort Key: v.dsiacctno
> -> Seq Scan on view_505 v (cost=1.00..104604059.36
> rows=112352736 width=20)
> -> Sort (cost=166462673.82..167176155.34 rows=285392608 width=17)
> Sort Key: s.dsiacctno
> -> Seq Scan on r3s169 s (cost=1.00..106875334.08
> rows=285392608 width=17)
> (8 rows)
>
>
> Since enable_seqscan is off, my understanding is that in order for the query
> planner to user a sequential scan it must think there is no other
> alternative.
> Both sides are indexed and anaylzed, so that confuses me a little.
>
> I tried it on a smaller sample set of the data and it works fine:
Actually, enable_seqscan=off just adds a fixed overhead to the seqscan
cost estimate. That's why the cost for the seqscans in that plan starts
at 1. I've suggested changing that to a variable overhead based
on the expected rowcount, but the counter-argument was that anyone with
so much data that the fixed amount wouldn't work would most likely be
having bigger issues anyway.
Other things you can try to get the index scan back would be to reduce
random_page_cost and to analyze the join fields in those tables with a
higher statistics target (though I'm not 100% certain the join cost
estimator actually takes that into account). Or if you don't mind
patching your source code, it wouldn't be difficult to make
enable_seqscan use a bigger 'penalty value' than 1000.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
