[SQL] Database consistency after a power shortage
My question is regarding a potential situation: I have a program that inserts values on 3 tables linked to each other. My program is used in a POS. In this specific case, the program has to update the tables "header_invoice", "detail_invoice" and "payments_x_header_invoice". In a normal operation, the program should insert first a registry on "header_invoice", then insert N registries on "detail_invoice" referencing the header_invoice number. After that it should insert N registries regarding the payments related to the header_invoice, referencing again the invoice. So the order goes like this: 1) Insert 1 new registry on "header_invoice" 2) Insert N registries on "detail_invoice" referencing header_invoice 3) Insert N registries on "payments_x_header_invoice" referencing the header_invoice If lets say the header_invoice registry was inserted, operation was committed and then a power shortage occurs and the system shuts down. In that case the database will never know that more registries had to be inserted, because that happened on the application level. Is there any way to make the 3 operations be one transaction for the database, so that it keeps them all consistent in case a power shortage occurs in the middle?
[SQL] Encrypt data type LO
Hi fellows, I need to encrypt fields of data type LO (LO is included in the contrib section of PostgreSQL) and I don’t know if pgcrypto is the way to do that or there is another way. If anyone knows the answer of my problem or know an alternative way to do this, I’ll appreciate you can share this with me. Regards. Ing. Carlos Alberto Piña Uribe Consultoría y Desarrollo KEBIR Infraestructura Tecnológica [EMAIL PROTECTED]
[SQL] select result into string's array
Hi,
I need to store the result of select into an array of string:
/create or replace function search_engine.test/
/(/
/)/
/returns integer as $$/
/declare/
/m_array text[];/
/begin/
/for m_array in select * from my_table loop/
/raise notice 'valor 1: %',m_array;/
/end loop;/
/return 1;/
/end; $$ LANGUAGE plpgsql;/
This launch this errors:
/ERROR: array value must start with "{" or dimension information
SQL state: 22P02
Context: PL/pgSQL function "test" line 4 at FOR over SELECT rows/
Is it possible do this?? May I choose another way?
Thanks in advance
Alberto,
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] select result into string's array
Dmitriy Igrishin wrote:
> Hello.
>
> You should use an array constructor:
>
> DECLARE
> m_array text[];
> [..]
> BEGIN
> FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP
> [..]
> END LOOP;
>
> Regards,
> Igrishin Dmitriy.
>
> 2009/10/9 Alberto Asuero Arroyo <mailto:albertoasu...@gmail.com>>
>
> Hi,
>
> I need to store the result of select into an array of string:
>
>/create or replace function search_engine.test/
>/(/
>/)/
>/returns integer as $$/
>/declare/
>/m_array text[];/
>/begin/
>/for m_array in select * from my_table loop/
>/raise notice 'valor 1: %',m_array;/
>/end loop;/
>/return 1;/
>/end; $$ LANGUAGE plpgsql;/
>
>
> This launch this errors:
>
>/ERROR: array value must start with "{" or dimension information
>SQL state: 22P02
>Context: PL/pgSQL function "test" line 4 at FOR over SELECT rows/
>
>
> Is it possible do this?? May I choose another way?
>
> Thanks in advance
>
> Alberto,
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
> <mailto:pgsql-sql@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
It's has been really useful for my to solve the dinamic Record
Introspection problem that I had.
Thanks,
Alberto
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [HACKERS] please help on query
> 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 asking about another query: SELECT customer.name, customer.custkey, orders.orderkey, orders.orderdate, orders.totalprice, sum(lineitem.quantity) FROM customer, orders, lineitem WHERE exists( SELECT lineitem.orderkey FROM lineitem WHERE lineitem.orderkey=orders.orderkey GROUP BY lineitem.orderkey HAVING sum(lineitem.quantity)>300 ) AND customer.custkey=orders.custkey AND orders.orderkey=lineitem.orderkey GROUP BY customer.name, customer.custkey, orders.orderkey, orders.orderdate, orders.totalprice ORDER BY orders.totalprice DESC, orders.orderdate; NOTICE: QUERY PLAN: Sort (cost=26923941.97..26923941.97 rows=300061 width=66) -> Aggregate (cost=26851634.86..26896644.05 rows=300061 width=66) -> Group (cost=26851634.86..26889142.52 rows=3000612 width=66) -> Sort (cost=26851634.86..26851634.86 rows=3000612 width=66) -> Hash Join (cost=26107574.81..26457309.10 rows=3000612 width=66) -> Seq Scan on lineitem (cost=0.00..08.25 rows=6001225 width=8) -> Hash (cost=26105699.81..26105699.81 rows=75 width=58) -> Hash Join (cost=7431.00..26105699.81 rows=75 width=58) -> Seq Scan on orders (cost=0.00..26083268.81 rows=75 width=25) SubPlan -> Aggregate (cost=0.00..17.35 rows=1 width=8) -> Group (cost=0.00..17.34 rows=5 width=8) -> Index Scan using lineitem_pkey on lineitem (cost=0.00..17.33 rows=5 width=8) -> Hash (cost=7056.00..7056.00 rows=15 width=33) -> Seq Scan on customer (cost=0.00..7056.00 rows=15 width=33) again: orders 150 tuples lineitem 600 tuples there are 1 to 7 lineitems per orderkey Customer 15 tuples select attname,n_distinct,correlation from pg_stats where tablename='lineitem'; attname| n_distinct | correlation ---++- orderkey | -0.199847 | 1 partkey | 196448 | 0.0223377 suppkey | 9658 | -0.00822751 linenumber| 7 | 0.17274 quantity | 50 | 0.0150153 extendedprice | 25651 | -0.00790245 discount | 11 |0.103761 tax | 9 | 0.0993771 returnflag| 3 |0.391434 linestatus| 2 |0.509791 shipdate | 2440 | 0.0072777 commitdate| 2497 | 0.00698162 receiptdate | 2416 | 0.00726686 shipinstruct | 4 |0.241511 shipmode | 7 |0.138432 comment | 275488 | 0.0188006 (16 rows) select attname,n_distinct,correlation from pg_stats where tablename='orders'; attname| n_distinct | correlation ---++- orderkey | -1 | -0.25 custkey | 76309 | 0.00590596 orderstatus | 3 |0.451991 totalprice| -1 | -0.00768806 orderdate | 2431 | -0.0211354 orderpriority | 5 |0.182489 clerk | 1009 | 0.00546939 shippriority | 1 | 1 comment | -0.750125 | -0.0123887 Customer attname | n_distinct | correlation ++- custkey| -1 | 1 name | -1 | 1 address| -1 | -0.00510274 nationkey | 25 | 0.0170533 phone | -1 | -0.0227816 acctbal| -0.83444 | -0.00220958 mktsegment | 5 |0.205013 comment| -1 | 0.0327827 This query takes 12 minutes to run and returns about 50 customers. lineitem.quantity takes values from 1 to 50, so 300 per orderkey is very restrictive May someone help on improving performance? Again thanks in advance Regards ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [HACKERS] please help on query
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 lineitem.shipdate<(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE
AND EXISTS(
SELECT
part.partkey
FROM
part
WHERE
part.name like 'forest%'
)
GROUP BY partsupp.partkey,partsupp.suppkey
HAVING min(availqty)>(0.5*(sum(lineitem.quantity)::FLOAT))
)
AND supplier.nationkey=nation.nationkey
AND nation.name='CANADA'
ORDER BY
supplier.name;
as you said and something is wrong
Sort (cost=1141741215.35..1141741215.35 rows=240049 width=81)
InitPlan
-> Aggregate (cost=0.00..921773.85 rows=48 width=24)
InitPlan
-> Seq Scan on part (cost=0.00..8561.00 rows=1 width=4)
-> Group (cost=0.00..921771.44 rows=481 width=24)
-> Result (cost=0.00..921769.04 rows=481 width=24)
-> Merge Join (cost=0.00..921769.04 rows=481
width=24)
-> Index Scan using partsupp_pkey on partsupp
(cost=0.00..98522.75 rows=80 width=12)
-> Index Scan using lsupp_index on lineitem
(cost=0.00..821239.91 rows=145 width=12)
-> Result (cost=1.31..112888690.31 rows=240049 width=81)
-> Nested Loop (cost=1.31..112888690.31 rows=240049 width=81)
-> Hash Join (cost=1.31..490.31 rows=400 width=81)
-> Seq Scan on supplier (cost=0.00..434.00 rows=1
width=77)
-> Hash (cost=1.31..1.31 rows=1 width=4)
-> Seq Scan on nation (cost=0.00..1.31 rows=1
width=4)
-> Seq Scan on lineitem (cost=0.00..08.25 rows=6001225
width=0)
where might be my mistake
Thanks and regards
- Original Message -
From: "Manfred Koizar" <[EMAIL PROTECTED]>
To: "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, July 11, 2002 6:47 PM
Subject: Re: [HACKERS] please help on query
> [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
> > FROM
> > partsupp
> > WHERE
> > partsupp.partkey IN(
> >SELECT
> > part.partkey
> >FROM
> > part
> >WHERE
> > part.name like 'forest%'
> > )
> > AND partsupp.availqty>(
> >SELECT
> > 0.5*(sum(lineitem.quantity)::FLOAT)
> >FROM
> > lineitem
> >WHERE
> > lineitem.partkey=partsupp.partkey
> > AND lineitem.suppkey=partsupp.partkey
> ^^^
> suppkey ???
> > AND lineitem.shipdate>=('1994-01-01')::DATE
> > AND lineitem.shipdate<(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE
> > )
> > )
> > AND supplier.nationkey=nation.nationkey
> > AND nation.name='CANADA'
> >ORDER BY
> > supplier.name;
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [HACKERS] please help on query
Lineitem is being modified on run time, so creating a temp table don't solves my problem The time of creating this table is the same of performing the subselect (or so I think), it could be done creating a new table, and a new trigger, but there are already triggers to calculate lineitem.extendedprice=part.retailprice*lineitem.quantity*(1+taxes)*(1-disco unt) and to calculate orderstatus in order with linestatus and to calculate orders.totalprice as sum(extendedprice) where lineitem.orderkey=new.orderkey. A new trigger in order to insert orderkey if sum(quantity) where orderkey=new.orderkey might be excessive. Any other idea? Thanks And Regards - Original Message - From: "Jakub Ouhrabka" <[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 > 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); > > SELECT > customer.name, > customer.custkey, > orders.orderkey, > orders.orderdate, > orders.totalprice, > sum(lineitem.quantity) > FROM > customer, > orders, > lineitem, > tmp > WHERE > orders.orderkey=tmp.orderkey > AND customer.custkey=orders.custkey > AND orders.orderkey=lineitem.orderkey > GROUP BY > customer.name, > customer.custkey, > orders.orderkey, > orders.orderdate, > orders.totalprice > ORDER BY > orders.totalprice DESC, > orders.orderdate; > > > may be the index is not necessary... > > kuba > > > > I'm going to keep on asking about another query: > > > > SELECT > > customer.name, > > customer.custkey, > > orders.orderkey, > > orders.orderdate, > > orders.totalprice, > > sum(lineitem.quantity) > > FROM > > customer, > > orders, > > lineitem > > WHERE > > exists( > > SELECT > >lineitem.orderkey > > FROM > >lineitem > > WHERE > >lineitem.orderkey=orders.orderkey > > GROUP BY > >lineitem.orderkey HAVING > >sum(lineitem.quantity)>300 > > ) > > AND customer.custkey=orders.custkey > > AND orders.orderkey=lineitem.orderkey > > GROUP BY > > customer.name, > > customer.custkey, > > orders.orderkey, > > orders.orderdate, > > orders.totalprice > > > > ORDER BY > > orders.totalprice DESC, > > orders.orderdate; > > > > NOTICE: QUERY PLAN: > > > > Sort (cost=26923941.97..26923941.97 rows=300061 width=66) > > -> Aggregate (cost=26851634.86..26896644.05 rows=300061 width=66) > > -> Group (cost=26851634.86..26889142.52 rows=3000612 width=66) > > -> Sort (cost=26851634.86..26851634.86 rows=3000612 > > width=66) > > -> Hash Join (cost=26107574.81..26457309.10 > > rows=3000612 width=66) > > -> Seq Scan on lineitem (cost=0.00..08.25 > > rows=6001225 width=8) > > -> Hash (cost=26105699.81..26105699.81 > > rows=75 width=58) > > -> Hash Join (cost=7431.00..26105699.81 > > rows=75 width=58) > > -> Seq Scan on orders > > (cost=0.00..26083268.81 rows=75 width=25) > > SubPlan > > -> Aggregate > > (cost=0.00..17.35 rows=1 width=8) > > -> Group > > (cost=0.00..17.34 rows=5 width=8) > > -> Index Scan > > using lineitem_pkey on lineitem (cost=0.00..17.33 rows=5 width=8) > > -> Hash (cost=7056.00..7056.00 > > rows=15 width=33) > > -> Seq Scan on customer > > (cost=0.00..7056.00 rows=15 width=33) > > > > again: > > orders 150 tuples > > lineitem 600 tuples there are 1 to 7 lineitems per orderkey > > Customer 15 tuples > > > > select attname,n_distinct,correlation from pg_stats where > > tablename=&#
Re: [SQL] [HACKERS] please help on query
- 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: 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=10.79..274259.16 rows=6001225 loops=1) -> Index Scan using lineitem_pkey on lineitem (cost=0.00..617154.97 rows=6001225 width=8) (actual time=10.77..162439.11 rows=6001225 loops=1) Total runtime: 347330.28 msec it is returning all rows in lineitem. Why is it using index? Thanks and regards > On Fri, 12 Jul 2002 17:32:50 +0200 > "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> wrote: > > > > Lineitem is being modified on run time, so creating a temp table don't > > solves my problem > > The time of creating this table is the same of performing the subselect (or > > so I think), it could be done creating a new table, and a new trigger, but > > there are already triggers to calculate > > lineitem.extendedprice=part.retailprice*lineitem.quantity*(1+taxes)*(1-disco > > unt) and to calculate orderstatus in order with linestatus and to calculate > > orders.totalprice as sum(extendedprice) where > > lineitem.orderkey=new.orderkey. A new trigger in order to insert orderkey if > > sum(quantity) where orderkey=new.orderkey might be excessive. > > Any other idea? > > Thanks And Regards > > > > - Original Message - > > From: "Jakub Ouhrabka" <[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 > > > > > > > > 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; > > > Hi, > > I'm not sure whether its performance can be improved or not. But I feel > there is a slight chance to reduce the total number of the tuples which > Planner must think. > > BTW, how much time does the following query take in your situation, > and how many rows does it retrieve ? > > > EXPLAIN ANALYZE > SELECT > lineitem.orderkey > FROM > lineitem > GROUP BY > lineitem.orderkey > HAVING > SUM(lineitem.quantity) > 300; > > > > Regards, > Masaru Sugawara > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [HACKERS] please help on query
- 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, I don't know the reason. > I need more info. Can you show me the outputs of EXPLAIN ANALYZE ? > Here it is: > > EXPLAIN ANALYZE > SELECT > orders.orderkey > FROM > lineitem LEFT OUTER JOIN > orders USING(orderkey) > WHERE > orders.orderkey IS NOT NULL > GROUP BY > orders.orderkey > HAVING > SUM(lineitem.quantity) > 300; > 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=1233968.87..1385034.91 rows=6001225 loops=1) -> Sort (cost=1257368.92..1257368.92 rows=6001225 width=12) (actual time=1233968.82..1276147.37 rows=6001225 loops=1) -> Hash Join (cost=166395.00..520604.08 rows=6001225 width=12) (actual time=59061.21..773997.08 rows=6001225 loops=1) -> Seq Scan on lineitem (cost=0.00..195405.25 rows=6001225 width=8) (actual time=20.66..115511.34 rows=6001225 loops=1) -> Hash (cost=162645.00..162645.00 rows=150 width=4) (actual time=59032.16..59032.16 rows=0 loops=1) -> Seq Scan on orders (cost=0.00..162645.00 rows=150 width=4) (actual time=17.33..44420.10 rows=150 loops=1) Total runtime: 1454929.11 msec > > > EXPLAIN ANALYZE > SELECT > t2.* > FROM (SELECT > orders.orderkey > FROM >lineitem LEFT OUTER JOIN >orders USING(orderkey) > WHERE >orders.orderkey IS NOT NULL > GROUP BY >orders.orderkey > HAVING > SUM(lineitem.quantity) > 300 >) AS t1 LEFT OUTER JOIN >orders AS t2 USING(orderkey) > ORDER BY t2.custkey > Sort (cost=1739666.43..1739666.43 rows=600122 width=119) (actual time=1538897.23..1538897.47 rows=62 loops=1) -> Merge Join (cost=1344971.49..1682069.98 rows=600122 width=119) (actual time=1440886.58..1538886.03 rows=62 loops=1) -> Index Scan using orders_pkey on orders t2 (cost=0.00..324346.65 rows=150 width=115) (actual time=32.80..87906.98 rows=1455276 loops=1) -> Sort (cost=1344971.49..1344971.49 rows=600122 width=12) (actual time=1439550.31..1439550.73 rows=62 loops=1) -> Subquery Scan t1 (cost=1257368.92..1287375.04 rows=600122 width=12) (actual time=1222560.86..1439549.36 rows=62 loops=1) -> Aggregate (cost=1257368.92..1287375.04 rows=600122 width=12) (actual time=1222560.84..1439548.42 rows=62 loops=1) -> Group (cost=1257368.92..1272371.98 rows=6001225 width=12) (actual time=1219607.04..1369327.42 rows=6001225 loops=1) -> Sort (cost=1257368.92..1257368.92 rows=6001225 width=12) (actual time=1219607.00..1261208.08 rows=6001225 loops=1) -> Hash Join (cost=166395.00..520604.08 rows=6001225 width=12) (actual time=65973.31..769253.41 rows=6001225 loops=1) -> Seq Scan on lineitem (cost=0.00..195405.25 rows=6001225 width=8) (actual time=20.07..115247.61 rows=6001225 loops=1) -> Hash (cost=162645.00..162645.00 rows=150 width=4) (actual time=65943.80..65943.80 rows=0 loops=1) -> Seq Scan on orders (cost=0.00..162645.00 rows=150 width=4) (actual time=39.04..52049.90 rows=150 loops=1) Total runtime: 1539010.00 msec Thanks and regards ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] why is postgres estimating so badly?
I have a query and estimations and results don´t look similar, here is explain analyze: NOTICE: QUERY PLAN: Sort (cost=12443.90..12443.90 rows=1 width=93) (actual time=505331.94..505332.67 rows=175 loops=1) -> Aggregate (cost=12443.88..12443.89 rows=1 width=93) (actual time=472520.29..505326.48 rows=175 loops=1) -> Group (cost=12443.88..12443.89 rows=1 width=93) (actual time=472307.31..485173.92 rows=325302 loops=1) -> Sort (cost=12443.88..12443.88 rows=1 width=93) (actual time=472307.24..473769.79 rows=325302 loops=1) -> Nested Loop (cost=12439.25..12443.87 rows=1 width=93) (actual time=103787.68..441614.43 rows=325302 loops=1) -> Hash Join (cost=12439.25..12440.64 rows=1 width=85) (actual time=103733.76..120916.86 rows=325302 loops=1) -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=15) (actual time=7.81..8.72 rows=25 loops=1) -> Hash (cost=12439.25..12439.25 rows=1 width=70) (actual time=103722.25..103722.25 rows=0 loops=1) -> Nested Loop (cost=0.00..12439.25 rows=1 width=70) (actual time=95.43..100162.91 rows=325302 loops=1) -> Nested Loop (cost=0.00..12436.23 rows=1 width=62) (actual time=84.91..47502.93 rows=325302 loops=1) -> Nested Loop (cost=0.00..12412.93 rows=4 width=24) (actual time=66.86..8806.01 rows=43424 loops=1) -> Seq Scan on part (cost=0.00..12399.00 rows=1 width=4) (actual time=24.88..4076.81 rows=10856 loops=1) -> Index Scan using partsupp_pkey on partsupp (cost=0.00..13.89 rows=4 width=20) (actual time=0.20..0.34 rows=4 loops=10856) -> Index Scan using l_partsupp_index on lineitem (cost=0.00..6.02 rows=1 width=38) (actual time=0.20..0.61 rows=7 loops=43424) -> Index Scan using supplier_pkey on supplier (cost=0.00..3.01 rows=1 width=8) (actual time=0.08..0.10 rows=1 loops=325302) -> Index Scan using orders_pkey on orders (cost=0.00..3.22 rows=1 width=8) (actual time=0.85..0.87 rows=1 loops=325302)Total runtime: 505563.85 msec estimated 12000msec here is the query: SELECT nation, o_year, CAST((sum(amount))AS NUMERIC(10,2))AS sum_profitFROM( SELECT nation.name AS nation, EXTRACT(year FROM orders.orderdate) AS o_year, lineitem.extendedprice*(1-lineitem.discount)-partsupp.supplycost*lineitem.quantity AS amount FROM part, supplier, lineitem, partsupp, orders, nation WHERE supplier.suppkey=lineitem.suppkey AND partsupp.suppkey=lineitem.suppkey AND partsupp.partkey=lineitem.partkey AND part.partkey=lineitem.partkey AND orders.orderkey=lineitem.orderkey AND supplier.nationkey=nation.nationkey AND part.name LIKE '%green%' ) AS profitGROUP BY nation, o_yearORDER BY nation, o_year DESC; lineitem is about 6M rows partsupp 800K rows part 200K rows any advice? Thanks and regards
Re: [SQL] [HACKERS] why is postgres estimating so badly?
> > AND part.name LIKE '%green%' > > It's difficult for the planner to produce a decent estimate for the > selectivity of an unanchored LIKE clause, since there are no statistics > it can use for the purpose. We recently changed FIXED_CHAR_SEL in > src/backend/utils/adt/selfuncs.c from 0.04 to 0.20, which would make > this particular case come out better. (I believe the estimate would > work out to about 320, if part is 200K rows; that should be enough to > produce at least some change of plan.) You could try patching your > local installation likewise. Here are the results, worse than before: NOTICE: QUERY PLAN: Sort (cost=25209.88..25209.88 rows=1 width=93) (actual time=1836143.78..1836144.48 rows=175 loops=1) -> Aggregate (cost=25209.85..25209.87 rows=1 width=93) (actual time=1803559.97..1836136.47 rows=175 loops=1) -> Group (cost=25209.85..25209.86 rows=2 width=93) (actual time=1803348.04..1816093.89 rows=325302 loops=1) -> Sort (cost=25209.85..25209.85 rows=2 width=93) (actual time=1803347.97..1804795.41 rows=325302 loops=1) -> Hash Join (cost=25208.43..25209.84 rows=2 width=93) (actual time=1744714.61..1772790.19 rows=325302 loops=1) -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=15) (actual time=13.92..14.84 rows=25 loops=1) -> Hash (cost=25208.42..25208.42 rows=2 width=78) (actual time=1744603.74..1744603.74 rows=0 loops=1) -> Nested Loop (cost=0.00..25208.42 rows=2 width=78) (actual time=139.21..1740110.04 rows=325302 loops=1) -> Nested Loop (cost=0.00..25201.19 rows=2 width=70) (actual time=122.37..1687895.49 rows=325302 loops=1) -> Nested Loop (cost=0.00..25187.93 rows=4 width=62) (actual time=121.75..856097.27 rows=325302 loops=1) -> Nested Loop (cost=0.00..17468.91 rows=1280 width=24) (actual time=78.43..19698.77 rows=43424 loops=1) -> Seq Scan on part (cost=0.00..12399.00 rows=320 width=4) (actual time=29.57..4179.70 rows=10856 loops=1) -> Index Scan using partsupp_pkey on partsupp (cost=0.00..15.79 rows=4 width=20) (actual time=1.17..1.33 rows=4 loops=10856) -> Index Scan using l_partsupp_index on lineitem (cost=0.00..6.02 rows=1 width=38) (actual time=2.83..18.97 rows=7 loops=43424) -> Index Scan using orders_pkey on orders (cost=0.00..3.23 rows=1 width=8) (actual time=2.47..2.50 rows=1 loops=325302) -> Index Scan using supplier_pkey on supplier (cost=0.00..3.01 rows=1 width=8) (actual time=0.08..0.09 rows=1 loops=325302) Total runtime: 1836375.16 msec It looks even worse, another advice?, or maybe a query change. here is the query again: SELECT nation, o_year, CAST((sum(amount))AS NUMERIC(10,2))AS sum_profit FROM( SELECT nation.name AS nation, EXTRACT(year FROM orders.orderdate) AS o_year, lineitem.extendedprice*(1-lineitem.discount)-partsupp.supplycost*lineitem.qu antity AS amount FROM part, supplier, lineitem, partsupp, orders, nation WHERE supplier.suppkey=lineitem.suppkey AND partsupp.suppkey=lineitem.suppkey AND partsupp.partkey=lineitem.partkey AND part.partkey=lineitem.partkey AND orders.orderkey=lineitem.orderkey AND supplier.nationkey=nation.nationkey AND part.name LIKE '%green%' ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC; Thanks and regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] FUNCTIONS PROBLEM
Hi. i have a function and i need to return 4 fields but not work, any idea , please thank mario ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL]
HI, i have a following function., but not work. CREATE or replace FUNCTION get_nombre(numeric,numeric,text) RETURNS setof cumaalu AS ' SELECT cunomalu,cuappalu,cuapmalu FROM cumaalu WHERE siempalu = $1 and cusesalu = $2 and curutalu = $3; ' LANGUAGE SQL; the error say PostgreSQL ha dicho: ERROR: function declared to return cumaalu does not SELECT the right number of columns (49) Your query: CREATE FUNCTION get_nombre(numeric,numeric,text) RETURNS setof cumaalu AS ' SELECT cunomalu,cuappalu,cuapmalu FROM cumaalu WHERE siempalu = $1 and cusesalu = $2 and curutalu = $3; ' LANGUAGE SQL please any idea., i canṫ't use select * becose the table its very long. thank ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
