Re: Temporary tables

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

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: 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   peter_e <at> gmx.net

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

Re: Determining Inheritance

"Gordon Ross" <G.Ross <at> ccw.gov.uk> 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

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: 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 "*SELECT* 1"  (cost=0.00..109.38 rows=25
               width=324) (actual 
time=6.12..4571.25 rows=28482 loops=1)
                     ->  Merge Join  (cost=0.00..109.38 rows=25
                     width=324) (actual time=6.09..4192.39 
rows=28482 loops=1)
                           Merge Cond: (("outer".f0 = "inner".f0) AND
                           ("outer".f1 = "inner".f1))
                           ->  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)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..20.00
               rows=1000 width=156) (actual time=0.02..0.02 
rows=0 loops=1)
                     ->  Seq Scan on table3  (cost=0.00..20.00 rows=1000
                     width=156) (actual time=0.01..0.01 
rows=0 loops=1)
 Total runtime: 5025.73 msec
(11 rows)

db1=# EXPLAIN ANALYZE SELECT COUNT(*) FROM table1;
                                                 QUERY PLAN               
------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=22.50..22.50 rows=1 width=0) (actual
 time=116.90..116.91 rows=1 loops=1)
   ->  Seq Scan on table1  (cost=0.00..20.00 rows=1000 width=0) (actual
   time=0.22..76.37 rows=9429 loops=1)
 Total runtime: 117.10 msec
(3 rows)

--

-- 
http://www.fastmail.fm - A fast, anti-spam email service.

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

Re: 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: Creating Index

"CN" <cnliou9 <at> fastmail.fm> 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 majordomo <at> postgresql.org so that your
      message can get through to the mailing list cleanly

Re: 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.