[SQL] How To Increment A Field Value By Function

[cn]

Hello!

I have read pgsql online docs but still need helps. (And pardon me for
cross posting to pgsql-novice as I have  not received responses to my
problem.)

I want to increment the value of field SocialSecurityNo in a centrally
controlled table SocialSecurityTable. SocialSecurityTable contains only
one row and one field - SocialSecurityNo. Because, I think,
SocialSecurityNo in SocialSecurityTable should not even be read by any
other user when it is being incremented by a user (or the number will
duplicate), and setting transaction isolation level seems to be not
restrictive enough in this case, I use table lock as follows:

CREATE FUNCTION GetAndSetNextNumber () RETURNS TEXT AS '
DECLARE
  UsedNumber TEXT;
BEGIN
  BEGIN WORK;
  LOCK SocialSecurityTable IN ROW EXCLUSIVE MODE;
  SELECT SocialSecurityNo INTO UsedNumber FROM SocialSecurityTable;
--Do a lot of calculation on UsedNumber
-- and generate the next free SocialSecurityNo
-- and assign this free value to UsedNumber.
  UPDATE SocialSecurityTable SET SocialSecurityNo=UsedNumber;
  COMMIT WORK;
  RETURN UsedNumber;
END;' LANGUAGE 'plpgsql';

Question A: Is this above function apporpriately designed, or are there
more efficient ways than table locking?

Question B: I think statement "LOCK SocialSecurityTable IN ROW EXCLUSIVE
MODE;" will fail when this user (user A) executed this statement AFTER
another user (user B) and user B has not yet COMMITed his transaction?
What are the solutions for this (LOCK TABLE fails in function)?

Regards,

CN

[SQL] Which Approach Performs Better?

[CN]

Hi!

I have a tree table:

CREATE TABLE tree (
CONSTRAINT fktree FOREIGN KEY (parent) REFERENCES tree (dept),
dept int primary key, --department
parent int
);

insert into tree values(1,1);
insert into tree values(2,1);
insert into tree values(3,2);

and a history table:

CREATE TABLE history (
CONSTRAINT fkhistory FOREIGN KEY (dept) REFERENCES tree (dept),
dept int primary key, --department
amount int
);

insert into history values(1,100);
insert into history values(2,200);
insert into history values(3,300);

My purpose is to retrieve the amount detail of department "1" and all
departments under it.
I have come out with 2 approaches:

APPROACH A:

--Returns TRUE if department $2 reports to department $1.
CREATE FUNCTION IsChild(TEXT,TEXT) RETURNS BOOLEAN AS '
DECLARE
  p ALIAS FOR $1; --parent
  c ALIAS FOR $2; --child
  vparent INT;
BEGIN
  IF c = p THEN RETURN TRUE; END IF;
  SELECT parent INTO vparent FROM tree WHERE dept=c;
  IF NOT FOUND THEN
RETURN FALSE;
  ELSE
RETURN IsChild(p,vparent);
  END IF;
END' LANGUAGE 'plpgsql' STABLE;

SELECT amount FROM history WHERE IsChild(1,dept);
-
APPROACH B:

(Assuming the number of layers of this tree is predicatable. Let's take 3
layers as an example.)

SELECT amount FROM history WHERE
dept=1
OR dept IN (SELECT dept FROM tree WHERE parent=1)
OR dept IN (SELECT dept FROM tree WHERE parent IN (SELECT dept FROM tree
WHERE parent=1));

Both queries return

 amount

100
200
300
(3 rows)

APPROACH A is obviously easier to implement.
My question is which approach gets better performance when the number of
rows in history and the layers in tree grows?
I don't intend to apply "Joy's worm" tree algorism as it is too
complicate to me to understand.

Thank you in advance for any input!

Regards,

CN

-- 
http://www.fastmail.fm - A no graphics, no pop-ups email service


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Extraordinary Full Join

[CN]

Hello! postgreSQL lovers,

Master table tmaster has 2 childern tables tbook and thobby.

CREATE TABLE tmaster (id TEXT,name TEXT);
CREATE TABLE tbook (id TEXT, book TEXT);
CREATE TABLE thobby (id TEXT, hobby TEXT);

INSERT INTO tmaster VALUES ('m1','John');
INSERT INTO tmaster VALUES ('m2','Mary');

INSERT INTO tbook VALUES ('m1','book1');
INSERT INTO tbook VALUES ('m1','book2');

INSERT INTO thobby VALUES ('m1','hobby1');
INSERT INTO thobby VALUES ('m1','hobby2');

I want to list John's books and hobbies in one table:

 id | name |  book  | hobby
+--++---
 m1 | John | book1  |
 m1 | John | book2  |
 m1 | John ||hobby1
 m1 | John ||hobby2

or

 id | name |  book  | hobby
+--++---
 m1 | John ||hobby1
 m1 | John ||hobby2
 m1 | John | book1  |
 m1 | John | book2  |

or

 id | name |  book  | hobby
+--++---
 m1 | John ||hobby1
 m1 | John | book1  |
 m1 | John ||hobby2
 m1 | John | book2  |

etc.

What is the SQL to make any one of above resuts?

Even better, if possible, "sort" book and hobby column descendently to
fill up null columns:

 id | name |  book  | hobby
+--++---
 m1 | John | book1  |hobby1
 m1 | John | book2  |hobby2

What is the better-have SQL to produce the last furnished list?

TIA

CN

-- 
http://www.fastmail.fm - Choose from over 50 domains or use your own


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Extraordinary Full Join

[CN]

Thank you! Josh,

> You'll need to add an "sort_no" column to both tbook and thobby, and
> ensure somehow that both tables have the exact same sequence of sort numbers. 
> Then you can:
> 
> select tmaster.id, tmaster.name, tbook.book, thobby.hobby
> from tmaster, tbook, thobbby
> where tmaster.id = tbook.id 
>   and tmaster.id = thobby.id
>   and tbook.sort_no = thobby.sort_no
> order by name, tbook.sort_no
> 
> There would also be ways for you to account for having a different number
> of 
> books or hobbies, but that would be a much more complex query.

Perhaps I did not make my requirement clear. My ultimate goal is to

(1) list not null values first in columns book and hobby
(2) list book and hobby in either descending or ascending order

For example, improving

id | name |  book  | hobby
---+--++---
m1 | John ||hobby1
m1 | John | book2  |
m1 | John ||hobby2
m1 | John | book1  |hobby3

to gain (for descending)

id | name |  book  | hobby
---+--++---
m1 | John | book2  |hobby3
m1 | John | book1  |hobby2
m1 | John ||hobby1

or gain (for ascending)

id | name |  book  | hobby
---+--++---
m1 | John | book1  |hobby1
m1 | John | book2  |hobby2
m1 | John ||hobby3

I don't want to add "sort_to" column to any table because I have more
tables similar to book and hobby. For example,

CREATE TABLE tfriend(id TEXT, friend TEXT);
CREATE TABLE tfriend(id TEXT, education TEXT);

and hopefully to get

id | name |  book  | hobby | friend  | education 
---+--++---+-+---
m1 | John | book2  |hobby3 | FriendA | Edu C
m1 | John | book1  |hobby2 | | Edu B
m1 | John ||hobby1 | | Edu A

Do I have any chance to achieve the ultimate goal?
I need, at least, the correct result for the worst case like:

id | name |  book  | hobby
---+--++---
m1 | John | book1  |
m1 | John |    | hobby2
m1 | John | book2  | 
m1 | John || hobby1

Regards,

CN

-- 
http://www.fastmail.fm - Choose from over 50 domains or use your own


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] Creating Index

[CN]

Hi!

CREATE TABLE table1
( d DATE PRIMARY KEY,
  amount INTEGER
);

CREATE TABLE table2
( PRIMARY KEY (y,m),
  y INTEGER,
  m INTEGER
  amount INTEGER
);

CREATE VIEW view1 AS
SELECT EXTRACT(YEAR FROM d) AS year, EXTRACT(MONTH FROM d) AS month,
amount
UNION ALL
SELECT * from table2;

Table1 contains 9000 rows and table2 contains 0 row. This query, which
takes 13489 msec, is extremely slow as pgsql sequentially scans all rows
in table1:

EXPLAIN ANALYZE SELECT COUNT(*) FROM view1;

I am in the impression that building an index on column d surely will
help improve the performance but I am not smart enough to apply its usage
explained in the manual.
I would much appreciate if anyone could show me how to build that index
something similar to (I guess) the following query (which is illegal of
course):

CREATE INDEX index1 ON table1 EXTRACT(YEAR FROM d) || EXTRACT(MONTH FROM
d);

TIA
CN

-- 
http://www.fastmail.fm - Faster than the air-speed velocity of an
  unladen european swallow

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Creating Index

[CN]

Peter, Thanks a lot!

> Unqualified count() cannot use an index because it has to visit all the
> rows in the table.

It is only for my test. In my real practice, queries like
"SELECT * FROM view1 WHERE year = 2003 AND month BETWEEN 10 AND 12"
will be performed.

> Then again, I don't quite believe that visiting 9000
> rows takes 13 seconds.  Can you show us the result of EXPLAIN ANALYZE and
> your real table and view definitions, because the ones you showed
> contained a few syntax errors.

Sure. I did not post the real script as I did not want to make readers
feel headache :-).
The following view is, again, a simplified version. The real version,
which takes 13 seconds, joins 2 more tables.


CREATE TABLE table1 (
PRIMARY KEY (f0,f1),
f0 VARCHAR(20),
f1 VARCHAR(20),
f2 DATE,
f3 DATE,
f4 "char",
f5 VARCHAR(30)
)WITHOUT OIDS;
CREATE INDEX itable1f2 ON table1 (f2);

CREATE TABLE table2 (
PRIMARY KEY (f0,f1,f2),
f0 VARCHAR(20),
f1 VARCHAR(20),
f2 INTEGER,
f3 VARCHAR(20),
f4 "char",
f5 CHAR(3),
f6 NUMERIC,
f7 NUMERIC,
f8 VARCHAR(20),
f9 "char",
f10 VARCHAR(80),
f11 VARCHAR(20)
)WITHOUT OIDS;

CREATE TABLE table3 (
PRIMARY KEY (f0,f1,f2,f3,f4),
f0 VARCHAR(20),
f1 INTEGER,
f2 VARCHAR(20),
f3 VARCHAR(20),
f4 INTEGER,
f5 INTEGER
)WITHOUT OIDS;

CREATE OR REPLACE VIEW view1 AS
SELECT table1.f0 AS company
  ,FALSE AS IsBudget
  ,EXTRACT(YEAR FROM table1.f2) AS year
  ,EXTRACT(MONTH FROM table1.f2) AS month
  ,table2.f8 AS department
  ,table2.f3 AS account
  ,table2.f7 AS amount
FROM table1,table2
WHERE table2.f0=table1.f0 AND table2.f1=table1.f1

UNION ALL

SELECT f0,TRUE,f1,f4,f3,f2,f5 FROM table3;


db1=# \d table1
   Table "public.table1"
 Column | Type  | Modifiers 
+---+---
 f0 | character varying(20) | not null
 f1 | character varying(20) | not null
 f2 | date  | 
 f3 | date  | 
 f4 | "char"| 
 f5 | character varying(30) | 
Indexes: table1_pkey primary key btree (f0, f1),
 itable1f2 btree (f2)

db1=# \d table2
   Table "public.table2"
 Column | Type  | Modifiers 
+---+---
 f0 | character varying(20) | not null
 f1 | character varying(20) | not null
 f2 | integer   | not null
 f3 | character varying(20) | 
 f4 | "char"| 
 f5 | character(3)  | 
 f6 | numeric   | 
 f7 | numeric   | 
 f8 | character varying(20) | 
 f9 | "char"| 
 f10| character varying(80) | 
 f11| character varying(20) | 
Indexes: table2_pkey primary key btree (f0, f1, f2)

db1=# \d table3
   Table "public.table3"
 Column | Type  | Modifiers 
+---+---
 f0 | character varying(20) | not null
 f1 | integer   | not null
 f2 | character varying(20) | not null
 f3 | character varying(20) | not null
 f4 | integer   | not null
 f5 | integer   | 
Indexes: table3_pkey primary key btree (f0, f1, f2, f3, f4)

db1=# \d view1
View "public.view1"
   Column   |   Type| Modifiers 
+---+---
 company| character varying | 
 isbudget   | boolean   | 
 year   | double precision  | 
 month  | double precision  | 
 department | character varying | 
 account| character varying | 
 amount | numeric   | 
View definition: ((SELECT table1.f0 AS company, false AS isbudget,
date_part('year'::text, table1.f2) AS 
"year", date_part('month'::text, table1.f2) AS "month", table2.f8 AS
department, table2.f3 AS account, 
table2.f7 AS amount FROM table1, table2 WHERE ((table2.f0 = table1.f0)
AND (table2.f1 = table1.f1))) UNION 
ALL (SELECT table3.f0 AS company, true AS isbudget, table3.f1 AS "year",
table3.f4 AS "month", table3.f3 AS 
department, table3.f2 AS account, table3.f5 AS amount FROM table3));

db1=# EXPLAIN ANALYZE SELECT COUNT(*) FROM view1;
   QUERY 
PLAN  

 Aggregate  (cost=131.94..131.94 rows=1 width=324) (actual
 time=5025.00..5025.01 rows=1 loops=1)
   ->  Subquery Scan view1  (cost=0.00..129.38 rows=1025 width=324)
   (actual time=6.14..4862.74 rows=28482 
loops=1)
 ->  Append  (cost=0.00..129.38 rows=1025 width=324) (actual
 time=6.13..4677.45 rows=28482 loops=1)
   ->  Subquery Scan "

Re: [SQL] Creating Index

[CN]

> >->  Seq Scan on table1  (cost=0.00..20.00 rows=1000 width=0) (actual
> 
> Run VACUUM ANALYZE, then repost your EXPLAIN ANALYZE results please.

 QUERY 
PLAN 

 Aggregate  (cost=1858.09..1858.09 rows=1 width=156) (actual
 time=5089.34..5089.34 rows=1 loops=1)
   ->  Subquery Scan view1  (cost=187.86..1788.14 rows=27980 width=156)
   (actual time=187.74..4952.09 
rows=28482 loops=1)
 ->  Append  (cost=187.86..1788.14 rows=27980 width=156) (actual
 time=187.72..4787.18 rows=28482 
loops=1)
   ->  Subquery Scan "*SELECT* 1"  (cost=187.86..1788.14
   rows=27979 width=69) (actual 
time=187.72..4687.71 rows=28482 loops=1)
 ->  Hash Join  (cost=187.86..1788.14 rows=27979
 width=69) (actual time=187.68..4332.30 
rows=28482 loops=1)
   Hash Cond: ("outer".f1 = "inner".f1)
   Join Filter: ("outer".f0 = "inner".f0)
   ->  Seq Scan on table2  (cost=0.00..745.82
   rows=28482 width=47) (actual 
time=0.27..547.90 rows=28482 loops=1)
   ->  Hash  (cost=164.29..164.29 rows=9429
   width=22) (actual time=165.17..165.17 
rows=0 loops=1)
 ->  Seq Scan on table1 
 (cost=0.00..164.29 rows=9429 width=22)
 (actual 
time=0.23..89.18 rows=9429 loops=1)
   ->  Subquery Scan "*SELECT* 2"  (cost=0.00..0.00 rows=1
   width=156) (actual time=0.03..0.03 
rows=0 loops=1)
 ->  Seq Scan on table3  (cost=0.00..0.00 rows=1
 width=156) (actual time=0.01..0.01 rows=0 
loops=1)
 Total runtime: 5114.47 msec
(13 rows)

Thanks again! Gurus.
Regards,
CN

-- 
http://www.fastmail.fm - The professional email service

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Creating Index

[CN]

> You do realize that extract returns a double precision value not an
> integer, and it's probably not going to be willing to push clauses down
> through the union where the types are different .
>

Argh! I didn't noticed that. Thanks for the reminder.

Let's do not consider table2 and view1 for this moment and focus only on
table1.
Table1 in my original post was incorrect. Please forgive me! (I posted it
midnight when my head was not clear and tried to make my case simple for
understanding.) The correct one is:

CREATE TABLE table1
( id VARCHAR(20) PRIMARY KEY,
  d DATE,
  amount INTEGER
);
CREATE INDEX itable1 ON table1 (d);

EXPLAIN ANALYZE SELECT COUNT(*) FROM table1 WHERE EXTRACT(YEAR FROM d) >=
2001.0 AND EXTRACT(MONTH FROM d) >= 1.;

takes 630 msec on my AMD 450MHz machine. While

EXPLAIN ANALYZE SELECT COUNT(*) FROM table1 WHERE d >= '2001-1-1';

takes only 114 msec.
--
 Aggregate  (cost=535.20..535.20 rows=1 width=0) (actual
 time=625.10..625.11 rows=1 loops=1)
   ->  Seq Scan on table1  (cost=0.00..532.58 rows=1048 width=0) (actual
   time=14.84..605.85 rows=3603 loops=1)
 Filter: ((date_part('year'::text, f2) > 2001::double precision)
 AND (date_part('month'::text, f2) >= 
1::double precision))
 Total runtime: 626.61 msec

---
 Aggregate  (cost=464.12..464.12 rows=1 width=0) (actual
 time=114.28..114.28 rows=1 loops=1)
   ->  Seq Scan on table1  (cost=0.00..461.86 rows=902 width=0) (actual
   time=10.71..102.99 rows=3603 loops=1)
 Filter: (f2 >= '2002-01-01'::date)
 Total runtime: 114.50 msec

Does the first query perform sequential scan?
If a composit index (year,month) derived from column "d" helps and is
available, then someone please show me how to build that index like:

CREATE INDEX i1 ON table1 

Is creating a function that eats DATE as argument to build that index my
only solution?

Best Regards,

CN

-- 
http://www.fastmail.fm - The professional email service

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] help with rule and notification

[CN]

> so anyone listening for notifications on table2 can
> ask table2 for the jobno that was updated. then if
> they were viewing that jobno, update their display. if
> not just ignore the notify.

Pardon me if I make your focus blur!
I believe the implementation for such requirement in an application
server in 3 tier environment is not too difficult.

Regards,
CN

-- 
http://www.fastmail.fm - Or how I learned to stop worrying and
  love email again

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Creating Index

[CN]

Stephan and Tom,
Many thanks!

I am trying to explain the meaning of tables and views:
The tables in the first SELECT in the UNION of view1 are jorunal, whose
rows are entered daily.
The table in the second SELECT in the UNION of view1 is budget, whose
rows use year+month as key.
View1 unions journal and budget to present users a pseudo table that can
be selected to produce comparison reports - budget vs. actual.
User will be prompted to enter the interested year+month before selecting
the view.

I think I have found out a way to make the index from year and month.
Although this approach produces reduntant data but it should be feasible:
- Create 2 more columns, year and month for table2.
- Feed NEW.c3 and NEW.f4 with values EXTRACT(YEAR FROM c2) and
EXTRACT(MONTH FROM c2), respectively in 
plpgsql trigger function.
- Create composit index for year+month.

There is still one thing I don't know why - query on view1 being
extermely slow. I also removed the subquery 
from view1 to form view2. The query on view2 is swift. (postgresql
v7.3.2)
Both queries return 0 rows. The results are correct because table2
contains data of years on or before 2003 
and table4 contains no rows. 

Again, I would much appreciate any idea helping me speed up view1.

One off-topic issue is that I wish postgresql could be smarter to make
use of the index even there are INT2 columns in composit index. As my
case shows, INT2 is big enough for columns year and month, isn't it?

Best Regards,
CN
-
--This table contains 1036 rows.
CREATE TABLE table1 (
c1  VARCHAR(20) PRIMARY KEY,
c2  "char"
)WITHOUT OIDS;
-
--This table contains 9255 rows.
CREATE TABLE table2 (
c1  VARCHAR(20) PRIMARY KEY,
c2  DATE,
c3  INTEGER, --year part of c2
c4  INTEGER  --month part of c2
)WITHOUT OIDS;
CREATE INDEX i2c3c4 ON table2 (c3,c4);
-
--This table contains 28482 rows.
CREATE TABLE table3 (
CONSTRAINT fk2c1 FOREIGN KEY (c1) REFERENCES table2 (c1) ON UPDATE
CASCADE ON DELETE CASCADE,
CONSTRAINT fk2c3 FOREIGN KEY (c3) REFERENCES table1 (c1),
PRIMARY KEY (c1,c2),
c1  VARCHAR(20),
c2  INTEGER,
c3  VARCHAR(20),
c4  "char",
c5  NUMERIC --amount
)WITHOUT OIDS;
-
--This table contains 0 row.
CREATE TABLE table4 (
PRIMARY KEY (c1,c2),
c1  INTEGER,   --year
c2  INTEGER,   --month
c3  INTEGER
)WITHOUT OIDS;
-
CREATE OR REPLACE VIEW view1 AS
SELECT
  table2.c3 AS year
  ,table2.c4 AS month
  ,(SELECT CASE
  WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
  THEN table3.c5 ELSE 0-table3.c5 END
  AS amount
FROM table1
WHERE table1.c1=table3.c3
   )
FROM table2,table3
WHERE table3.c1=table2.c1

UNION ALL

SELECT c1,c2,c3 FROM table4;
-
CREATE OR REPLACE VIEW view2 AS
SELECT table2.c3 AS year,table2.c4 AS month
FROM table2,table3
WHERE table3.c1=table2.c1

UNION ALL

SELECT c1,c2 FROM table4;
-
-
EXPLAIN ANALYZE SELECT * FROM view1 WHERE year > 2003;
-
 Subquery Scan view1  (cost=0.00..141.50 rows=2000 width=185) (actual
 time=4348.92..4348.92 rows=0 loops=1)
   Filter: ("year" > 2003)
   ->  Append  (cost=0.00..141.50 rows=2000 width=185) (actual
   time=2.65..4230.44 rows=28482 loops=1)
 ->  Subquery Scan "*SELECT* 1"  (cost=0.00..121.50 rows=1000
 width=185) (actual time=2.64..4127.71 
rows=28482 loops=1)
   ->  Merge Join  (cost=0.00..121.50 rows=1000 width=185)
   (actual time=2.62..3875.23 rows=28482 
loops=1)
 Merge Cond: ("outer".c1 = "inner".c1)
 ->  Index Scan using table2_pkey on table2 
 (cost=0.00..52.00 rows=1000 width=56) 
(actual time=0.81..183.37 rows=9255 loops=1)
 ->  Index Scan using table3_pkey on table3 
 (cost=0.00..52.00 rows=1000 width=129) 
(actual time=0.74..649.32 rows=28482 loops=1)
 SubPlan
   ->  Index Scan using table1_pkey on table1 
   (cost=0.00..4.82 rows=1 width=1) (actual 
time=0.07..0.07 rows=1 loops=28482)
 Index Cond: (c1 = $2)
 ->  Subquery Scan "*SELECT* 2"  (cost=0.00..20.00 rows=1000
 width=12) (actual time=0.02..0.02 rows=0 
loops=1)
   ->  Seq Scan on table4  (cost=0.00..20.00 rows=1000
   width=12) (actual time=0.01..0.01 rows=0 
loops=1)
 Total runtime: 4350.24 msec
-
-
EXPLAIN ANALYZE SELECT * FROM view2 WHERE year > 2003;
-
 Subquery Scan view2  (cost=36.47..119.30 rows=667 width=104) (actual
 time=40.90..40.90 rows=0 loops=1)
   ->  Append  (cost=

Re: [SQL] Creating Index

[CN]

dth=129)
> > (actual time=0.74..649.32 rows=28482 loops=1)
> >  SubPlan
> >->  Index Scan using table1_pkey on table1
> >(cost=0.00..4.82 rows=1 width=1) (actual
> > time=0.07..0.07 rows=1 loops=28482)
> >  Index Cond: (c1 = $2)
> >  ->  Subquery Scan "*SELECT* 2"  (cost=0.00..20.00 rows=1000
> >  width=12) (actual time=0.02..0.02 rows=0
> > loops=1)
> >->  Seq Scan on table4  (cost=0.00..20.00 rows=1000
> >width=12) (actual time=0.01..0.01 rows=0
> > loops=1)
> >  Total runtime: 4350.24 msec
> > -
> > -
> > EXPLAIN ANALYZE SELECT * FROM view2 WHERE year > 2003;
> > -
> >  Subquery Scan view2  (cost=36.47..119.30 rows=667 width=104) (actual
> >  time=40.90..40.90 rows=0 loops=1)
> >->  Append  (cost=36.47..119.30 rows=667 width=104) (actual
> >time=40.88..40.88 rows=0 loops=1)
> >  ->  Subquery Scan "*SELECT* 1"  (cost=36.47..96.80 rows=333
> >  width=104) (actual time=40.85..40.85
> > rows=0 loops=1)
> >->  Merge Join  (cost=36.47..96.80 rows=333 width=104)
> >(actual time=40.84..40.84 rows=0
> > loops=1)
> >  Merge Cond: ("outer".c1 = "inner".c1)
> >  ->  Index Scan using table3_pkey on table3
> >  (cost=0.00..52.00 rows=1000 width=48)
> > (actual time=0.52..0.52 rows=1 loops=1)
> >  ->  Sort  (cost=36.47..37.30 rows=333 width=56)
> >  (actual time=40.30..40.30 rows=0 loops=1)
> >Sort Key: table2.c1
> >->  Seq Scan on table2  (cost=0.00..22.50
> >rows=333 width=56) (actual
> > time=38.65..38.65 rows=0 loops=1)
> >  Filter: (c3 > 2003)
> >  ->  Subquery Scan "*SELECT* 2"  (cost=0.00..22.50 rows=333
> >  width=8) (actual time=0.02..0.02 rows=0
> > loops=1)
> >->  Seq Scan on table4  (cost=0.00..22.50 rows=333
> >width=8) (actual time=0.01..0.01 rows=0
> > loops=1)
> >  Filter: (c1 > 2003)
> >  Total runtime: 41.86 msec

Best Regards,
CN

-- 
http://www.fastmail.fm - mmm... Fastmail...

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Creating Index

[CN]

> The reason why view1 isn't well optimized is that you've been sloppy
> about datatypes.  It looks to me like the "SELECT CASE" business yields
> NUMERIC while the other arm of the UNION yields INTEGER for table4.c3.
> For various subtle semantic reasons we do not try to push down
> conditions into UNIONs when the UNION arms yield different datatypes.

Absolutely right! After switching table3.c5 to INTEGER, query to view1 is
lightening fast:

--
 Subquery Scan view1  (cost=0.00..23.18 rows=4 width=48) (actual
 time=0.13..0.13 rows=0 loops=1)
   ->  Append  (cost=0.00..23.18 rows=4 width=48) (actual time=0.12..0.12
   rows=0 loops=1)
 ->  Subquery Scan "*SELECT* 1"  (cost=0.00..23.18 rows=3
 width=48) (actual time=0.11..0.11 rows=0 
loops=1)
   ->  Nested Loop  (cost=0.00..23.18 rows=3 width=48)
   (actual time=0.10..0.10 rows=0 loops=1)
 ->  Index Scan using i2c3c4 on table2 
 (cost=0.00..5.04 rows=1 width=21) (actual 
time=0.09..0.09 rows=0 loops=1)
   Index Cond: (c3 > 2003)
 ->  Index Scan using table3_pkey on table3 
 (cost=0.00..18.09 rows=4 width=27) (never 
executed)
   Index Cond: (table3.c1 = "outer".c1)
 SubPlan
   ->  Index Scan using table1_pkey on table1 
   (cost=0.00..3.01 rows=1 width=1) (never 
executed)
 Index Cond: (c1 = $2)
 ->  Subquery Scan "*SELECT* 2"  (cost=0.00..0.00 rows=1
 width=12) (actual time=0.01..0.01 rows=0 
loops=1)
   ->  Seq Scan on table4  (cost=0.00..0.00 rows=1 width=12)
   (actual time=0.01..0.01 rows=0 
loops=1)
 Filter: (c1 > 2003)
 Total runtime: 0.69 msec

> 7.4 would optimize this query successfully anyway because the condition
> isn't actually on the column with inconsistent datatype --- but 7.3 just
> punts if *any* of the UNION columns have inconsistent datatypes.

Apparently this postgreSQL beast has always been well under control by
the fingers of you genious developers!

Long live the king!
Ooops! I'm sorry! Please pardon my English!

Long live postgreSQL, the no. 1 DBMS, and its masters - the developers!

Best Regards,

CN

-- 
http://www.fastmail.fm - Email service worth paying for. Try it for free

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Creating Index

[CN]

Hi!

I thought I have got no more question in this thread, and I was wrong :-(
Is the extra 300ms in UNION essential?

Best Regards,
CN

--This table contains 1036 rows.
CREATE TABLE table1 (
c1  VARCHAR(20) PRIMARY KEY,
c2  "char"
)WITHOUT OIDS;
-
--This table contains 9429 rows.
CREATE TABLE table2 (
c1  VARCHAR(20) PRIMARY KEY,
c2  DATE
)WITHOUT OIDS;
-
--This table contains 28482 rows.
CREATE TABLE table3 (
CONSTRAINT fk2c1 FOREIGN KEY (c1) REFERENCES table2 (c1) ON UPDATE
CASCADE ON DELETE CASCADE,
CONSTRAINT fk2c3 FOREIGN KEY (c3) REFERENCES table1 (c1),
PRIMARY KEY (c1,c2),
c1  VARCHAR(20),
c2  INTEGER,
c3  VARCHAR(20),
c4  "char",
c5  INTEGER
)WITHOUT OIDS;
-
--This table contains 0 row.
CREATE TABLE table4 (
PRIMARY KEY (c1,c2),
c1  INTEGER,   --year
c2  INTEGER,   --month
c3  INTEGER
)WITHOUT OIDS;
-
EXPLAIN ANALYZE
SELECT
  table2.c2 AS date
  ,CASE
  WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
  THEN table3.c5 ELSE 0-table3.c5 END
   AS amount
FROM table2,table3,table1
WHERE table3.c1=table2.c1
  AND table1.c1=table3.c3

UNION ALL

SELECT TO_DATE(c1 || ' ' || c2 || ' 1',' MM DD'),c3 FROM table4;
---
 Append  (cost=199.64..2446.56 rows=28483 width=54) (actual
 time=154.06..2200.53 rows=28482 loops=1)
   ->  Subquery Scan "*SELECT* 1"  (cost=199.64..2446.56 rows=28482
   width=54) (actual time=154.05..2116.96 
rows=28482 loops=1)
 ->  Hash Join  (cost=199.64..2446.56 rows=28482 width=54)
 (actual time=154.03..1941.40 rows=28482 
loops=1)
   Hash Cond: ("outer".c1 = "inner".c1)
   ->  Hash Join  (cost=18.95..1411.41 rows=28482 width=37)
   (actual time=22.85..934.94 rows=28482 
loops=1)
 Hash Cond: ("outer".c3 = "inner".c1)
 ->  Seq Scan on table3  (cost=0.00..822.82
 rows=28482 width=27) (actual 
time=8.26..403.61 rows=28482 loops=1)
 ->  Hash  (cost=16.36..16.36 rows=1036 width=10)
 (actual time=14.18..14.18 rows=0 
loops=1)
   ->  Seq Scan on table1  (cost=0.00..16.36
   rows=1036 width=10) (actual 
time=0.23..8.26 rows=1036 loops=1)
   ->  Hash  (cost=157.55..157.55 rows=9255 width=17) (actual
   time=130.63..130.63 rows=0 loops=1)
 ->  Seq Scan on table2  (cost=0.00..157.55 rows=9255
 width=17) (actual time=0.24..78.22 
rows=9255 loops=1)
   ->  Subquery Scan "*SELECT* 2"  (cost=0.00..0.00 rows=1 width=12)
   (actual time=0.02..0.02 rows=0 loops=1)
 ->  Seq Scan on table4  (cost=0.00..0.00 rows=1 width=12)
 (actual time=0.01..0.01 rows=0 loops=1)
 Total runtime: 2259.10 msec
-
EXPLAIN ANALYZE
SELECT
  table2.c2 AS date
  ,CASE
  WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
  THEN table3.c5 ELSE 0-table3.c5 END
   AS amount
FROM table2,table3,table1
WHERE table3.c1=table2.c1
  AND table1.c1=table3.c3;
---
 Hash Join  (cost=199.64..2446.56 rows=28482 width=54) (actual
 time=155.37..1857.25 rows=28482 loops=1)
   Hash Cond: ("outer".c1 = "inner".c1)
   ->  Hash Join  (cost=18.95..1411.41 rows=28482 width=37) (actual
   time=23.08..891.03 rows=28482 loops=1)
 Hash Cond: ("outer".c3 = "inner".c1)
 ->  Seq Scan on table3  (cost=0.00..822.82 rows=28482 width=27)
 (actual time=8.12..386.64 rows=28482 
loops=1)
 ->  Hash  (cost=16.36..16.36 rows=1036 width=10) (actual
 time=14.58..14.58 rows=0 loops=1)
   ->  Seq Scan on table1  (cost=0.00..16.36 rows=1036
   width=10) (actual time=0.26..9.19 
rows=1036 loops=1)
   ->  Hash  (cost=157.55..157.55 rows=9255 width=17) (actual
   time=131.84..131.84 rows=0 loops=1)
 ->  Seq Scan on table2  (cost=0.00..157.55 rows=9255 width=17)
 (actual time=0.24..77.11 rows=9255 
loops=1)
 Total runtime: 1915.33 msec

-- 
http://www.fastmail.fm - A no graphics, no pop-ups email service

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Need a SQL to create sets of hobbies

[CN]

Hi!

CREATE TABLE x1 (name TEXT, hobby TEXT, PRIMARY KEY(name,hobby));

INSERT INTO x1 VALUES('John','music');
INSERT INTO x1 VALUES('John','arts');
INSERT INTO x1 VALUES('Bob','arts');
INSERT INTO x1 VALUES('Bob','music');
INSERT INTO x1 VALUES('Rocky','copmputer');
INSERT INTO x1 VALUES('Steve','arts');
INSERT INTO x1 VALUES('Steve','football');
INSERT INTO x1 VALUES('Tom','computer');
INSERT INTO x1 VALUES('Tom','music');

select * from x1;

 name  |  hobby   
---+--
 John  | music
 John  | arts
 Bob   | arts
 Bob   | music
 Rocky | computer
 Steve | arts
 Steve | football
 Tom   | computer
 Tom   | music
(9 rows)

John and Bob have the same hobbies - music and arts. So music and arts
are treated as one set of hobbies.

Rocky has an unique set of interest - computer.

Steve also likes arts just as John and Bob do, but he also has an
exclusive interest - football. Thus, his set of hobbies is unique -
arts, football.

One of Tom's hobbies, music, overlaps those of John, Bob, and Rocky; but
computer does not. Hence his hobbies, computer and music, forms a new
set of hobbies.

Now we have 4 sets of hobbies:

set 1: music, arts
set 2: computer
set 3: arts, football
set 4: computer, music

I am looking for an SQL that creates sets of hobbies in table x2 by
selecting from table x1:

CREATE TABLE x2 (sid INTEGER, hobby TEXT, PRIMARY KEY(sid,hobby));

and makes x2 contain rows:

 sid   |  hobby   
---+--
1  | music
1  | arts
2  | computer
3  | arts
3  | football
4  | computer
4  | music

where gid starts from 1.

Thank you in advance!
CN

-- 
http://www.fastmail.fm - A no graphics, no pop-ups email service


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] How To Get Bytea Data Instead Of Its Oid

[CN LIOU]

Greetings!

I must have missed lesson 1 of postgresql!

create table test (c1 text,c2 bytea);

Then, I probably have successfullyi nserted several records into test using 
C++Builder. Now I am trying to retrieve back the binary data in c2 I just inserted. 
However, the SQL statement issued by both C++Builder and psql:

SELECT * FROM test LIMIT 1

returns, I guess, the OID of c2 instead of the binary data to which the OID points. 

What is the correct SQL syntax for C++Builder for bytea?

Regards,
-- 
___
Get your free email from http://www.graffiti.net

Powered by Outblaze

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] Two Permance Questions

[CN LIOU]

Hi!

Q1. Is subquery better or join?

For subquery:

SELECT t1.c1,(SELECT t2.c2 FROM t2 WHERE t2.c1 = t1.c1) FROM t1

I wonder it will loop n times if t1 returns n rows. If this is the case, is it better 
to use join like this:

SELECT t1.c1,t2.c2 FROM t1,t2 WHERE t2.c1 = t1.c1

Q2. If the query is not optimize like this:

SELECT t1.c1,t2.c2 FROM t1,t2,t1,t2,t2 WHERE t2.c1=t1.c1 AND t1.c1=t2.c1 AND 
t1.c1=t2.c1

and the size of this clause can soar up to several kbytes, then can this query cause 
performance problem?

Regards,
-- 
___
Get your free email from http://www.graffiti.net

Powered by Outblaze

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster