Thread: Running two queries concurrently?
Hi there, The more and more that I use PostgreSQL the more technical my queries are seeming to become, so here is the latest question/scenario I could do with some advice on please. Take the following hypothetical situation: QueryOne: I select 10000 rows from TABLE A using CRITERIA Y, and insert the result into TABLE B. This query takes 20 minutes to run. QueryTwo: Two minutes after QueryOne has begun, I select 5000 rows from TABLE A using CRITERIA X, and insert the result into TABLE B. This query takes 10 minutes to run. So my question really is, is this possible? I would have thought that perhaps QueryOne puts a lock on TABLE A and TABLE B while it is selecting data from one and inserting into the other, but maybe not? At the moment I have to do QueryOne and then QueryTwo quite a few times, but have been doing them one after another. Maybe I can actually run them at the same time? Best wishes James
Start your journey here: http://www.postgresql.org/docs/9.3/interactive/mvcc.html James David Smith wrote > So my question really is, is this possible? I would have thought that > perhaps QueryOne puts a lock on TABLE A and TABLE B while it is > selecting data from one and inserting into the other, but maybe not? Yes. Locks do occur BUT 1) these are not "exclusive" locks; multiple clients can interact according to rules defined in the documentation 2) locks can be done on a row (or at worse group of rows) basis; not just table > At the moment I have to do QueryOne and then QueryTwo quite a few > times, but have been doing them one after another. Maybe I can > actually run them at the same time? You do have the ability to run these simultaneously in separate sessions; whether you get any performance/time gain from doing so depends on a number of factors such as disk i/o and just randomness as to where data is located - not to mention your processor setup. I am hoping you are using something like: INSERT INTO tableB SELECT ... FROM tableA WHERE ... 20 minutes for 10,000 records seems extremely long...it sounds like you might also want to learn about: http://www.postgresql.org/docs/9.3/interactive/indexes.html and also: http://www.postgresql.org/docs/9.3/interactive/using-explain.html the later to help you (and others) pin-point where in the query (assuming you aren't pulling 10k records over the network to your client then sending them back during the insert) you are eating all that time. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Running-two-queries-concurrently-tp5770309p5770315.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.