[SQL] Unsubscribe
please remove my email from your database contacts. best regards, etur
[SQL] Unsubscribe
please remove my email from your database contacts. Best Regards, Naren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Unsubscribe
please remove my email from your database contacts. Kind Regards, Mark. ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication medium. We strongly advise that you understand & observe this lack of security when e-mailing us. This email and any files transmitted with it are intended solely for the use of the individual to whom they are addressed. If you have received this email in error please notify your email administrator. Any views or opinions are solely those of the author of this email and do not represent those of Great Ormond Street Hospital for Children NHS Trust unless specifically stated. VIRUSES: This email message has been checked for the presence of computer viruses by Sophos antivirus software. However, this does not guarantee that this email is free of viruses, and the recipient should perform their own check. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Determining Inheritance
Is there any way to determine which table a row belows to, if I do a search on the top level table: e.g. I have a table called "BASE". There are two tables which inherit from "BASE" called "SUBTABLE1" and "SUBTABLE2" If I do a SELECT * FROM BASE WHERE blah; how can I tell which table (either SUBTABLE1 or SUBTABLE2) a row comes from ? Do I need to add an extra column to BASE to specify which table a row really belongs to ? (If it helps or hinders, I am ultimately going to be doing this via JDBC) Thanks, GTG Gordon Ross, Network Manager/Rheolwr Rhydwaith Countryside Council for Wales/Cyngor Cefn Gwlad Cymru ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Creating Index
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 writes: > 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; Unqualified count() cannot use an index because it has to visit all the rows in the table. 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. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Determining Inheritance
"Gordon Ross" <[EMAIL PROTECTED]> writes: > Is there any way to determine which table a row belows to, if I do a > search on the top level table: Look at the built-in column "tableoid". You can join this to pg_class.oid to retrieve the table name. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Link Oracle tables in Postgre
Howdy, I’d like to do (live) link of Oracle table (on win) in Postgres (on RH9 box). In Oracle are stored attribute of my spatial layer collect in postgres-postgis geo-database ono-to-one relationship. I use Mapserver (http://mapserver.gis.umn.edu) in order to render web map stored in postgres. I need to perform filer after do JOIN between oracle-postgres data. Any suggest? Do you know where can I find documentation? best-practice? Thank -- opengis (at) libero (dot) it ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Creating Index
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] Link Oracle tables in Postgre
On Wednesday 01 October 2003 16:24, OpenGis wrote: > Howdy, > > I’d like to do (live) link of Oracle table (on win) in Postgres (on RH9 > box). > In Oracle are stored attribute of my spatial layer collect in > postgres-postgis geo-database ono-to-one relationship. I use Mapserver > (http://mapserver.gis.umn.edu) in order to render web map stored in > postgres. I need to perform filer after do JOIN between oracle-postgres > data. You might want to search the mailing list archives for discussion of an oracle version of dblink - I seem to remember someone saying they were working on such a thing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Creating Index
"CN" <[EMAIL PROTECTED]> writes: > The following view is, again, a simplified version. The real version, > which takes 13 seconds, joins 2 more tables. You're really doing your best to make sure we don't figure out what's going on :-( One thing I can see from your EXPLAIN ANALYZE results, though, is that you've never VACUUMed or ANALYZEd these tables. If you had, there'd be something other than the default 1000-row table size estimates: >-> Index Scan using table1_pkey on table1 >(cost=0.00..52.00 rows=1000 width=100) > (actual time=0.69..220.87 rows=9428 loops=1) >-> Index Scan using table2_pkey on table2 >(cost=0.00..52.00 rows=1000 width=224) > (actual time=0.63..959.95 rows=28482 loops=1) and possibly the planner would have picked a more appropriate plan. regards, tom lane ---(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] Creating Index
>-> Seq Scan on table1 (cost=0.00..20.00 rows=1000 width=0) (actual Run VACUUM ANALYZE, then repost your EXPLAIN ANALYZE results please. signature.asc Description: This is a digitally signed message part
Re: [SQL] Link Oracle tables in Postgre
Richard, > You might want to search the mailing list archives for discussion of an > oracle version of dblink - I seem to remember someone saying they were > working on such a thing. Yes, it'll be out sometime after 7.4. According to their posts in August, it's in alpha right now and just barely didn't make it into the 7.4 source. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Creating Index
> >-> 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
On Wed, 1 Oct 2003, CN wrote: > 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. 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 . ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL]
Title: Untitled Stationery please remove my email from your database contacts. Best Regards, Vishal Charan IT Support Courts/Homecentres
[SQL] help with rule and notification
I don't know if this is the correct forum for this question but I will start here... I have a job tracking system that I am developing with postgresql and mac os x. I have all the pieces in place (mostly) but i am having a problem with notify.. I am trying to set up things so that two (or more) people can view the same job data, and if one client updates the data the others will be notified and can update their displays. I got the notify to work (it wasn't too difficult) but now I am trying to figure out the logic. I mean the only examples I see have rules that say.. update table1, then the rule updates table2 and sends a notify to anyone listening. The information passed in the notify is a reference to the second table. Ok so far. I am having a problem with the second table update. When I update table1 (update table1 set info ='info' where jobno = '10023') how do I pick up the jobno variable in my rule? something like create rule r1 as on update to table1 do (update table2 set jobno = table1.jobno; notify table2;) 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. maybe i'm going about this all wrong and someone can point me in a better direction. I am open to any solution... Thanks.. Ted __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Creating Index
> 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
> 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] help with rule and notification
Theodore Petrosky <[EMAIL PROTECTED]> writes: > create rule r1 as on update to table1 do (update > table2 set jobno = table1.jobno; notify table2;) > 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. At the moment, a NOTIFY cannot convey very much information beyond "something happened, better look to see what". (There have been discussions about making the notification carry more info, see the pgsql-hackers archives.) In a previous lifetime I had a moderately complex application that used NOTIFY to trigger display updates for multiple client apps viewing a shared database. If memory serves, I did it by having a "sequence number" column that was assigned from a nextval() operation on every insert or update. In addition the inserts and updates triggered NOTIFY events. When the clients got NOTIFY they'd do "select from tab where seqno > last-seqno-seen" and then update their local state from the rows they got back. This solution doesn't directly handle deletes. I think I finessed the problem by treating "delete" as "update to a 'dead' state" and only cleaning out the dead rows later. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Creating Index
"CN" <[EMAIL PROTECTED]> writes: > Is creating a function that eats DATE as argument to build that index my > only solution? You haven't really explained what your problem is. In general I'd not guess that "year >= X and month >= Y" is a useful operation if Y is different from 1. You'd be selecting a very discontinuous set of dates ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Creating Index
On Wed, 1 Oct 2003, CN wrote:
> > 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';
These two queries seem fairly equivalent, but
WHERE EXTRACT(YEAR FROM d) >=2001 AND EXTRACT(MONTH FROM d)>=2
is not equivalent to
WHERE d>='2001-2-1'
Are you trying to get certain months in a group of years or all months
after a given fixed time point? If the former, only the former form in
general will work, if the latter the former form really doesn't work at
all with the exception of the case where you're doing month>=1 (which
might as well mean you don't do a month test at all).
> 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?
They both are. I'd have to guess that most of the real cost is coming from
evaluating the conditions, which seems wierd.
> 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 FROM d)::TEXT>
>
> Is creating a function that eats DATE as argument to build that index my
> only solution?
For 7.3 and earlier, yes I think so and you'd have to use that form in
the query. However that wouldn't help for the union query.
In 7.4, you can make an index on table((extract(year from d)),
(extract(month from d))) and I believe once the type issues were resolved
that would get used. However, I think the evaluations of the extracts
would mean that it'd still probably lose to a comparison on date (unless
you want the fraction of a set of years solution).
---(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
