Re: [PERFORM] [SQL] two queryes in a single tablescan
On Oct 20, 2007, at 12:19 PM, Andreas Kretschmer wrote: Markus Schaber <[EMAIL PROTECTED]> schrieb: is there any way to get both results in a single query, eventually through stored procedure? The retrieved [count(*),A] ; [count(*),B)] data couldnt fit on a single table, of course. The main goal would be to get multiple results while scanning the table[s] once only thus getting results in a faster way. PostgreSQL 8.3 contains great improvements in this area, you can simply start the selects from concurrent connections, and the backend will synchronize the scans. works this right across different transactions? I mean, for instance, TX a insert rows and TX b insert other rows and both clients (with different transactions) starts a seq-scan? If you are in read-committed mode and both backends start their scans after the other has made its insert, then yes. Note Markus's point that both queries must be initiated by concurrent connections. Since Postgres doesn't have any kind of shared transaction mechanism across connections then this is inherent. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] [SQL] two queryes in a single tablescan
Markus Schaber <[EMAIL PROTECTED]> schrieb: > > is there any way to get both results in a single query, > > eventually through stored procedure? > > The retrieved [count(*),A] ; [count(*),B)] data couldnt fit > > on a single table, of course. > > > > The main goal would be to get multiple results while scanning the > > table[s] once only > > thus getting results in a faster way. > > PostgreSQL 8.3 contains great improvements in this area, you can simply > start the selects from concurrent connections, and the backend will > synchronize the scans. works this right across different transactions? I mean, for instance, TX a insert rows and TX b insert other rows and both clients (with different transactions) starts a seq-scan? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] [SQL] two queryes in a single tablescan
Hi, Stefano, "Stefano Dal Pra" <[EMAIL PROTECTED]> wrote: > suppose you have a large table tab and two (or more) queryes like this: > > SELECT count(*),A FROM tab WHERE C GROUP BY A; > SELECT count(*),B FROM tab WHERE C GROUP BY B; > > is there any way to get both results in a single query, > eventually through stored procedure? > The retrieved [count(*),A] ; [count(*),B)] data couldnt fit > on a single table, of course. > > The main goal would be to get multiple results while scanning the > table[s] once only > thus getting results in a faster way. PostgreSQL 8.3 contains great improvements in this area, you can simply start the selects from concurrent connections, and the backend will synchronize the scans. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(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
