pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtwo queryes in a single tablescan
9+ messages / 7 participants
[nested] [flat]
* two queryes in a single tablescan
@ 2007-10-17 12:30 Stefano Dal Pra <s.dalpra@gmail.com>
2007-10-17 13:00 ` Re: two queryes in a single tablescan Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-17 13:15 ` Re: two queryes in a single tablescan Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-10-18 02:24 ` Re: two queryes in a single tablescan 李彦 Ian Li <liyan82@gmail.com>
2007-10-20 10:58 ` Re: [SQL] two queryes in a single tablescan Markus Schaber <schabi@logix-tt.com>
0 siblings, 4 replies; 9+ messages in thread
From: Stefano Dal Pra @ 2007-10-17 12:30 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-performance@postgresql.org
Hi everybody,
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.
This seems to me quite a common situation but i have no clue whether a neat
solution can be implemented through stored procedure.
Any hint?
Thank you
Stefano
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: two queryes in a single tablescan
2007-10-17 12:30 two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
@ 2007-10-17 13:00 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-17 13:21 ` Re: two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
3 siblings, 1 reply; 9+ messages in thread
From: Heikki Linnakangas @ 2007-10-17 13:00 UTC (permalink / raw)
To: Stefano Dal Pra <s.dalpra@gmail.com>; +Cc: pgsql-sql; pgsql-performance@postgresql.org
Stefano Dal Pra 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.
>
> This seems to me quite a common situation but i have no clue whether a neat
> solution can be implemented through stored procedure.
With a temp table:
CREATE TEMPORARY TABLE tmp AS SELECT COUNT(*) as rows, a,b FROM WHERE C
GROUP BY a,b;
SELECT SUM(rows), a FROM tmp GROUP BY a;
SELECT SUM(rows), b FROM tmp GROUP BY b;
DROP TABLE tmp;
(Using temp tables in plpgsql procedures doesn't quite work until 8.3.
But you can use dynamic EXECUTE as a work-around. There used to be a FAQ
entry about that, but apparently it's been removed because the problem
has been fixed in the upcoming release.)
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: two queryes in a single tablescan
2007-10-17 12:30 two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
2007-10-17 13:00 ` Re: two queryes in a single tablescan Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-10-17 13:21 ` Stefano Dal Pra <s.dalpra@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Stefano Dal Pra @ 2007-10-17 13:21 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-sql; pgsql-performance@postgresql.org
On 10/17/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
> Stefano Dal Pra 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.
> >
> > This seems to me quite a common situation but i have no clue whether a neat
> > solution can be implemented through stored procedure.
>
> With a temp table:
>
> CREATE TEMPORARY TABLE tmp AS SELECT COUNT(*) as rows, a,b FROM WHERE C
> GROUP BY a,b;
> SELECT SUM(rows), a FROM tmp GROUP BY a;
> SELECT SUM(rows), b FROM tmp GROUP BY b;
> DROP TABLE tmp;
>
Thank You.
I actually already do something like that:
in a stored procedure i do create a md5 hash using passed parameters
converted to TEXT
and get a unix_like timestamp using now()::abstime::integer.
This gets me a string like: 9ffeb60e9e6581726f7f5027b42c7942_1192443215
which i do use to
EXECUTE
CREATE TABLE 9ffeb60e9e6581726f7f5027b42c7942_1192443215 AS
SELECT * FROM
getjd('''||param1||''','''||param2||''','||param3||','||param4||')'
The 9ffeb60e9e6581726f7f5027b42c7942_1192443215 is what i called 'tab'
in my first post,
and i need to perform about 7 queryes on that. (after a while i will
drop the table using the timestamp part of the name, but that's
another point).
Here is where i would like to scan once only that table. Depending on
parameters it may get as big as 50Mb (this actually is the tablespace
size growth) or more with about 10^6 tuples.
Stefano
> (Using temp tables in plpgsql procedures doesn't quite work until 8.3.
> But you can use dynamic EXECUTE as a work-around. There used to be a FAQ
> entry about that, but apparently it's been removed because the problem
> has been fixed in the upcoming release.)
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: two queryes in a single tablescan
2007-10-17 12:30 two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
@ 2007-10-17 13:15 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-10-17 13:39 ` Re: two queryes in a single tablescan Heikki Linnakangas <heikki@enterprisedb.com>
3 siblings, 1 reply; 9+ messages in thread
From: Steinar H. Gunderson @ 2007-10-17 13:15 UTC (permalink / raw)
To: Stefano Dal Pra <s.dalpra@gmail.com>; +Cc: pgsql-sql; pgsql-performance@postgresql.org
On Wed, Oct 17, 2007 at 02:30:52PM +0200, Stefano Dal Pra wrote:
> The main goal would be to get multiple results while scanning the
> table[s] once only
> thus getting results in a faster way.
In 8.3, Postgres will do this for you itself -- if you already have a
sequential scan running against a given table, another one starting in
parallel will simply piggyback it.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: two queryes in a single tablescan
2007-10-17 12:30 two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
2007-10-17 13:15 ` Re: two queryes in a single tablescan Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2007-10-17 13:39 ` Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 9+ messages in thread
From: Heikki Linnakangas @ 2007-10-17 13:39 UTC (permalink / raw)
To: Stefano Dal Pra <s.dalpra@gmail.com>; pgsql-sql; pgsql-performance@postgresql.org
Steinar H. Gunderson wrote:
> On Wed, Oct 17, 2007 at 02:30:52PM +0200, Stefano Dal Pra wrote:
>> The main goal would be to get multiple results while scanning the
>> table[s] once only
>> thus getting results in a faster way.
>
> In 8.3, Postgres will do this for you itself -- if you already have a
> sequential scan running against a given table, another one starting in
> parallel will simply piggyback it.
You'd have to run the seq scans at the same time, from two different
backends, so it's not going to help here.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: two queryes in a single tablescan
2007-10-17 12:30 two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
@ 2007-10-18 02:24 ` 李彦 Ian Li <liyan82@gmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: 李彦 Ian Li @ 2007-10-18 02:24 UTC (permalink / raw)
To: Stefano Dal Pra <s.dalpra@gmail.com>; +Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
I remember when I was using SQL server we did like like that:
SELECT count(CASE WHEN A THEN 1 END) AS cnt_a, count(CASE WHEN B
THEN 1 END) AS cnt_b FROM tab WHERE C;
I did a little test with pg_bench data, also works in PostgreSQL:
test=# select count(*) from history where tid = 1;
count
-------
574
(1 行)
时间: 9.553 ms
test=# select count(*) from history where tid = 2;
count
-------
1107
(1 行)
时间: 8.949 ms
test=# select count(CASE WHEN tid = 1 then 1 END) as t1_cont,
count(case when tid=2 then 1 end) as t2_cnt from history ;
t1_cont | t2_cnt
---------+--------
574 | 1107
(1 行)
时间: 17.182 ms
Hope that helps.
Regards
Stefano Dal Pra wrote:
> Hi everybody,
>
> 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.
>
> This seems to me quite a common situation but i have no clue whether a neat
> solution can be implemented through stored procedure.
>
> Any hint?
>
> Thank you
>
> Stefano
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] two queryes in a single tablescan
2007-10-17 12:30 two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
@ 2007-10-20 10:58 ` Markus Schaber <schabi@logix-tt.com>
2007-10-20 17:19 ` Re: [SQL] two queryes in a single tablescan Andreas Kretschmer <akretschmer@spamfence.net>
3 siblings, 1 reply; 9+ messages in thread
From: Markus Schaber @ 2007-10-20 10:58 UTC (permalink / raw)
To: Stefano Dal Pra <s.dalpra@gmail.com>; +Cc: pgsql-sql; pgsql-performance@postgresql.org
Hi, Stefano,
"Stefano Dal Pra" <s.dalpra@gmail.com> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] two queryes in a single tablescan
2007-10-17 12:30 two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
2007-10-20 10:58 ` Re: [SQL] two queryes in a single tablescan Markus Schaber <schabi@logix-tt.com>
@ 2007-10-20 17:19 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-10-20 22:02 ` Re: [SQL] two queryes in a single tablescan Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 9+ messages in thread
From: Andreas Kretschmer @ 2007-10-20 17:19 UTC (permalink / raw)
To: pgsql-performance@postgresql.org; pgsql-sql
Markus Schaber <schabi@logix-tt.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?
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°
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] two queryes in a single tablescan
2007-10-17 12:30 two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
2007-10-20 10:58 ` Re: [SQL] two queryes in a single tablescan Markus Schaber <schabi@logix-tt.com>
2007-10-20 17:19 ` Re: [SQL] two queryes in a single tablescan Andreas Kretschmer <akretschmer@spamfence.net>
@ 2007-10-20 22:02 ` Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 9+ messages in thread
From: Erik Jones @ 2007-10-20 22:02 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance@postgresql.org; pgsql-sql
On Oct 20, 2007, at 12:19 PM, Andreas Kretschmer wrote:
> Markus Schaber <schabi@logix-tt.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@myemma.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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-10-20 22:02 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-17 12:30 two queryes in a single tablescan Stefano Dal Pra <s.dalpra@gmail.com>
2007-10-17 13:00 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-17 13:21 ` Stefano Dal Pra <s.dalpra@gmail.com>
2007-10-17 13:15 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-10-17 13:39 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-18 02:24 ` 李彦 Ian Li <liyan82@gmail.com>
2007-10-20 10:58 ` Markus Schaber <schabi@logix-tt.com>
2007-10-20 17:19 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-10-20 22:02 ` Erik Jones <erik@myemma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox