[SQL] explain analyze results are different for each iteration
I'm trying to benchmark some complex sql queries. One query, in particular, is causing problems -- its cost values can vary from 228 to 907, its Total Runtimes from 60 ms to 5176 ms. The query plans show that the optimizer is choosing different plans for different iterations of the same query. Does anyone have any idea what's going on here or maybe how I can force Postgres to prefer the faster plan? The query plans are very long (11 tables joined) but I can include them if it might help. Thanks for any advice, Roberto ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] explain analyze results are different for each iteration
Are you forcing stuff out of cache (both postgres and OS) ? I've found that this can make a huge difference with some queries ... not sure that this would make it have different plans, though. My ill-informied $0.02 worth ... fwiw Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Robert Davis [mailto:[EMAIL PROTECTED] Sent: Tue 9/14/2004 6:21 PM To: [EMAIL PROTECTED] Cc: Subject:[SQL] explain analyze results are different for each iteration I'm trying to benchmark some complex sql queries. One query, in particular, is causing problems -- its cost values can vary from 228 to 907, its Total Runtimes from 60 ms to 5176 ms. The query plans show that the optimizer is choosing different plans for different iterations of the same query. Does anyone have any idea what's going on here or maybe how I can force Postgres to prefer the faster plan? The query plans are very long (11 tables joined) but I can include them if it might help. Thanks for any advice, Roberto ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] explain analyze results are different for each iteration
On Tue, 14 Sep 2004, Robert Davis wrote: > I'm trying to benchmark some complex sql queries. One query, in > particular, is causing problems -- its cost values can vary from 228 > to 907, its Total Runtimes from 60 ms to 5176 ms. The query plans > show that the optimizer is choosing different plans for different > iterations of the same query. Does anyone have any idea what's > going on here or maybe how I can force Postgres to prefer the faster > plan? > > The query plans are very long (11 tables joined) but I can include > them if it might help. Thanks for any advice, 11 tables. You might want to see if raising geqo_threshold stabilizes the plans since IIRC that's the threshold for using the genetic optimizer. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] implementing an out-of-transaction trigger
(B (B (BHi All, (B (BI've come across a situation where I'd (Blike to use some kind of "out-of-transaction trigger" to do some processing (Bafter changes to some tables, but without extending the duration of the main (Btransaction. Of course, it's important that the processing be completed so it (Bhas to be, as far as possible, reliable and "safe". The extra processing should (Bbe completed within a reasonable time after the original transaction, but it (Bneedn't happen immediately. (B (BIn the past, we have used triggers (Bwritten in C that call perl scripts. It seems untidy to me as we introduce 2 (Bmore programming languages and I'm not so comfortable with the idea of (Bcalling an OS program from a trigger which is part of the original transaction (Banyway. It doesn't seem to be helping the situation much. (B (BI havn't been able to come up wth a standard way to do this in postgres so (BI'm contemplating writing a kind of background process that checks for changes (Band processes them periodically, nothing very sophisticated. I'm wondering if (Bthere is a better way. Maybe other people here have dealt with this kind of (Bsituation. (B (BIf I were using Oracle, I might (Buse Streams or Advanced Queuing, this would probably be a good case for (Busing them - though I have no experience myself. In oracles case the subscribed (Bchanges are captured from the redo log. After that it's fairly standard (Bpublisher/subsriber type stuff. I expect that this should, in theory, be (Bpossible with postgres too though AFAIK (Bthis isn't on the todo list. (B (BAnyway, if anyone has any thoughts on (Bthis, I'd be interested to hear them, (Bregards (BIain
