Skip site navigation (1) Skip section navigation (2)

Re: [SQL] two queryes in a single tablescan

From: Erik Jones <erik(at)myemma(dot)com>
To: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] two queryes in a single tablescan
Date: 2007-10-20 22:02:23
Message-ID: 4596A889-BE0B-4B4E-80BD-E0BF5654B04D@myemma.com (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-sql
On Oct 20, 2007, at 12:19 PM, Andreas Kretschmer wrote:

> Markus Schaber <schabi(at)logix-tt(dot)com> 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®
erik(at)myemma(dot)com
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



In response to

pgsql-performance by date

Next:From: Jonah H. HarrisDate: 2007-10-21 16:44:01
Subject: Re: need help with a query
Previous:From: Pavel VelikhovDate: 2007-10-20 20:11:00
Subject: Re: need help with a query

pgsql-sql by date

Next:From: Chuck D.Date: 2007-10-23 16:19:07
Subject: request for help with COPY syntax
Previous:From: Andreas KretschmerDate: 2007-10-20 17:19:39
Subject: Re: [SQL] two queryes in a single tablescan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group