Обсуждение: SELECT INTO

Hi all

I'm tring to do some trigger and I must use a SELECT INTO,
look this:

CREATE OR REPLACE FUNCTION f_lancamentos_pagos() RETURNS opaque AS
'  DECLARE V_VALUE NUMERIC(12,2);
  DECLARE V_PAYMENT TIMESTAMP;
begin
       if tg_op = ''INSERT'' then
            SELECT INTO V_PAYMENT "DATE_PAYMENT",
                                      V_VALUE "VALUE"
            FROM  "PAYMENTS"
            WHERE
              "ID" = NEW.ID;

            ....
        end if
end'
LANGUAGE plpgsql;

but, when this function is called I'm getting the following error message:
ERROR: parser: parse error at or near ''''''

but if do it in 2 SELECTs it runs normally..


            SELECT INTO V_PAYMENT "DATE_PAYMENT"
                        FROM  "PAYMENTS"
            WHERE
              "ID" = NEW.ID;

            SELECT INTO V_VALUE "VALUE"
                      FROM  "PAYMENTS"
            WHERE
              "ID" = NEW.ID;


so, if I want get 10 columns, must I do 10 SELETCs???

thats wrong there??


Roberto de Amorim - +55 48 346-2243
Software engineer at SmartBit Software
Delphi and Interbase consultant
roberto@smartbit.inf.br

Hi Everybody,


I have this  following query : select count(*) from "Data" where
"IDOrigin"='29';
It's take more less 2 minutes to run... It's really slow...
Some one maybe knows about how to optimize "select count"

Below, there are the expalin analyse of the query.


EXPLAIN ANALYZE select count(*) from "Data" where "IDOrigin"='29';
NOTICE:  QUERY PLAN:

Aggregate  (cost=108945.77..108945.77 rows=1 width=0) (actual
time=108845.29..108845.30 rows=1 loops=1)
  ->  Seq Scan on Data (cost=0.00..107152.11 rows=717462 width=0) (actual
time=18233.46..106927.60 rows=1457826 loops=1)
Total runtime: 108845.50 msec.


I did already a lot of optimization such as modify shared_buffers and
sort_mem...
But always too slow...


Thanks, Areski

> EXPLAIN ANALYZE select count(*) from "Data" where "IDOrigin"='29';
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=108945.77..108945.77 rows=1 width=0) (actual
> time=108845.29..108845.30 rows=1 loops=1)
>   ->  Seq Scan on Data (cost=0.00..107152.11 rows=717462 width=0) (actual
> time=18233.46..106927.60 rows=1457826 loops=1)
> Total runtime: 108845.50 msec.

Well, do you have an index on Data.IDOrigin ?

Regards,
Bjoern

On Wednesday 13 Nov 2002 10:24 am, Roberto de Amorim wrote:
> Hi all
>
> I'm tring to do some trigger and I must use a SELECT INTO,
> look this:
>
> CREATE OR REPLACE FUNCTION f_lancamentos_pagos() RETURNS opaque AS
> '  DECLARE V_VALUE NUMERIC(12,2);
>   DECLARE V_PAYMENT TIMESTAMP;
> begin
>        if tg_op = ''INSERT'' then
>             SELECT INTO V_PAYMENT "DATE_PAYMENT",
>                                       V_VALUE "VALUE"
>             FROM  "PAYMENTS"

I think perhaps it should be:
    SELECT INTO V_PAYMENT,V_VALUE "DATE_PAYMENT","VALUE"...

> so, if I want get 10 columns, must I do 10 SELETCs???

If you want 10 columns, you might prefer to select into a record variable and
reference the columns as required.

http://www.postgresql.org/idocs/index.php?plpgsql-statements.html

--
  Richard Huxton

"Roberto de Amorim" <roberto@smartbit.inf.br> writes:
>             SELECT INTO V_PAYMENT "DATE_PAYMENT",
>                                       V_VALUE "VALUE"
>             FROM  "PAYMENTS"
>             WHERE
>               "ID" = NEW.ID;

That's not the right syntax.  Try

            SELECT INTO V_PAYMENT, V_VALUE  "DATE_PAYMENT", "VALUE"
            FROM  ... etc ...

            regards, tom lane

Yes of course...
I have more less 2 millions of records on this table...

----- Original Message -----
From: "Björn Metzdorf" <bm@turtle-entertainment.de>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, November 13, 2002 12:25 PM
Subject: Re: [GENERAL] Performance : Optimize query


> > EXPLAIN ANALYZE select count(*) from "Data" where "IDOrigin"='29';
> > NOTICE:  QUERY PLAN:
> >
> > Aggregate  (cost=108945.77..108945.77 rows=1 width=0) (actual
> > time=108845.29..108845.30 rows=1 loops=1)
> >   ->  Seq Scan on Data (cost=0.00..107152.11 rows=717462 width=0)
(actual
> > time=18233.46..106927.60 rows=1457826 loops=1)
> > Total runtime: 108845.50 msec.
>
> Well, do you have an index on Data.IDOrigin ?
>
> Regards,
> Bjoern
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

On Wed, 13 Nov 2002, Areski wrote:

> Hi Everybody,
>
>
> I have this  following query : select count(*) from "Data" where
> "IDOrigin"='29';
> It's take more less 2 minutes to run... It's really slow...
> Some one maybe knows about how to optimize "select count"
>
> Below, there are the expalin analyse of the query.
>
>
> EXPLAIN ANALYZE select count(*) from "Data" where "IDOrigin"='29';
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=108945.77..108945.77 rows=1 width=0) (actual
> time=108845.29..108845.30 rows=1 loops=1)
>   ->  Seq Scan on Data (cost=0.00..107152.11 rows=717462 width=0) (actual
> time=18233.46..106927.60 rows=1457826 loops=1)
> Total runtime: 108845.50 msec.

Let's go through the normal stuff :)

Have you used vacuum analyze recently?  How many rows are in the table?
How many rows actually have IDOrigin=29 (ie, is 717462 a valid estimate)?
If it's not a reasonable estimate, you might want to raise the number of
statistic buckets the column is getting (alter table "Data" alter
column "IDOrigin" SET STATISTICS <number> where the default value is 10)
and running vacuum analyze again.