pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Problem
17+ messages / 12 participants
[nested] [flat]
* Query Problem
@ 2000-10-26 01:59 Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 17+ messages in thread
From: Josh Berkus @ 2000-10-26 01:59 UTC (permalink / raw)
To: pgsql-sql
Folks:
Here's the problem, in abstract: I need to select every record in table
A that does not have a link in table B Join Table C where Table
C.account = 11
The relevant fields:
Table_A
CaseID
Data
Table_B
GroupID
CaseID
Amount
Table_C
GroupID
AccountID
Thus, I need to select:
SELECT Data FROM Table A
WHERE CaseID NOT IN (
SELECT CaseID FROM Table_B, Table_C
WHERE Table_B.GroupID = TableC.GroupID
AND TableC.AccountID = 11)
The problem is, since Table_B and Table_C are large (10,000 records +)
this exclusion query takes several *minutes* to run.
I've fooled around with drectional joins, views, and temporary tables,
but I can seem to find anything that works faster. Suggestions?
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Query Problem
@ 2000-10-26 13:53 Tomas Berndtsson <tomas@nocrew.org>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 17+ messages in thread
From: Tomas Berndtsson @ 2000-10-26 13:53 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
> Folks:
>
> Here's the problem, in abstract: I need to select every record in table
> A that does not have a link in table B Join Table C where Table
> C.account = 11
>
> The relevant fields:
>
> Table_A
> CaseID
> Data
>
> Table_B
> GroupID
> CaseID
> Amount
>
> Table_C
> GroupID
> AccountID
>
> Thus, I need to select:
>
> SELECT Data FROM Table A
> WHERE CaseID NOT IN (
> SELECT CaseID FROM Table_B, Table_C
> WHERE Table_B.GroupID = TableC.GroupID
> AND TableC.AccountID = 11)
>
> The problem is, since Table_B and Table_C are large (10,000 records +)
> this exclusion query takes several *minutes* to run.
>
> I've fooled around with drectional joins, views, and temporary tables,
> but I can seem to find anything that works faster. Suggestions?
Without having tried, something like this might be faster:
SELECT Data FROM Table A
WHERE CaseID NOT IN (
SELECT CaseID FROM Table_B
WHERE Table_B.GroupID IN (
SELECT GroupID FROM Table_C
WHERE TableC.AccountID = 11))
Tomas
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Query Problem
@ 2000-10-26 15:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2000-10-26 15:16 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
Josh Berkus <josh@agliodbs.com> writes:
> Thus, I need to select:
> SELECT Data FROM Table A
> WHERE CaseID NOT IN (
> SELECT CaseID FROM Table_B, Table_C
> WHERE Table_B.GroupID = TableC.GroupID
> AND TableC.AccountID = 11)
> The problem is, since Table_B and Table_C are large (10,000 records +)
> this exclusion query takes several *minutes* to run.
I don't think there is any good way to make this fast in current
sources. A partial workaround is to use a temp table:
SELECT CaseID INTO TEMP TABLE mycaseids FROM Table_B, Table_C
WHERE Table_B.GroupID = TableC.GroupID
AND TableC.AccountID = 11;
CREATE INDEX mycaseids_idx ON mycaseids(caseid); -- critical!
SELECT Data FROM TableA upper
WHERE NOT EXISTS (select 1 from mycaseids where caseid = upper.caseid);
You'd need to check with EXPLAIN, but the EXISTS subplan should make
use of the index to probe the temp table, so you get one index lookup
per outer tuple. Better than a complete scan of the subselect outputs,
which is what you'll get with the NOT IN style.
In 7.1 it'll be possible to do this with an outer join, which should
be a lot quicker:
SELECT Data FROM TableA LEFT JOIN
(SELECT CaseID FROM Table_B, Table_C
WHERE Table_B.GroupID = TableC.GroupID
AND TableC.AccountID = 11) subselect
ON (tablea.caseid = subselect.caseid)
WHERE subselect.caseid IS NULL;
ie, do the outer join and then discard the successfully-matched rows.
Further down the pike, we have plans to make the system smart enough to
transform IN and NOT IN constructs into join-like queries automatically.
Right now, though, they're best rewritten into something else when
performance is important.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Query Problem
@ 2000-10-26 15:22 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 17+ messages in thread
From: Stephan Szabo @ 2000-10-26 15:22 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
What is the explain output for the queries you've tried?
Stephan Szabo
sszabo@bigpanda.com
On Wed, 25 Oct 2000, Josh Berkus wrote:
>
> Folks:
>
> Here's the problem, in abstract: I need to select every record in table
> A that does not have a link in table B Join Table C where Table
> C.account = 11
>
> The relevant fields:
>
> Table_A
> CaseID
> Data
>
> Table_B
> GroupID
> CaseID
> Amount
>
> Table_C
> GroupID
> AccountID
>
> Thus, I need to select:
>
> SELECT Data FROM Table A
> WHERE CaseID NOT IN (
> SELECT CaseID FROM Table_B, Table_C
> WHERE Table_B.GroupID = TableC.GroupID
> AND TableC.AccountID = 11)
>
> The problem is, since Table_B and Table_C are large (10,000 records +)
> this exclusion query takes several *minutes* to run.
>
> I've fooled around with drectional joins, views, and temporary tables,
> but I can seem to find anything that works faster. Suggestions?
>
> -Josh Berkus
>
> --
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 436-9166
> for law firms, small businesses fax 436-0137
> and non-profit organizations. pager 338-4078
> San Francisco
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Query Problem
@ 2000-10-26 17:55 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 17+ messages in thread
From: Josh Berkus @ 2000-10-26 17:55 UTC (permalink / raw)
To: pgsql-sql
Michael,
> SELECT Data
> FROM Table A
> WHERE NOT EXISTS (
> SELECT * FROM Table_B, Table_C
> WHERE Table_B.GroupID = TableC.GroupID
> AND TableC.AccountID = 11
> )
>
> I think that the not exists is a bit quicker than the NOT IN. Give it
> a whirl.
A *lot* faster. Like, 7x as fast. I'd forgotten about EXISTS, since I
so seldom have a use for it ... but this is shy it was created, I guess.
Thanks so much for your help!
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: query problem
@ 2002-08-29 20:45 Josh Berkus <josh@agliodbs.com>
parent: Marco Muratori <marco.muratori@emaze.net>
1 sibling, 1 reply; 17+ messages in thread
From: Josh Berkus @ 2002-08-29 20:45 UTC (permalink / raw)
To: Marco Muratori <marco.muratori@emaze.net>; pgsql-sql
Marco,
> Is there a way to obtain this records by performing one
> single query and not by making for each city something like
> "SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19
> 15:39:15+00' ORDER BY date ASC LIMIT 1;"?
Close. Try:
SELECT city, MAX("date") as last_date
FROM table
WHERE "date" > $date
GROUP BY city
ORDER BY city
Though as an aggregate query, this will be slow on large tables.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 17+ messages in thread
* query problem
@ 2002-08-30 16:38 Marco Muratori <marco.muratori@emaze.net>
0 siblings, 2 replies; 17+ messages in thread
From: Marco Muratori @ 2002-08-30 16:38 UTC (permalink / raw)
To: pgsql-sql
Hi
suppose I have the following situation:
city date
---------+-----------------------
London | 2002-08-08 07:05:16+00
London | 2002-07-30 13:08:22+00
London | 2002-07-30 07:39:15+00
London | 2002-07-29 17:51:47+00
London | 2002-07-29 17:45:49+00
London | 2002-07-29 17:45:47+00
Paris | 2002-04-08 15:04:28+00
Paris | 2002-03-29 17:22:18+00
Paris | 2002-02-15 12:50:32+00
Paris | 2002-01-22 11:40:22+00
Paris | 2002-01-07 17:41:23+00
Paris | 2001-11-12 16:37:37+00
Paris | 2001-11-05 15:28:23+00
Paris | 2001-11-05 08:21:19+00
Oslo | 2002-07-19 15:42:20+00
Oslo | 2002-07-19 15:42:18+00
Oslo | 2002-07-18 10:03:58+00
Oslo | 2002-07-18 08:56:30+00
Oslo | 2002-07-17 17:17:27+00
Oslo | 2002-07-17 16:11:38+00
For each city I have a couple of dates in DESC order.
For each city i need to get the first record which date
comes after a given date. If the given date was for example
"2002-07-19 15:39:15+00", I would get the following
records:
London | 2002-07-29 17:45:47+00
Oslo | 2002-07-19 15:42:18+00
Is there a way to obtain this records by performing one
single query and not by making for each city something like
"SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19
15:39:15+00' ORDER BY date ASC LIMIT 1;"?
Thanks.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: query problem
@ 2002-08-30 20:00 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Marco Muratori <marco.muratori@emaze.net>
1 sibling, 0 replies; 17+ messages in thread
From: Jean-Luc Lachance @ 2002-08-30 20:00 UTC (permalink / raw)
To: Marco Muratori <marco.muratori@emaze.net>; +Cc: pgsql-sql
How about:
select city, min(date) from thetable where date > '2002-07-19
15:39:15+00' group by city;
JLL
Marco Muratori wrote:
>
> Hi
> suppose I have the following situation:
>
> city date
> ---------+-----------------------
> London | 2002-08-08 07:05:16+00
> London | 2002-07-30 13:08:22+00
> London | 2002-07-30 07:39:15+00
> London | 2002-07-29 17:51:47+00
> London | 2002-07-29 17:45:49+00
> London | 2002-07-29 17:45:47+00
> Paris | 2002-04-08 15:04:28+00
> Paris | 2002-03-29 17:22:18+00
> Paris | 2002-02-15 12:50:32+00
> Paris | 2002-01-22 11:40:22+00
> Paris | 2002-01-07 17:41:23+00
> Paris | 2001-11-12 16:37:37+00
> Paris | 2001-11-05 15:28:23+00
> Paris | 2001-11-05 08:21:19+00
> Oslo | 2002-07-19 15:42:20+00
> Oslo | 2002-07-19 15:42:18+00
> Oslo | 2002-07-18 10:03:58+00
> Oslo | 2002-07-18 08:56:30+00
> Oslo | 2002-07-17 17:17:27+00
> Oslo | 2002-07-17 16:11:38+00
>
> For each city I have a couple of dates in DESC order.
> For each city i need to get the first record which date
> comes after a given date. If the given date was for example
> "2002-07-19 15:39:15+00", I would get the following
> records:
>
> London | 2002-07-29 17:45:47+00
> Oslo | 2002-07-19 15:42:18+00
>
> Is there a way to obtain this records by performing one
> single query and not by making for each city something like
> "SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19
> 15:39:15+00' ORDER BY date ASC LIMIT 1;"?
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: query problem
@ 2002-08-30 20:02 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 17+ messages in thread
From: Jean-Luc Lachance @ 2002-08-30 20:02 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Marco Muratori <marco.muratori@emaze.net>; pgsql-sql
I think you meant min(date)...
Josh Berkus wrote:
>
> Marco,
>
> > Is there a way to obtain this records by performing one
> > single query and not by making for each city something like
> > "SELECT city,date FROM table WHERE city='London' AND date>'2002-07-19
> > 15:39:15+00' ORDER BY date ASC LIMIT 1;"?
>
> Close. Try:
>
> SELECT city, MAX("date") as last_date
> FROM table
> WHERE "date" > $date
> GROUP BY city
> ORDER BY city
>
> Though as an aggregate query, this will be slow on large tables.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 17+ messages in thread
* Query problem
@ 2003-08-19 13:02 Rado Petrik <r.p@szm.sk>
0 siblings, 2 replies; 17+ messages in thread
From: Rado Petrik @ 2003-08-19 13:02 UTC (permalink / raw)
To: pgsql-sql
Hi, I have table "user".
table "user"
id bit(dec) bit(bin)
-----------------------------
1 1 1
2 3 11
3 12 1100
and I need query - >
SELECT "output" FROM user WHERE id = 1 OR id = 2 or id = 3
output = (1111)
In programing language output =
id(1..3) {
$output = ( bit | $output );
}
How write query ? . Thanks
--
Rado Petrik <r.p@szm.sk>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Query problem
@ 2003-08-19 14:21 Richard Huxton <dev@archonet.com>
parent: Rado Petrik <r.p@szm.sk>
1 sibling, 0 replies; 17+ messages in thread
From: Richard Huxton @ 2003-08-19 14:21 UTC (permalink / raw)
To: Rado Petrik <r.p@szm.sk>; pgsql-sql
On Tuesday 19 August 2003 14:02, Rado Petrik wrote:
> id bit(dec) bit(bin)
> -----------------------------
> 1 1 1
> 2 3 11
> 3 12 1100
>
>
> and I need query - >
>
> SELECT "output" FROM user WHERE id = 1 OR id = 2 or id = 3
> output = (1111)
PG supports bit-strings which I guess are designed for exactly this situation.
See the "data types" section of the manual for details.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Query problem
@ 2003-08-19 15:50 Bruno Wolff III <bruno@wolff.to>
parent: Rado Petrik <r.p@szm.sk>
1 sibling, 0 replies; 17+ messages in thread
From: Bruno Wolff III @ 2003-08-19 15:50 UTC (permalink / raw)
To: Rado Petrik <r.p@szm.sk>; +Cc: pgsql-sql
On Tue, Aug 19, 2003 at 15:02:24 +0200,
Rado Petrik <r.p@szm.sk> wrote:
>
> In programing language output =
>
> id(1..3) {
> $output = ( bit | $output );
> }
>
> How write query ? . Thanks
You probably want to write a custom aggregate function to do this.
^ permalink raw reply [nested|flat] 17+ messages in thread
* query problem
@ 2003-11-08 07:44 ron_tabada <ron_tabada@yahoo.com>
0 siblings, 0 replies; 17+ messages in thread
From: ron_tabada @ 2003-11-08 07:44 UTC (permalink / raw)
To: pgsql-sql
Hello everyone, Good day! Could anyone help me translate this query in Microsoft Access to Postgresql. I'm having a difficulty. Pls...
Query1:
SELECT items.description, Sum(supplieditems.qty) AS SumOfqty
FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno
GROUP BY items.description;
Query2:
SELECT [items].[description], Sum([customer].[qty]) AS SumOfqty
FROM (items INNER JOIN OtherItem ON [items].[itemno]=[OtherItem].[Itemno]) INNER JOIN customer ON [OtherItem].[Itemno]=[customer].[itemcode]
GROUP BY [items].[description];
Query3:
SELECT [Query1].[SumOfqty], [Query2].[SumOfqty], [Query1]![SumOfqty]-[Query2]![SumOfqty] AS remain
FROM Query1, Query2;
I have translated Query1 and Query2 in POSTGRESQL but I don't know how to implement Query3.
---------------------------------
Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger
^ permalink raw reply [nested|flat] 17+ messages in thread
* Query Problem
@ 2003-11-11 01:06 Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 1 reply; 17+ messages in thread
From: Abdul Wahab Dahalan @ 2003-11-11 01:06 UTC (permalink / raw)
To: pgsql-sql
Hi there!
If I've a table like below.
>kk kj pngk vote
>
>01 02 c 10
>01 02 b 5
>
>
How do I make a query so that I can get a result
like this?
>
>kk kj pngk vote
>
>01 02 c,b 15
>
>
>
Any help pretty much appreciated.
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Query Problem
@ 2003-11-11 01:22 Joe Conway <mail@joeconway.com>
parent: Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 0 replies; 17+ messages in thread
From: Joe Conway @ 2003-11-11 01:22 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; +Cc: pgsql-sql
Abdul Wahab Dahalan wrote:
> If I've a table like below.
>
>> kk kj pngk vote
>> 01 02 c 10
>> 01 02 b 5
> How do I make a query so that I can get a result
> like this?
>>
>> kk kj pngk vote
>> 01 02 c,b 15
>>
create or replace function accum_text(text, text) returns text as
'select case when $1 = '''' then $2 else $1 || '','' || $2 end' language
sql;
CREATE AGGREGATE concat(BASETYPE = text, SFUNC = accum_text, STYPE =
text, INITCOND = '');
create table t(kk text, kj text, pngk text, vote int);
insert into t values('01','02','c',10);
insert into t values('01','02','b',5);
regression=# select kk, kj, concat(pngk), sum(vote) from t group by kk, kj;
kk | kj | concat | sum
----+----+--------+-----
01 | 02 | c,b | 15
(1 row)
HTH,
Joe
^ permalink raw reply [nested|flat] 17+ messages in thread
* Query Problem
@ 2005-04-07 07:16 Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 1 reply; 17+ messages in thread
From: Abdul Wahab Dahalan @ 2005-04-07 07:16 UTC (permalink / raw)
To: pgsql-sql
If I've 2 tables like this:
Country table : with 2 fields (country and id)
Country id
England E
France F
Japan J
FlightTo table : with 2 fields(Flight ID and Destination)
FlightID Destination
B1 E,J
B2 E,F
B3 J,F
How do I make a query, so that I can get a result something like this :
FlightID CountryVisited
B1 England,Japan
B2 England,France
B3 Japan, France
Any help very much appreciated. Thanks
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Query Problem
@ 2005-04-07 08:46 Richard Huxton <dev@archonet.com>
parent: Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 0 replies; 17+ messages in thread
From: Richard Huxton @ 2005-04-07 08:46 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; +Cc: pgsql-sql
Abdul Wahab Dahalan wrote:
> If I've 2 tables like this:
>
> Country table : with 2 fields (country and id)
>
> Country id
> England E
> France F
> Japan J
>
> FlightTo table : with 2 fields(Flight ID and Destination)
>
> FlightID Destination
> B1 E,J
> B2 E,F
> B3 J,F
Simplest solution is to correct this table - "Destination" should be two
fields (e.g. dest_from,dest_to). Then you could do something like:
SELECT
flight_id,
c1.country as from_country,
c2.country as to_country
FROM
flight_to,
country c1,
country c2
WHERE
dest_from = c1.id
AND dest_to = c2.id
;
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2005-04-07 08:46 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-26 01:59 Query Problem Josh Berkus <josh@agliodbs.com>
2000-10-26 13:53 ` Tomas Berndtsson <tomas@nocrew.org>
2000-10-26 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-26 15:22 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-10-26 17:55 Re: Query Problem Josh Berkus <josh@agliodbs.com>
2002-08-30 16:38 query problem Marco Muratori <marco.muratori@emaze.net>
2002-08-29 20:45 ` Re: query problem Josh Berkus <josh@agliodbs.com>
2002-08-30 20:02 ` Re: query problem Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-30 20:00 ` Re: query problem Jean-Luc Lachance <jllachan@nsd.ca>
2003-08-19 13:02 Query problem Rado Petrik <r.p@szm.sk>
2003-08-19 14:21 ` Re: Query problem Richard Huxton <dev@archonet.com>
2003-08-19 15:50 ` Re: Query problem Bruno Wolff III <bruno@wolff.to>
2003-11-08 07:44 query problem ron_tabada <ron_tabada@yahoo.com>
2003-11-11 01:06 Query Problem Abdul Wahab Dahalan <wahab@mimos.my>
2003-11-11 01:22 ` Joe Conway <mail@joeconway.com>
2005-04-07 07:16 Query Problem Abdul Wahab Dahalan <wahab@mimos.my>
2005-04-07 08:46 ` Richard Huxton <dev@archonet.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