Re: [PERFORM] Table locking problems?
Dan Harris wrote: On Aug 9, 2005, at 3:51 PM, John A Meinel wrote: Dan Harris wrote: On Aug 10, 2005, at 12:49 AM, Steve Poe wrote: Dan, Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a clarification, since RAID 0 is still a single-point of failure even if RAID1 is on top of RAID0. Well, you tell me if I stated incorrectly. There are two raid enclosures with 7 drives in each. Each is on its own bus on a dual- channel controller. Each box has a stripe across its drives and the enclosures are mirrors of each other. I understand the controller could be a single point of failure, but I'm not sure I understand your concern about the RAID structure itself. In this configuration, if you have a drive fail on both controllers, the entire RAID dies. Lets label them A1-7, B1-7, because you stripe within a set, if a single one of A dies, and a single one of B dies, you have lost your entire mirror. The correct way of doing it, is to have A1 be a mirror of B1, and then stripe above that. Since you are using 2 7-disk enclosures, I'm not sure how you can do it well, since it is not an even number of disks. Though if you are using software RAID, there should be no problem. The difference is that in this scenario, *all* of the A drives can die, and you haven't lost any data. The only thing you can't lose is a matched pair (eg losing both A1 and B1 will cause complete data loss) I believe the correct notation for this last form is RAID 1 + 0 (RAID10) since you have a set of RAID1 drives, with a RAID0 on-top of them. I have read up on the difference now. I don't understand why it's a "single point of failure". Technically any array could be a "single point" depending on your level of abstraction. In retrospect, I probably should have gone 8 drives in each and used RAID 10 instead for the better fault-tolerance, but it's online now and will require some planning to see if I want to reconfigure that in the future. I wish HP's engineer would have promoted that method instead of 0+1.. I wouldn't say that it is a single point of failure, but I *can* say that it is much more likely to fail. (2 drives rather than on average n drives) If your devices will hold 8 drives, you could simply do 1 8-drive, and one 6-drive. And then do RAID1 with pairs, and RAID0 across the resultant 7 RAID1 sets. I'm really surprised that someone promoted RAID 0+1 over RAID10. I think I've heard that there is a possible slight performance improvement, but really the failure mode makes it a poor tradeoff. John =:-> -Dan signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Table locking problems?
On Aug 9, 2005, at 3:51 PM, John A Meinel wrote: Dan Harris wrote: On Aug 10, 2005, at 12:49 AM, Steve Poe wrote: Dan, Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a clarification, since RAID 0 is still a single-point of failure even if RAID1 is on top of RAID0. Well, you tell me if I stated incorrectly. There are two raid enclosures with 7 drives in each. Each is on its own bus on a dual- channel controller. Each box has a stripe across its drives and the enclosures are mirrors of each other. I understand the controller could be a single point of failure, but I'm not sure I understand your concern about the RAID structure itself. In this configuration, if you have a drive fail on both controllers, the entire RAID dies. Lets label them A1-7, B1-7, because you stripe within a set, if a single one of A dies, and a single one of B dies, you have lost your entire mirror. The correct way of doing it, is to have A1 be a mirror of B1, and then stripe above that. Since you are using 2 7-disk enclosures, I'm not sure how you can do it well, since it is not an even number of disks. Though if you are using software RAID, there should be no problem. The difference is that in this scenario, *all* of the A drives can die, and you haven't lost any data. The only thing you can't lose is a matched pair (eg losing both A1 and B1 will cause complete data loss) I believe the correct notation for this last form is RAID 1 + 0 (RAID10) since you have a set of RAID1 drives, with a RAID0 on-top of them. I have read up on the difference now. I don't understand why it's a "single point of failure". Technically any array could be a "single point" depending on your level of abstraction. In retrospect, I probably should have gone 8 drives in each and used RAID 10 instead for the better fault-tolerance, but it's online now and will require some planning to see if I want to reconfigure that in the future. I wish HP's engineer would have promoted that method instead of 0+1.. -Dan ---(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] Table locking problems?
Dan Harris wrote: On Aug 10, 2005, at 12:49 AM, Steve Poe wrote: Dan, Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a clarification, since RAID 0 is still a single-point of failure even if RAID1 is on top of RAID0. Well, you tell me if I stated incorrectly. There are two raid enclosures with 7 drives in each. Each is on its own bus on a dual- channel controller. Each box has a stripe across its drives and the enclosures are mirrors of each other. I understand the controller could be a single point of failure, but I'm not sure I understand your concern about the RAID structure itself. In this configuration, if you have a drive fail on both controllers, the entire RAID dies. Lets label them A1-7, B1-7, because you stripe within a set, if a single one of A dies, and a single one of B dies, you have lost your entire mirror. The correct way of doing it, is to have A1 be a mirror of B1, and then stripe above that. Since you are using 2 7-disk enclosures, I'm not sure how you can do it well, since it is not an even number of disks. Though if you are using software RAID, there should be no problem. The difference is that in this scenario, *all* of the A drives can die, and you haven't lost any data. The only thing you can't lose is a matched pair (eg losing both A1 and B1 will cause complete data loss) I believe the correct notation for this last form is RAID 1 + 0 (RAID10) since you have a set of RAID1 drives, with a RAID0 on-top of them. How many users are connected when your update / delete queries are hanging? Have you done an analyze verbose on those queries? Most of the traffic is from programs we run to do analysis of the data and managing changes. At the time I noticed it this morning, there were 10 connections open to the database. That rarely goes above 20 concurrent. As I said in my other response, I believe that the log will only contain the query at the point the query finishes, so if it never finishes... Have you made changes to the postgresql.conf? kernel.vm settings? IO scheduler? I set shmmax appropriately for my shared_buffers setting, but that's the only kernel tweak. If you're not doing so already, you may consider running sar (iostat) to monitor when the hanging occurs if their is a memory / IO bottleneck somewhere. I will try that. Thanks When you discover that an update is hanging, can you get into the database, and see what locks currently exist? (SELECT * FROM pg_locks) That might help you figure out what is being locked and possibly preventing your updates. It is also possible that your UPDATE query is trying to do something funny (someone just recently was talking about an UPDATE that wanted to do a hash join against 12M rows). Which probably meant that it had to spill to disk, where a merge join would have worked better. John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Table locking problems?
On Aug 10, 2005, at 12:49 AM, Steve Poe wrote: Dan, Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a clarification, since RAID 0 is still a single-point of failure even if RAID1 is on top of RAID0. Well, you tell me if I stated incorrectly. There are two raid enclosures with 7 drives in each. Each is on its own bus on a dual- channel controller. Each box has a stripe across its drives and the enclosures are mirrors of each other. I understand the controller could be a single point of failure, but I'm not sure I understand your concern about the RAID structure itself. How many users are connected when your update / delete queries are hanging? Have you done an analyze verbose on those queries? Most of the traffic is from programs we run to do analysis of the data and managing changes. At the time I noticed it this morning, there were 10 connections open to the database. That rarely goes above 20 concurrent. As I said in my other response, I believe that the log will only contain the query at the point the query finishes, so if it never finishes... Have you made changes to the postgresql.conf? kernel.vm settings? IO scheduler? I set shmmax appropriately for my shared_buffers setting, but that's the only kernel tweak. If you're not doing so already, you may consider running sar (iostat) to monitor when the hanging occurs if their is a memory / IO bottleneck somewhere. I will try that. Thanks ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Table locking problems?
On Aug 9, 2005, at 1:08 PM, Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: My experience is that when this type of thing happens it is typically specific queries that cause the problem. If you turn on statement logging you can get the exact queries and debug from there. Here are some things to look for: Is it a large table (and thus large indexes) that it is updating? Is the query using indexes? Is the query modifying ALOT of rows? Another thing to look at is foreign keys. Dan could be running into problems with an update on one side of an FK being blocked by locks on the associated rows on the other side. regards, tom lane Tom, Steve, Josh: Thank you for your ideas. The updates are only on a single table, no joins. I had stats collection turned off. I have turned that on again so that I can try and catch one while the problem is occurring. The last table it did this on was about 3 million records. 4 single-column indexes on it. The problem I had with statement logging is that if the query never finishes, it doesn't get logged as far as I can tell. So everything that did get logged was normal and would run with no isses in psql by copy and pasting it. The rows updated will certainly vary by query. I really need to "catch it in the act" with stats collection on so I can get the query from pg_stat_activity. Once I get it, I will play with explains and see if I can reproduce it outside the wild. Thanks again for your help. -Dan ---(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] Table locking problems?
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > My experience is that when this type of thing happens it is typically > specific queries that cause the problem. If you turn on statement > logging you can get the exact queries and debug from there. > Here are some things to look for: > Is it a large table (and thus large indexes) that it is updating? > Is the query using indexes? > Is the query modifying ALOT of rows? Another thing to look at is foreign keys. Dan could be running into problems with an update on one side of an FK being blocked by locks on the associated rows on the other side. regards, tom lane ---(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] Why hash join instead of nested loop?
Bingo, the smaller the sort_mem, the faster that query is. Thanks a lot to everybody that helped, i'll tweak with these values more when I get a chance now that I have some guidelines that make sense. Rhett On 8/9/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Rhett Garber <[EMAIL PROTECTED]> writes: > > Well that could be an issue, is this abnormally large: > > #shared_buffers = 1536 # min 16, at least max_connections*2, 8KB > > each > > shared_buffers = 206440 > > #sort_mem = 131072 # min 64, size in KB > > sort_mem = 524288 # min 64, size in KB > > vacuum_mem = 131072 # min 1024, size in K > > The vacuum_mem number is OK I think, but both of the others seem > unreasonably large. Conventional wisdom about shared_buffers is that > the sweet spot is maybe 1 or so buffers, rarely more than 5. > (Particularly in pre-8.0 releases, there are code paths that grovel > through all the buffers linearly, so there is a significant cost to > making it too large.) Don't worry about it being too small to make > effective use of RAM --- we rely on the kernel's disk cache to do that. > > sort_mem is *per sort*, and so half a gig in a machine with only a > couple of gig is far too much except when you know you have only one > query running. A couple dozen backends each trying to use half a gig > will drive you into the ground in no time. Conventional wisdom here > is that the global setting should be conservatively small (perhaps > 10Mb to 100Mb depending on how many concurrent backends you expect to > have), and then you can explicitly increase it locally with SET for > specific queries that need it. > > In terms of the problem at hand, try the test case with a few different > values of sort_mem (use SET to adjust it, you don't need to touch the > config file) and see what happens. I think the cost you're seeing is > just startup overhead to zero a hash table of a few hundred meg ... > > regards, tom lane > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Table locking problems?
Dan, Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a clarification, since RAID 0 is still a single-point of failure even if RAID1 is on top of RAID0. How many users are connected when your update / delete queries are hanging? Have you done an analyze verbose on those queries? Have you made changes to the postgresql.conf? kernel.vm settings? IO scheduler? If you're not doing so already, you may consider running sar (iostat) to monitor when the hanging occurs if their is a memory / IO bottleneck somewhere. Good luck. Steve Poe On Tue, 2005-08-09 at 12:04 -0600, Dan Harris wrote: > I thought I would send this to pg-performance since so many people > helped me with my speed issues recently. I was definitely IO- > bottlenecked. > > Since then, I have installed 2 RAID arrays with 7 15k drives in them > in RAID 0+1 as well as add a new controller card with 512MB of cache > on it. I also created this new partition on the RAID as XFS instead > of ext3. > > These changes have definitely improved performance, but I am now > finding some trouble with UPDATE or DELETE queries "hanging" and > never releasing their locks. As this happens, other statements queue > up behind it. It seems to occur at times of very high loads on the > box. Is my only option to kill the query ( which usually takes down > the whole postmaster with it! ouch ). > > Could these locking issues be related to the other changes I made? > I'm really scared that this is related to choosing XFS, but I sure > hope not. How should I go about troubleshooting the "problem" > queries? They don't seem to be specific to a single table or single > database. > > I'm running 8.0.1 on kernel 2.6.12-3 on 64-bit Opterons if that > matters.. > > > -Dan > > ---(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 ---(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] Why hash join instead of nested loop?
Rhett Garber <[EMAIL PROTECTED]> writes: > Well that could be an issue, is this abnormally large: > #shared_buffers = 1536 # min 16, at least max_connections*2, 8KB each > shared_buffers = 206440 > #sort_mem = 131072 # min 64, size in KB > sort_mem = 524288 # min 64, size in KB > vacuum_mem = 131072 # min 1024, size in K The vacuum_mem number is OK I think, but both of the others seem unreasonably large. Conventional wisdom about shared_buffers is that the sweet spot is maybe 1 or so buffers, rarely more than 5. (Particularly in pre-8.0 releases, there are code paths that grovel through all the buffers linearly, so there is a significant cost to making it too large.) Don't worry about it being too small to make effective use of RAM --- we rely on the kernel's disk cache to do that. sort_mem is *per sort*, and so half a gig in a machine with only a couple of gig is far too much except when you know you have only one query running. A couple dozen backends each trying to use half a gig will drive you into the ground in no time. Conventional wisdom here is that the global setting should be conservatively small (perhaps 10Mb to 100Mb depending on how many concurrent backends you expect to have), and then you can explicitly increase it locally with SET for specific queries that need it. In terms of the problem at hand, try the test case with a few different values of sort_mem (use SET to adjust it, you don't need to touch the config file) and see what happens. I think the cost you're seeing is just startup overhead to zero a hash table of a few hundred meg ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Table locking problems?
On Tue, Aug 09, 2005 at 12:04:11PM -0600, Dan Harris wrote: > These changes have definitely improved performance, but I am now > finding some trouble with UPDATE or DELETE queries "hanging" and > never releasing their locks. As this happens, other statements queue > up behind it. Have you examined pg_locks to see if the UPDATE or DELETE is blocked because of a lock another session holds? Are you using foreign keys? When updating referencing rows, released versions of PostgreSQL acquire a lock on the referenced row that can hurt concurrency or cause deadlock (this will be improved in 8.1). -- Michael Fuhr ---(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] Why hash join instead of nested loop?
Well that could be an issue, is this abnormally large: #shared_buffers = 1536 # min 16, at least max_connections*2, 8KB each shared_buffers = 206440 #sort_mem = 131072 # min 64, size in KB sort_mem = 524288 # min 64, size in KB vacuum_mem = 131072 # min 1024, size in K I actually had a lot of trouble finding example values for these... no one wants to give real numbers in any postgres performance tuning articles I saw. What would be appropriate for machines with 1 or 6 gigs of RAM and wanting to maximize performance. Rhett On 8/9/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Rhett Garber <[EMAIL PROTECTED]> writes: > > They are both running SuSE 8, 2.4.21-128-smp kernel > > > Compile instructions (I didn't do it myself) indicate we built from > > source with nothing fancy: > > You could double-check the configure options by running pg_config. > But probably the more interesting question is whether any nondefault > CFLAGS were used, and I don't think pg_config records that. > (Hmm, maybe it should.) > > In any case, there's no smoking gun there. I'm now wondering if maybe > there's something unusual about your runtime parameters. AFAIR you > didn't show us your postgresql.conf settings --- could we see any > nondefault entries there? > > (I looked quickly at the 7.4 hashjoin code, and I see that it uses a > hash table sized according to sort_mem even when the input is predicted > to be very small ... so an enormous sort_mem setting would account for > some plan startup overhead to initialize the table ...) > > regards, tom lane > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Why hash join instead of nested loop?
Rhett Garber <[EMAIL PROTECTED]> writes: > They are both running SuSE 8, 2.4.21-128-smp kernel > Compile instructions (I didn't do it myself) indicate we built from > source with nothing fancy: You could double-check the configure options by running pg_config. But probably the more interesting question is whether any nondefault CFLAGS were used, and I don't think pg_config records that. (Hmm, maybe it should.) In any case, there's no smoking gun there. I'm now wondering if maybe there's something unusual about your runtime parameters. AFAIR you didn't show us your postgresql.conf settings --- could we see any nondefault entries there? (I looked quickly at the 7.4 hashjoin code, and I see that it uses a hash table sized according to sort_mem even when the input is predicted to be very small ... so an enormous sort_mem setting would account for some plan startup overhead to initialize the table ...) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Table locking problems?
Could these locking issues be related to the other changes I made? I'm really scared that this is related to choosing XFS, but I sure hope not. How should I go about troubleshooting the "problem" queries? They don't seem to be specific to a single table or single database. My experience is that when this type of thing happens it is typically specific queries that cause the problem. If you turn on statement logging you can get the exact queries and debug from there. Here are some things to look for: Is it a large table (and thus large indexes) that it is updating? Is the query using indexes? Is the query modifying ALOT of rows? Of course there is also the RTFM of are you analyzing and vacuuming? Sincerely, Joshua D. Drake I'm running 8.0.1 on kernel 2.6.12-3 on 64-bit Opterons if that matters.. -Dan ---(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 -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Why hash join instead of nested loop?
> I'm now thinking you've got either a platform- or compiler-specific > problem. Exactly what is the hardware (the CPU not the disks)? How did > you build or come by the Postgres executables (compiler, configure > options, etc)? I've tried it on two of our machines, both HP Proliant DL580: Production: Intel(R) Xeon(TM) MP CPU 2.80GHz (I think there are 2 physical CPUs with Hyperthreading, shows up as 4) 6 gigs RAM Development: Intel(R) XEON(TM) MP CPU 2.00GHz (I have vague recollection of disabling hyperthreading on this chip because of some other kernel issue) 1 gig RAM They are both running SuSE 8, 2.4.21-128-smp kernel Compile instructions (I didn't do it myself) indicate we built from source with nothing fancy: tar xpvf postgresql-7.4.1.tar.bz2 cd postgresql-7.4.1 ./configure --prefix=/usr/local/postgresql-7.4.1 make make install make install-all-headers If i run 'file' on /usr/local/postgresql-7.4.1/bin/postgres : postgres: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), not stripped Thanks for all your help guys, Rhett ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Table locking problems?
I thought I would send this to pg-performance since so many people helped me with my speed issues recently. I was definitely IO- bottlenecked. Since then, I have installed 2 RAID arrays with 7 15k drives in them in RAID 0+1 as well as add a new controller card with 512MB of cache on it. I also created this new partition on the RAID as XFS instead of ext3. These changes have definitely improved performance, but I am now finding some trouble with UPDATE or DELETE queries "hanging" and never releasing their locks. As this happens, other statements queue up behind it. It seems to occur at times of very high loads on the box. Is my only option to kill the query ( which usually takes down the whole postmaster with it! ouch ). Could these locking issues be related to the other changes I made? I'm really scared that this is related to choosing XFS, but I sure hope not. How should I go about troubleshooting the "problem" queries? They don't seem to be specific to a single table or single database. I'm running 8.0.1 on kernel 2.6.12-3 on 64-bit Opterons if that matters.. -Dan ---(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] Why hash join instead of nested loop?
Rhett Garber <[EMAIL PROTECTED]> writes: > Duplicated your setup in a separate DB. > At least its reproducable for me. Hmm. Well, we now have several data points but they seem to be on wildly varying hardware. To try to normalize the results a little, I computed the total actual time for the hash plan divided by the sum of the actual times for the two scan nodes. Thus, for your example: > Hash Join (cost=4.83..5.91 rows=1 width=14) (actual time=7.148..7.159 > rows=1 loops=1) >Hash Cond: ("outer".id = "inner".obj2) >-> Seq Scan on rtmessagestate (cost=0.00..1.05 rows=5 width=14) > (actual time=0.007..0.015 rows=5 loops=1) >-> Hash (cost=4.83..4.83 rows=1 width=4) (actual > time=0.055..0.055 rows=0 loops=1) > -> Index Scan using connection_regid_obj1_index on > connection (cost=0.00..4.83 rows=1 width=4) (actual time=0.028..0.032 > rows=1 loops=1) >Index Cond: ((connection_registry_id = 40105) AND (obj1 > = 73582)) Total runtime: 7.693 ms > (7 rows) this would be 7.159 / (0.015 + 0.032). This is probably not an enormously robust statistic but it at least focuses attention in the right place. Here's what I get (rounded off to 4 digits which is surely as much precision as we have in the numbers): Tom 7.4.8+ 1.619 Ian 7.4.86.000 Ian 7.4.2 13.95 Steinar 7.4.78.833 Rhett orig 108.3 Rhett test 152.3 Michael 7.4.12.015 My number seems to be a bit of an outlier to the low side, but yours are way the heck to the high side. And Michael's test seems to rule out the idea that it's something broken in 7.4.1 in particular. I'm now thinking you've got either a platform- or compiler-specific problem. Exactly what is the hardware (the CPU not the disks)? How did you build or come by the Postgres executables (compiler, configure options, etc)? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Why hash join instead of nested loop?
Duplicated your setup in a separate DB. At least its reproducable for me. I tested this on a Xeon 2 Ghz, 1 Gig Ram. Its running on some shared storage array that I'm not sure the details of. My production example also shows up on our production machine that is almost the same hardware but has dual zeon and 6 gigs of ram. Rhett Hash Join (cost=4.83..5.91 rows=1 width=14) (actual time=7.148..7.159 rows=1 loops=1) Hash Cond: ("outer".id = "inner".obj2) -> Seq Scan on rtmessagestate (cost=0.00..1.05 rows=5 width=14) (actual time=0.007..0.015 rows=5 loops=1) -> Hash (cost=4.83..4.83 rows=1 width=4) (actual time=0.055..0.055 rows=0 loops=1) -> Index Scan using connection_regid_obj1_index on connection (cost=0.00..4.83 rows=1 width=4) (actual time=0.028..0.032 rows=1 loops=1) Index Cond: ((connection_registry_id = 40105) AND (obj1 = 73582)) Total runtime: 7.693 ms (7 rows) On 8/8/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Rhett Garber <[EMAIL PROTECTED]> writes: > > This is postgres 7.4.1 > > All the rows involved are integers. > > Hmph. There is something really strange going on here. I tried to > duplicate your problem in 7.4.*, thus: > > regression=# create table rtmessagestate(id int, f1 char(6)); > CREATE TABLE > regression=# insert into rtmessagestate values(1,'z'); > INSERT 559399 1 > regression=# insert into rtmessagestate values(2,'z'); > INSERT 559400 1 > regression=# insert into rtmessagestate values(3,'z'); > INSERT 559401 1 > regression=# insert into rtmessagestate values(4,'z'); > INSERT 559402 1 > regression=# insert into rtmessagestate values(5,'z'); > INSERT 559403 1 > regression=# vacuum analyze rtmessagestate; > VACUUM > regression=# create table connection(connection_registry_id int, obj1 int, > obj2 int); > CREATE TABLE > regression=# create index connection_regid_obj1_index on > connection(connection_registry_id,obj1); > CREATE INDEX > regression=# insert into connection values(40105,73582,3); > INSERT 559407 1 > regression=# explain analyze select rtmessagestate.* from > rtmessagestate,connection where (connection_registry_id = 40105) AND (obj1 > = 73582) and id = obj2; > QUERY > PLAN > > Hash Join (cost=4.83..5.91 rows=1 width=14) (actual time=0.498..0.544 > rows=1 loops=1) >Hash Cond: ("outer".id = "inner".obj2) >-> Seq Scan on rtmessagestate (cost=0.00..1.05 rows=5 width=14) (actual > time=0.030..0.072 rows=5 loops=1) >-> Hash (cost=4.83..4.83 rows=1 width=4) (actual time=0.305..0.305 > rows=0 loops=1) > -> Index Scan using connection_regid_obj1_index on connection > (cost=0.00..4.83 rows=1 width=4) (actual time=0.236..0.264 rows=1 loops=1) >Index Cond: ((connection_registry_id = 40105) AND (obj1 = > 73582)) > Total runtime: 1.119 ms > (7 rows) > > This duplicates your example as to plan and row counts: > > > Hash Join (cost=5.96..7.04 rows=1 width=14) (actual > > time=10.591..10.609 rows=1 loops=1) > > Hash Cond: ("outer".id = "inner".obj2) > > -> Seq Scan on rtmessagestate (cost=0.00..1.05 rows=5 width=14) > > (actual time=0.011..0.022 rows=5 loops=1) > > -> Hash (cost=5.96..5.96 rows=1 width=4) (actual > > time=0.109..0.109 rows=0 loops=1) > > -> Index Scan using connection_regid_obj1_index on > > connection (cost=0.00..5.96 rows=1 width=4) (actual time=0.070..0.076 > > rows=1 loops=1) > > Index Cond: ((connection_registry_id = 40105) AND (obj1 > > = 73582)) Total runtime: 11.536 ms > > (7 rows) > > My machine is considerably slower than yours, to judge by the actual > elapsed times in the scan nodes ... so why is it beating the pants > off yours in the join step? > > Can you try the above script verbatim in a scratch database and see > what you get? (Note it's worth trying the explain two or three > times to be sure the values have settled out.) > > I'm testing a fairly recent 7.4-branch build (7.4.8 plus), so that's one > possible reason for the discrepancy between my results and yours, but I > do not see anything in the 7.4 CVS logs that looks like it's related to > hashjoin performance. > > I'd be interested to see results from other people using 7.4.* too. > > 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] Why hash join instead of nested loop?
Yes, sorry, two totally different machines. The 7.4.8 run was on a dual P4 3.2GHz, and the 7.4.2 run was on a dual hyperthreaded Xeon 2.4GHz. --Ian On Tue, 2005-08-09 at 10:33, Tom Lane wrote: > Ian Westmacott <[EMAIL PROTECTED]> writes: > > On Mon, 2005-08-08 at 20:58, Tom Lane wrote: > >> I'd be interested to see results from other people using 7.4.* too. > > > 7.4.8: > > Total runtime: 0.198 ms > > > 7.4.2: > > Total runtime: 0.697 ms > > Just to be clear: those are two different machines of different speeds, > right? I don't believe we put any factor-of-three speedups into 7.4.* > after release ;-) > > regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Why hash join instead of nested loop?
Ian Westmacott <[EMAIL PROTECTED]> writes: > On Mon, 2005-08-08 at 20:58, Tom Lane wrote: >> I'd be interested to see results from other people using 7.4.* too. > 7.4.8: > Total runtime: 0.198 ms > 7.4.2: > Total runtime: 0.697 ms Just to be clear: those are two different machines of different speeds, right? I don't believe we put any factor-of-three speedups into 7.4.* after release ;-) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Why hash join instead of nested loop?
On Mon, 2005-08-08 at 20:58, Tom Lane wrote: > I'd be interested to see results from other people using 7.4.* too. 7.4.8: QUERY PLAN Hash Join (cost=4.83..5.91 rows=1 width=14) (actual time=0.122..0.126 rows=1 loops=1) Hash Cond: ("outer".id = "inner".obj2) -> Seq Scan on rtmessagestate (cost=0.00..1.05 rows=5 width=14) (actual time=0.003..0.006 rows=5 loops=1) -> Hash (cost=4.83..4.83 rows=1 width=4) (actual time=0.021..0.021 rows=0 loops=1) -> Index Scan using connection_regid_obj1_index on connection (cost=0.00..4.83 rows=1 width=4) (actual time=0.013..0.015 rows=1 loops=1) Index Cond: ((connection_registry_id = 40105) AND (obj1 = 73582)) Total runtime: 0.198 ms 7.4.2: QUERY PLAN Hash Join (cost=4.83..5.91 rows=1 width=14) (actual time=0.577..0.600 rows=1 loops=1) Hash Cond: ("outer".id = "inner".obj2) -> Seq Scan on rtmessagestate (cost=0.00..1.05 rows=5 width=14) (actual time=0.006..0.023 rows=5 loops=1) -> Hash (cost=4.83..4.83 rows=1 width=4) (actual time=0.032..0.032 rows=0 loops=1) -> Index Scan using connection_regid_obj1_index on connection (cost=0.00..4.83 rows=1 width=4) (actual time=0.016..0.020 rows=1 loops=1) Index Cond: ((connection_registry_id = 40105) AND (obj1 = 73582)) Total runtime: 0.697 ms --Ian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings