On Tue, 16 Jul 2002 10:51:03 +0200
"Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> wrote:
> Aggregate (cost=1257368.92..1287375.04 rows=600122 width=12) (actual
> time=1236941.71..1454824.56 rows=62 loops=1)
> -> Group (cost=1257368.92..1272371.98 rows=6001225 width=12) (actual
> time=1233
- Original Message -
From: "Masaru Sugawara" <[EMAIL PROTECTED]>
To: "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, July 15, 2002 6:15 PM
Subject: Re: [SQL] [HACKERS] please help on query
>
> Sorry,
On Mon, 15 Jul 2002 09:45:36 +0200
"Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> wrote:
> This is the output:
>
> Aggregate (cost=0.00..647161.10 rows=600122 width=8) (actual
> time=4959.19..347328.83 rows=62 loops=1)
> -> Group (cost=0.00..632158.04 rows=6001225 width=8) (actual
> time=
- Original Message -
From: "Masaru Sugawara" <[EMAIL PROTECTED]>
To: "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, July 14, 2002 2:23 PM
Subject: Re: [SQL] [HACKERS] please help on query
This is the output:
uhrabka" <[EMAIL PROTECTED]>
> To: "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]>
> Cc: "Manfred Koizar" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Friday, July 12, 2002 1:50 PM
> Subject: Re: [SQL] [HACKERS] please help on query
>
]>
Sent: Friday, July 12, 2002 1:50 PM
Subject: Re: [SQL] [HACKERS] please help on query
> hi,
>
> avoid subselect: create a temp table and use join...
>
> CREATE TEMP TABLE tmp AS
>SELECT
> lineitem.orderkey
>FROM
> lineitem
>WHERE
>
I've tried
SELECT
supplier.name,
supplier.address
FROM
supplier,
nation,
lineitem
WHERE
EXISTS(
SELECT
partsupp.suppkey
FROM
partsupp,lineitem
WHERE
lineitem.partkey=partsupp.partkey
AND lineitem.suppkey=partsupp.partkey
AND lineitem.shipdate>=('1994-01-01')::DATE
AND
hi,
avoid subselect: create a temp table and use join...
CREATE TEMP TABLE tmp AS
SELECT
lineitem.orderkey
FROM
lineitem
WHERE
lineitem.orderkey=orders.orderkey
GROUP BY
lineitem.orderkey HAVING
sum(lineitem.quantity)>300;
CREATE INDEX tmp_idx ON tmp (orderkey);
hi,
avoid subselect: create a temp table and use join...
CREATE TEMP TABLE tmp AS
SELECT
lineitem.orderkey
FROM
lineitem
WHERE
lineitem.orderkey=orders.orderkey
GROUP BY
lineitem.orderkey HAVING
sum(lineitem.quantity)>300;
CREATE INDEX tmp_idx ON tmp (orderkey);
> The cost is now only 1141741215.35 compared to 2777810917708.17
> before; this is an improvement factor of more than 2000. So what's
> your problem? ;-)
>
> Servus
> Manfred
>
In fact planner is estimating incredibly badly, it took only 833msecs now
runs perfectly
I'm going to keep on ask
On Thu, 11 Jul 2002 19:40:46 +0200, "Luis Alberto Amigo Navarro"
<[EMAIL PROTECTED]> wrote:
>I've tried
[reformatted to fit on one page]
| SELECT supplier.name, supplier.address
| FROM supplier, nation, lineitem
You already found out that you do not need lineitem here.
| WHERE EXISTS(
| SELECT
[moving to pgsql-sql]
On Thu, 11 Jul 2002 17:22:14 +0200, "Luis Alberto Amigo Navarro"
<[EMAIL PROTECTED]> wrote:
>I can't improve performance on this query:
>
>SELECT
> supplier.name,
> supplier.address
>FROM
> supplier,
> nation
>WHERE
> supplier.suppkey IN(
> SELECT
> partsupp.suppkey
> FRO
12 matches
Mail list logo
