pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTo use a VIEW or not to use a View.....
23+ messages / 7 participants
[nested] [flat]
* To use a VIEW or not to use a View.....
@ 2003-01-22 15:12 Ries van Twisk <ries@jongert.nl>
0 siblings, 4 replies; 23+ messages in thread
From: Ries van Twisk @ 2003-01-22 15:12 UTC (permalink / raw)
To: pgsql-sql
Dear PostgreSQL users,
I have a view and a table,
I understand that when a frontend accesses a VIEW that PostgreSQL cannot use
a index on that view.
For example when I do this: SELECT * FROM full_cablelist WHERE
projectocode=5; Correct?
Now I just want to make sure for myself if the VIEW I created is the right
way to go, or is it better
to contruct a SQL in my application that looks like the view and send it to
postgreSQL so it will
use all indexes correctly. I use postgreSQL 7.2.1
I beliefe there is a change in postgreSQL 7.3.x on which I can cache a
view??? Not sure what the issue was.
I ask this because I expect pore performance in feature when the cablelist
table holds up to around 20.000 rows.
Each query to full_cablelist will return around 1200 rows.
best regards,
Ries van Twisk
-- CABLE LIST
CREATE TABLE cablelist (
id SERIAL,
cableno VARCHAR(8),
projectcodeid INTEGER CONSTRAINT cablelist_projectcodes_con NOT NULL
REFERENCES projectcodes(id) ON DELETE CASCADE,
fromconnid INTEGER CONSTRAINT cablelist_fromconnid_con NOT NULL
REFERENCES libconnections(id) ON DELETE CASCADE,
toconnid INTEGER CONSTRAINT cablelist_toconnid_con NOT NULL REFERENCES
libconnections(id) ON DELETE CASCADE,
fromshiplocationid INTEGER CONSTRAINT cablelist_fromshiplocationid_con
NOT NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
toshiplocationid INTEGER CONSTRAINT cablelist_toshiplocationid_con NOT
NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
marktypesid INTEGER CONSTRAINT cablelist_tomarktypeid_con NOT NULL
REFERENCES marktypes(id) ON DELETE CASCADE,
cabletypeid INTEGER CONSTRAINT cablelist_cabletypeid_con NOT NULL
REFERENCES cabletypes(id) ON DELETE CASCADE,
cut BOOLEAN DEFAULT 'false' NOT NULL,
placed BOOLEAN DEFAULT 'false' NOT NULL,
ok BOOLEAN DEFAULT 'false'
);
-- CABLE LIST VIEW
CREATE VIEW full_cablelist AS
SELECT cl.id,
cl.cableno AS cableno,
pc.projectcode AS projectcode,
pc.id AS projectcodeid,
lcf.name AS fconnection, lct.name AS tconnection,
lif.name AS fitem, lit.name AS titem,
slf.rib AS frib,slt.rib AS trib,
slf.name AS fname, slt.name AS tname,
ct.cabletype AS cabletype, ct.coretype AS coretype,
cl.cut,
cl.placed,
cl.ok
FROM cablelist AS cl,
libconnections AS lcf, libconnections AS lct,
libitems AS lif, libitems AS lit,
shiplocations AS slf, shiplocations AS slt,
projectcodes AS pc,
cabletypes AS ct
WHERE
pc.id=cl.projectcodeid AND
lcf.id=cl.fromconnid AND lct.id=cl.toconnid AND
lif.id=lcf.libitemid AND lit.id=lct.libitemid AND
slf.id=cl.fromshiplocationid AND slt.id=cl.toshiplocationid AND
ct.id=cl.cabletypeid
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 15:46 Tomasz Myrta <jasiek@klaster.net>
parent: Ries van Twisk <ries@jongert.nl>
3 siblings, 1 reply; 23+ messages in thread
From: Tomasz Myrta @ 2003-01-22 15:46 UTC (permalink / raw)
To: Ries van Twisk <ries@jongert.nl>; +Cc: pgsql-sql
Ries van Twisk wrote:
>Dear PostgreSQL users,
>
>I have a view and a table,
>
>I understand that when a frontend accesses a VIEW that PostgreSQL cannot use
>a index on that view.
>For example when I do this: SELECT * FROM full_cablelist WHERE
>projectocode=5; Correct?
>
>Now I just want to make sure for myself if the VIEW I created is the right
>way to go, or is it better
>to contruct a SQL in my application that looks like the view and send it to
>postgreSQL so it will
>use all indexes correctly. I use postgreSQL 7.2.1
>
>I beliefe there is a change in postgreSQL 7.3.x on which I can cache a
>view??? Not sure what the issue was.
>
>I ask this because I expect pore performance in feature when the cablelist
>table holds up to around 20.000 rows.
>Each query to full_cablelist will return around 1200 rows.
>
>best regards,
>Ries van Twisk
>
>
>
>-- CABLE LIST
>CREATE TABLE cablelist (
> id SERIAL,
> cableno VARCHAR(8),
> projectcodeid INTEGER CONSTRAINT cablelist_projectcodes_con NOT NULL
>REFERENCES projectcodes(id) ON DELETE CASCADE,
> fromconnid INTEGER CONSTRAINT cablelist_fromconnid_con NOT NULL
>REFERENCES libconnections(id) ON DELETE CASCADE,
> toconnid INTEGER CONSTRAINT cablelist_toconnid_con NOT NULL REFERENCES
>libconnections(id) ON DELETE CASCADE,
> fromshiplocationid INTEGER CONSTRAINT cablelist_fromshiplocationid_con
>NOT NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
> toshiplocationid INTEGER CONSTRAINT cablelist_toshiplocationid_con NOT
>NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
> marktypesid INTEGER CONSTRAINT cablelist_tomarktypeid_con NOT NULL
>REFERENCES marktypes(id) ON DELETE CASCADE,
> cabletypeid INTEGER CONSTRAINT cablelist_cabletypeid_con NOT NULL
>REFERENCES cabletypes(id) ON DELETE CASCADE,
> cut BOOLEAN DEFAULT 'false' NOT NULL,
> placed BOOLEAN DEFAULT 'false' NOT NULL,
> ok BOOLEAN DEFAULT 'false'
>);
>
>
>-- CABLE LIST VIEW
>CREATE VIEW full_cablelist AS
>SELECT cl.id,
>cl.cableno AS cableno,
>pc.projectcode AS projectcode,
>pc.id AS projectcodeid,
>lcf.name AS fconnection, lct.name AS tconnection,
>lif.name AS fitem, lit.name AS titem,
>slf.rib AS frib,slt.rib AS trib,
>slf.name AS fname, slt.name AS tname,
>ct.cabletype AS cabletype, ct.coretype AS coretype,
>cl.cut,
>cl.placed,
>cl.ok
>
>FROM cablelist AS cl,
>libconnections AS lcf, libconnections AS lct,
>libitems AS lif, libitems AS lit,
>shiplocations AS slf, shiplocations AS slt,
>projectcodes AS pc,
>cabletypes AS ct
>
>WHERE
>pc.id=cl.projectcodeid AND
>lcf.id=cl.fromconnid AND lct.id=cl.toconnid AND
>lif.id=lcf.libitemid AND lit.id=lct.libitemid AND
>slf.id=cl.fromshiplocationid AND slt.id=cl.toshiplocationid AND
>ct.id=cl.cabletypeid
How can we help you with table indexing, if you didn't write anything
about indexes you have already created on your tables? Anyway you don't need
indexes on a view, but indexes on your tables. You need also a well constructed
view.
For your query:
- make sure, you have index on projectcodes(projectcode) - if you have many projectcodes
and index on cablelist(projectcodeid)
- make sure, you did "vacuum analyze" before you test your query.
- send result of "explain analyze SELECT * FROM full_cablelist WHERE
projectocode=5" to us.
Anyway result can't be too fast, because query returns >1000 rows which is rather
a big amount of data.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 15:46 Jan Wieck <JanWieck@Yahoo.com>
parent: Ries van Twisk <ries@jongert.nl>
3 siblings, 1 reply; 23+ messages in thread
From: Jan Wieck @ 2003-01-22 15:46 UTC (permalink / raw)
To: Ries van Twisk <ries@jongert.nl>; +Cc: pgsql-sql
Ries van Twisk wrote:
>
> Dear PostgreSQL users,
>
> I have a view and a table,
>
> I understand that when a frontend accesses a VIEW that PostgreSQL cannot use
> a index on that view.
> For example when I do this: SELECT * FROM full_cablelist WHERE
> projectocode=5; Correct?
No.
>
> Now I just want to make sure for myself if the VIEW I created is the right
> way to go, or is it better
> to contruct a SQL in my application that looks like the view and send it to
> postgreSQL so it will
> use all indexes correctly. I use postgreSQL 7.2.1
Views in PostgreSQL aren't materialized. They are implemented as query
rewrite rules that combine your query with the view definition. This is
done before planning and optimizing, so what the query planner is
chewing on (the internal parsetree representation of a query) is the
same as if your application would have sent down the complicated query
over the base tables.
There are a few exceptions where an application could construct a better
WHERE clause, resulting in a different join order or better scan
qualifications. As long as we're not talking about gigabytes here, you
shouldn't worry.
Use tables, views and views over views, it's all fine and your indexes
will be used.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 15:48 Bruno Wolff III <bruno@wolff.to>
parent: Ries van Twisk <ries@jongert.nl>
3 siblings, 0 replies; 23+ messages in thread
From: Bruno Wolff III @ 2003-01-22 15:48 UTC (permalink / raw)
To: Ries van Twisk <ries@jongert.nl>; +Cc: pgsql-sql
On Wed, Jan 22, 2003 at 16:12:52 +0100,
Ries van Twisk <ries@jongert.nl> wrote:
> Dear PostgreSQL users,
>
> I understand that when a frontend accesses a VIEW that PostgreSQL cannot use
> a index on that view.
> For example when I do this: SELECT * FROM full_cablelist WHERE
> projectocode=5; Correct?
For the most part views work like macros for selects and indexes should be
usable.
You can test this yourself using EXPLAIN to compare plans both using and
not using a view on a table.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 16:10 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Ries van Twisk <ries@jongert.nl>
3 siblings, 0 replies; 23+ messages in thread
From: Stephan Szabo @ 2003-01-22 16:10 UTC (permalink / raw)
To: Ries van Twisk <ries@jongert.nl>; +Cc: pgsql-sql
On Wed, 22 Jan 2003, Ries van Twisk wrote:
> Dear PostgreSQL users,
>
> I have a view and a table,
>
> I understand that when a frontend accesses a VIEW that PostgreSQL cannot use
> a index on that view.
> For example when I do this: SELECT * FROM full_cablelist WHERE
> projectocode=5; Correct?
In general, no. There are some exceptions, for example views using
EXCEPT I believe will not push conditions down. In 7.2.x, views using
any of the set ops (INTERSECT, UNION, EXCEPT) wouldn't push conditions
down. There are a few other such conditions, but for your view, I think
this isn't going to be an issue.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 16:20 Tomasz Myrta <jasiek@klaster.net>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 2 replies; 23+ messages in thread
From: Tomasz Myrta @ 2003-01-22 16:20 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Ries van Twisk <ries@jongert.nl>; pgsql-sql
Jan Wieck wrote:
>Use tables, views and views over views, it's all fine and your indexes
>will be used.
I can't agree with using views over views. It has some limitations.
I asked about it on this list several months ago
and Tom Lane's conclusion was:
>> Tomasz Myrta <jasiek@klaster.net> writes:
>> I'd like to split queries into views, but I can't join them - planner
>> search all of records instead of using index. It works very slow.
I think this is the same issue that Stephan identified in his response
to your other posting ("sub-select with aggregate"). When you write
FROM x join y using (col) WHERE x.col = const
the WHERE-restriction is only applied to x. I'm afraid you'll need
to write
FROM x join y using (col) WHERE x.col = const AND y.col = const
Ideally you should be able to write just
FROM x join y using (col) WHERE col = const
but I think that will be taken the same as "x.col = const" :-(
regards, tom lane
I don't know if anything changed on 7.3.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 17:09 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tomasz Myrta <jasiek@klaster.net>
1 sibling, 3 replies; 23+ messages in thread
From: Stephan Szabo @ 2003-01-22 17:09 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Ries van Twisk <ries@jongert.nl>; pgsql-sql
On Wed, 22 Jan 2003, Tomasz Myrta wrote:
> >> Tomasz Myrta <jasiek@klaster.net> writes:
> >> I'd like to split queries into views, but I can't join them - planner
> >> search all of records instead of using index. It works very slow.
>
>
> I think this is the same issue that Stephan identified in his response
> to your other posting ("sub-select with aggregate"). When you write
> FROM x join y using (col) WHERE x.col = const
> the WHERE-restriction is only applied to x. I'm afraid you'll need
> to write
> FROM x join y using (col) WHERE x.col = const AND y.col = const
> Ideally you should be able to write just
> FROM x join y using (col) WHERE col = const
> but I think that will be taken the same as "x.col = const" :-(
> I don't know if anything changed on 7.3.
I don't think so, but this is a general transitivity constraint AFAIK, not
one actually to do with views (ie, if you wrote out the query without a
view, you can run into the same issue). It's somewhat easier to run into
the case with views and the effect may be exasperated by views, but it's
a general condition.
For example:
create table a(a int);
create table c(a int);
sszabo=# explain select * from a join c using (a) where a=3;
QUERY PLAN
-------------------------------------------------------------
Hash Join (cost=1.01..26.08 rows=6 width=8)
Hash Cond: ("outer".a = "inner".a)
-> Seq Scan on c (cost=0.00..20.00 rows=1000 width=4)
-> Hash (cost=1.01..1.01 rows=1 width=4)
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
Filter: (a = 3)
(6 rows)
The filter is applied only to a. So, if you really wanted the
c.a=3 condition to be applied for whatever reason you're out of
luck.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 17:30 Jan Wieck <JanWieck@Yahoo.com>
parent: Tomasz Myrta <jasiek@klaster.net>
1 sibling, 0 replies; 23+ messages in thread
From: Jan Wieck @ 2003-01-22 17:30 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: Ries van Twisk <ries@jongert.nl>; pgsql-sql
Tomasz Myrta wrote:
>
> Jan Wieck wrote:
>
> >Use tables, views and views over views, it's all fine and your indexes
> >will be used.
>
> I can't agree with using views over views. It has some limitations.
> I asked about it on this list several months ago
> and Tom Lane's conclusion was:
It has less to do with the nesting level of rewriting, than with what
you do with the view in general. If you cram up all the functionality
with aggregating subselects into one monster view it might do better,
except for maintaining the code.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 18:38 Tomasz Myrta <jasiek@klaster.net>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 23+ messages in thread
From: Tomasz Myrta @ 2003-01-22 18:38 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Stephan Szabo wrote:
>On Wed, 22 Jan 2003, Tomasz Myrta wrote:
>
>
>>>>Tomasz Myrta writes:
>>>>I'd like to split queries into views, but I can't join them - planner
>>>>search all of records instead of using index. It works very slow.
>>
>>
>>I think this is the same issue that Stephan identified in his response
>>to your other posting ("sub-select with aggregate"). When you write
>> FROM x join y using (col) WHERE x.col = const
>>the WHERE-restriction is only applied to x. I'm afraid you'll need
>>to write
>> FROM x join y using (col) WHERE x.col = const AND y.col = const
>>Ideally you should be able to write just
>> FROM x join y using (col) WHERE col = const
>>but I think that will be taken the same as "x.col = const" :-(
>
>
>
>>I don't know if anything changed on 7.3.
>
>
>I don't think so, but this is a general transitivity constraint AFAIK, not
>one actually to do with views (ie, if you wrote out the query without a
>view, you can run into the same issue). It's somewhat easier to run into
>the case with views and the effect may be exasperated by views, but it's
>a general condition.
>
>For example:
>create table a(a int);
>create table c(a int);
>
>sszabo=# explain select * from a join c using (a) where a=3;
> QUERY PLAN
>-------------------------------------------------------------
> Hash Join (cost=1.01..26.08 rows=6 width=8)
> Hash Cond: ("outer".a = "inner".a)
> -> Seq Scan on c (cost=0.00..20.00 rows=1000 width=4)
> -> Hash (cost=1.01..1.01 rows=1 width=4)
> -> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
> Filter: (a = 3)
>(6 rows)
I don't understand your idea.
explain select * from przystanki p join miasta m using (id_miasta) where field_id=100
Both tables are indexed on field id_miasta. They have enough rows to use indexes.
Nested Loop (cost=0.00..9.48 rows=1 width=64)
-> Index Scan using ind_miasto_przyst on przystanki p (cost=0.00..5.54 rows=1 width=41)
-> Index Scan using miasta_pkey on miasta m (cost=0.00..3.10 rows=1 width=23)
Tomasz Myrta
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 18:55 Tomasz Myrta <jasiek@klaster.net>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 1 reply; 23+ messages in thread
From: Tomasz Myrta @ 2003-01-22 18:55 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Stephan Szabo wrote:
>On Wed, 22 Jan 2003, Tomasz Myrta wrote:
>
>
>>>>Tomasz Myrta writes:
>>>>I'd like to split queries into views, but I can't join them - planner
>>>>search all of records instead of using index. It works very slow.
>>
>>
>>I think this is the same issue that Stephan identified in his response
>>to your other posting ("sub-select with aggregate"). When you write
>> FROM x join y using (col) WHERE x.col = const
>>the WHERE-restriction is only applied to x. I'm afraid you'll need
>>to write
>> FROM x join y using (col) WHERE x.col = const AND y.col = const
>>Ideally you should be able to write just
>> FROM x join y using (col) WHERE col = const
>>but I think that will be taken the same as "x.col = const" :-(
>
>
>
>>I don't know if anything changed on 7.3.
>
>
>I don't think so, but this is a general transitivity constraint AFAIK, not
>one actually to do with views (ie, if you wrote out the query without a
>view, you can run into the same issue). It's somewhat easier to run into
>the case with views and the effect may be exasperated by views, but it's
>a general condition.
>
>For example:
>create table a(a int);
>create table c(a int);
>
>sszabo=# explain select * from a join c using (a) where a=3;
> QUERY PLAN
>-------------------------------------------------------------
> Hash Join (cost=1.01..26.08 rows=6 width=8)
> Hash Cond: ("outer".a = "inner".a)
> -> Seq Scan on c (cost=0.00..20.00 rows=1000 width=4)
> -> Hash (cost=1.01..1.01 rows=1 width=4)
> -> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
> Filter: (a = 3)
>(6 rows)
>
>The filter is applied only to a. So, if you really wanted the
>c.a=3 condition to be applied for whatever reason you're out of
>luck.
Let's make some test:
First, let's create some simple view with 2 tables join:
drop view pm;
create view pm as
select
id_przystanku,
m.nazwa
from
przystanki p
join miasta m using (id_miasta);
explain select * from pm where id_przystanku=1230;
Nested Loop (cost=0.00..6.26 rows=1 width=23)
-> Index Scan using przystanki_pkey on przystanki p (cost=0.00..3.14 rows=1 width=8)
-> Index Scan using miasta_pkey on miasta m (cost=0.00..3.10 rows=1 width=15)
Next, let's try query using this view 2 times with explicit join:
explain select * from pm a join pm b using(id_przystanku) where id_przystanku=1230;
Hash Join (cost=13.00..30.10 rows=1 width=46)
-> Hash Join (cost=6.74..21.02 rows=374 width=23)
-> Seq Scan on przystanki p (cost=0.00..7.74 rows=374 width=8)
-> Hash (cost=5.99..5.99 rows=299 width=15)
-> Seq Scan on miasta m (cost=0.00..5.99 rows=299 width=15)
-> Hash (cost=6.26..6.26 rows=1 width=23)
-> Nested Loop (cost=0.00..6.26 rows=1 width=23)
-> Index Scan using przystanki_pkey on przystanki p (cost=0.00..3.14 rows=1 width=8)
-> Index Scan using miasta_pkey on miasta m (cost=0.00..3.10 rows=1 width=15)
And now similiar view, but without nesting views:
drop view pm2;
create view pm2 as
select
id_przystanku,
m1.nazwa as nazwa1,
m2.nazwa as nazwa2
from
przystanki p1
join miasta m1 using (id_miasta)
join przystanki p2 using (id_przystanku)
join miasta m2 on (m2.id_miasta=p2.id_miasta);
explain select * from pm2 where id_przystanku=1230;
Nested Loop (cost=0.00..12.52 rows=1 width=46)
-> Nested Loop (cost=0.00..9.41 rows=1 width=31)
-> Nested Loop (cost=0.00..6.26 rows=1 width=23)
-> Index Scan using przystanki_pkey on przystanki p1 (cost=0.00..3.14 rows=1 width=8)
-> Index Scan using miasta_pkey on miasta m1 (cost=0.00..3.10 rows=1 width=15)
-> Index Scan using przystanki_pkey on przystanki p2 (cost=0.00..3.14 rows=1 width=8)
-> Index Scan using miasta_pkey on miasta m2 (cost=0.00..3.10 rows=1 width=15)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 21:09 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 23+ messages in thread
From: Stephan Szabo @ 2003-01-22 21:09 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
On Wed, 22 Jan 2003, Tomasz Myrta wrote:
> Let's make some test:
>
> First, let's create some simple view with 2 tables join:
> drop view pm;
> create view pm as
> select
> id_przystanku,
> m.nazwa
> from
> przystanki p
> join miasta m using (id_miasta);
>
> explain select * from pm where id_przystanku=1230;
> Nested Loop (cost=0.00..6.26 rows=1 width=23)
> -> Index Scan using przystanki_pkey on przystanki p (cost=0.00..3.14 rows=1 width=8)
> -> Index Scan using miasta_pkey on miasta m (cost=0.00..3.10 rows=1 width=15)
>
>
> Next, let's try query using this view 2 times with explicit join:
> explain select * from pm a join pm b using(id_przystanku) where id_przystanku=1230;
> Hash Join (cost=13.00..30.10 rows=1 width=46)
> -> Hash Join (cost=6.74..21.02 rows=374 width=23)
> -> Seq Scan on przystanki p (cost=0.00..7.74 rows=374 width=8)
> -> Hash (cost=5.99..5.99 rows=299 width=15)
> -> Seq Scan on miasta m (cost=0.00..5.99 rows=299 width=15)
> -> Hash (cost=6.26..6.26 rows=1 width=23)
> -> Nested Loop (cost=0.00..6.26 rows=1 width=23)
> -> Index Scan using przystanki_pkey on przystanki p (cost=0.00..3.14 rows=1 width=8)
> -> Index Scan using miasta_pkey on miasta m (cost=0.00..3.10 rows=1 width=15)
>
> And now similiar view, but without nesting views:
> drop view pm2;
> create view pm2 as
> select
> id_przystanku,
> m1.nazwa as nazwa1,
> m2.nazwa as nazwa2
> from
> przystanki p1
> join miasta m1 using (id_miasta)
> join przystanki p2 using (id_przystanku)
> join miasta m2 on (m2.id_miasta=p2.id_miasta);
That's not the same join for optimization purposes
since postgresql treats explicit join syntax as a
constraint on the ordering of joins.
The same join would be something like:
przystanki p1 join miasta m1 using (id_miasta)
join (przystanki p2 join miasta m2 using (id_miasta))
using (id_przystanku)
minus the fact I think you'd need some explicit naming in
there.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 21:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 1 reply; 23+ messages in thread
From: Tom Lane @ 2003-01-22 21:35 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Tomasz Myrta <jasiek@klaster.net>; Jan Wieck <JanWieck@Yahoo.com>; Ries van Twisk <ries@jongert.nl>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> ... but this is a general transitivity constraint AFAIK, not
> one actually to do with views (ie, if you wrote out the query without a
> view, you can run into the same issue). It's somewhat easier to run into
> the case with views and the effect may be exasperated by views, but it's
> a general condition.
Right. Views are just macros --- they don't in themselves affect the
planner's ability to generate a good plan. But they make it easier to
generate baroque queries without thinking much about what you're doing,
and in complex queries the planner doesn't always make the deductions
and simplifications that are obvious to a human.
> For example:
> create table a(a int);
> create table c(a int);
> sszabo=# explain select * from a join c using (a) where a=3;
> QUERY PLAN
> -------------------------------------------------------------
> Hash Join (cost=1.01..26.08 rows=6 width=8)
> Hash Cond: ("outer".a = "inner".a)
> -> Seq Scan on c (cost=0.00..20.00 rows=1000 width=4)
> -> Hash (cost=1.01..1.01 rows=1 width=4)
> -> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
> Filter: (a = 3)
> (6 rows)
> The filter is applied only to a. So, if you really wanted the
> c.a=3 condition to be applied for whatever reason you're out of
> luck.
FWIW, CVS tip is brighter: the condition does propagate to both relations.
Hash Join (cost=22.51..45.04 rows=1 width=8)
Hash Cond: ("outer".a = "inner".a)
-> Seq Scan on a (cost=0.00..22.50 rows=5 width=4)
Filter: (a = 3)
-> Hash (cost=22.50..22.50 rows=5 width=4)
-> Seq Scan on c (cost=0.00..22.50 rows=5 width=4)
Filter: (3 = a)
The reason this is useful is that (a) fewer rows need to be joined,
and (b) we may be able to make effective use of indexes on both tables.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-22 21:54 Tomasz Myrta <jasiek@klaster.net>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 23+ messages in thread
From: Tomasz Myrta @ 2003-01-22 21:54 UTC (permalink / raw)
To: pgsql-sql
Stephan Szabo wrote:
>That's not the same join for optimization purposes
>since postgresql treats explicit join syntax as a
>constraint on the ordering of joins.
>
>The same join would be something like:
>
>przystanki p1 join miasta m1 using (id_miasta)
>join (przystanki p2 join miasta m2 using (id_miasta))
> using (id_przystanku)
>
>minus the fact I think you'd need some explicit naming in
>there.
You are right.
The result of your query is:
explain select * from
przystanki p1 join miasta m1 using (id_miasta)
join (przystanki p2 join miasta m2 using (id_miasta))
using (id_przystanku)
where id_przystanku=1230
Hash Join (cost=13.00..30.10 rows=1 width=128)
-> Hash Join (cost=6.74..21.02 rows=374 width=64)
-> Seq Scan on przystanki p2 (cost=0.00..7.74 rows=374 width=41)
-> Hash (cost=5.99..5.99 rows=299 width=23)
-> Seq Scan on miasta m2 (cost=0.00..5.99 rows=299 width=23)
-> Hash (cost=6.26..6.26 rows=1 width=64)
-> Nested Loop (cost=0.00..6.26 rows=1 width=64)
-> Index Scan using przystanki_pkey on przystanki p1 (cost=0.00..3.14 rows=1 width=41)
-> Index Scan using miasta_pkey on miasta m1 (cost=0.00..3.10 rows=1 width=23)
Anyway - is it possible to expose table "przystanki alias p2" to get valid result?
The problem is similiar to my problem "sub-select with aggregate" dated on 2002-10-23
and the answer (which doesn't satisfy me) is the same:
if we pass static values to "przystanki p2 join miasta m2", the query will work ok:
explain select * from
przystanki p1 join miasta m1 using (id_miasta)
cross join (przystanki p2 join miasta m2 using (id_miasta)) X
where p1.id_przystanku=1230 and X.id_przystanku=1230
Nested Loop (cost=0.00..12.52 rows=1 width=128)
-> Nested Loop (cost=0.00..6.26 rows=1 width=64)
-> Index Scan using przystanki_pkey on przystanki p1 (cost=0.00..3.14 rows=1 width=41)
-> Index Scan using miasta_pkey on miasta m1 (cost=0.00..3.10 rows=1 width=23)
-> Materialize (cost=6.26..6.26 rows=1 width=64)
-> Nested Loop (cost=0.00..6.26 rows=1 width=64)
-> Index Scan using przystanki_pkey on przystanki p2 (cost=0.00..3.14 rows=1 width=41)
-> Index Scan using miasta_pkey on miasta m2 (cost=0.00..3.10 rows=1 width=23)
Stephan - I have some problems with mail relay to you.
Does my mail server have any open-relay problem, or something like this (213.25.37.66) ?
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 00:45 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 23+ messages in thread
From: Stephan Szabo @ 2003-01-23 00:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tomasz Myrta <jasiek@klaster.net>; Jan Wieck <JanWieck@Yahoo.com>; Ries van Twisk <ries@jongert.nl>; pgsql-sql
On Wed, 22 Jan 2003, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > The filter is applied only to a. So, if you really wanted the
> > c.a=3 condition to be applied for whatever reason you're out of
> > luck.
>
> FWIW, CVS tip is brighter: the condition does propagate to both relations.
>
> Hash Join (cost=22.51..45.04 rows=1 width=8)
> Hash Cond: ("outer".a = "inner".a)
> -> Seq Scan on a (cost=0.00..22.50 rows=5 width=4)
> Filter: (a = 3)
> -> Hash (cost=22.50..22.50 rows=5 width=4)
> -> Seq Scan on c (cost=0.00..22.50 rows=5 width=4)
> Filter: (3 = a)
>
> The reason this is useful is that (a) fewer rows need to be joined,
> and (b) we may be able to make effective use of indexes on both tables.
Yeah. I was going to ask how hard you thought it would be to do for
this particular sort of case. I thought about the simple case of using
and realized it'd probably be reasonable in amount of work, but it seems
I don't have to think about it. :)
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 00:52 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 23+ messages in thread
From: Stephan Szabo @ 2003-01-23 00:52 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
On Wed, 22 Jan 2003, Tomasz Myrta wrote:
> Stephan Szabo wrote:
>
> >That's not the same join for optimization purposes
> >since postgresql treats explicit join syntax as a
> >constraint on the ordering of joins.
> >
> >The same join would be something like:
> >
> >przystanki p1 join miasta m1 using (id_miasta)
> >join (przystanki p2 join miasta m2 using (id_miasta))
> > using (id_przystanku)
> >
> >minus the fact I think you'd need some explicit naming in
> >there.
>
> You are right.
>
> The result of your query is:
> explain select * from
> przystanki p1 join miasta m1 using (id_miasta)
> join (przystanki p2 join miasta m2 using (id_miasta))
> using (id_przystanku)
> where id_przystanku=1230
>
> Hash Join (cost=13.00..30.10 rows=1 width=128)
> -> Hash Join (cost=6.74..21.02 rows=374 width=64)
> -> Seq Scan on przystanki p2 (cost=0.00..7.74 rows=374 width=41)
> -> Hash (cost=5.99..5.99 rows=299 width=23)
> -> Seq Scan on miasta m2 (cost=0.00..5.99 rows=299 width=23)
> -> Hash (cost=6.26..6.26 rows=1 width=64)
> -> Nested Loop (cost=0.00..6.26 rows=1 width=64)
> -> Index Scan using przystanki_pkey on przystanki p1 (cost=0.00..3.14 rows=1 width=41)
> -> Index Scan using miasta_pkey on miasta m1 (cost=0.00..3.10 rows=1 width=23)
>
> Anyway - is it possible to expose table "przystanki alias p2" to get valid result?
I think it's possible that the work Tom mentioned in current CVS may
make these cases work the way you want. I don't have access to my
test machine to try it right now however.
> Stephan - I have some problems with mail relay to you.
> Does my mail server have any open-relay problem, or something like this (213.25.37.66) ?
Doesn't seem to be a simple open relay (but I didn't try anything
complicated). It acted a little wierd about email addresses in
reacting to my telnet, but I think it was acting correctly as far as
standards are concerned.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 01:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 23+ messages in thread
From: Tom Lane @ 2003-01-23 01:01 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Tomasz Myrta <jasiek@klaster.net>; Jan Wieck <JanWieck@Yahoo.com>; Ries van Twisk <ries@jongert.nl>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Wed, 22 Jan 2003, Tom Lane wrote:
>> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>>> The filter is applied only to a. So, if you really wanted the
>>> c.a=3 condition to be applied for whatever reason you're out of
>>> luck.
>>
>> FWIW, CVS tip is brighter: the condition does propagate to both relations.
> Yeah. I was going to ask how hard you thought it would be to do for
> this particular sort of case. I thought about the simple case of using
> and realized it'd probably be reasonable in amount of work, but it seems
> I don't have to think about it. :)
It could still use more eyeballs looking at it. One thing I'm concerned
about is whether the extra (derived) conditions lead to double-counting
restrictivity and thus underestimating the number of result rows. I
haven't had time to really test that, but I suspect there may be a problem.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 08:08 Ries van Twisk <ries@jongert.nl>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 23+ messages in thread
From: Ries van Twisk @ 2003-01-23 08:08 UTC (permalink / raw)
To: pgsql-sql
First of all I want to thank you for all responses! I was overwhelmed with
it :D
Below you find the schema I'm currently using and the output of explain. I
removed all comments so the mail will be small, the schema is still work in
progress. I especially I need to take a look at the indexes. Any hints will
be appreciated.
best reghards,
Ries van Twisk
<-----------
Here you find the output of the explain again:
I cannot yet read the output of explain si I'm not sure if the output looks
good or bad.
echo "VACUUM ANALYZE; EXPLAIN SELECT * FROM full_cablelist WHERE
projectcode=5" | psql testdb > /tmp/explain.txt
NOTICE: QUERY PLAN:
Hash Join (cost=26.28..39.00 rows=23 width=200)
-> Hash Join (cost=24.85..37.17 rows=23 width=182)
-> Hash Join (cost=23.43..35.34 rows=23 width=164)
-> Seq Scan on libitems lit (cost=0.00..7.39 rows=339
width=27)
-> Hash (cost=23.37..23.37 rows=23 width=137)
-> Hash Join (cost=11.05..23.37 rows=23 width=137)
-> Hash Join (cost=9.75..21.67 rows=23
width=120)
-> Seq Scan on libitems lif
(cost=0.00..7.39 rows=339 width=27)
-> Hash (cost=9.69..9.69 rows=23 width=93)
-> Hash Join (cost=4.76..9.69
rows=23 width=93)
-> Hash Join (cost=3.46..7.99
rows=23 width=76)
-> Hash Join
(cost=2.42..6.32 rows=69 width=63)
-> Seq Scan on
cablelist cl (cost=0.00..2.69 rows=69 width=41)
-> Hash
(cost=2.06..2.06 rows=106 width=22)
-> Seq Scan
on cabletypes ct (cost=0.00..2.06 rows=106 width=22)
-> Hash (cost=1.04..1.04
rows=1 width=13)
-> Seq Scan on
projectcodes pc (cost=0.00..1.04 rows=1 width=13)
-> Hash (cost=1.24..1.24
rows=24 width=17)
-> Seq Scan on
libconnections lcf (cost=0.00..1.24 rows=24 width=17)
-> Hash (cost=1.24..1.24 rows=24 width=17)
-> Seq Scan on libconnections lct
(cost=0.00..1.24 rows=24 width=17)
-> Hash (cost=1.34..1.34 rows=34 width=18)
-> Seq Scan on shiplocations slt (cost=0.00..1.34 rows=34
width=18)
-> Hash (cost=1.34..1.34 rows=34 width=18)
-> Seq Scan on shiplocations slf (cost=0.00..1.34 rows=34
width=18)
<------------------
CREATE FUNCTION ord_fn (text,text) RETURNS text AS '
SELECT (CASE
WHEN $1 < $2
THEN $1 || $2
ELSE $2 || $1
END) as t;
' LANGUAGE SQL WITH (iscachable);
CREATE FUNCTION plpgsql_call_handler ()
RETURNS OPAQUE
AS '/usr/lib/postgresql/plpgsql.so'
LANGUAGE 'C';
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE TABLE cabletypes (
id SERIAL,
cabletype VARCHAR(24) NOT NULL CHECK ( length(cabletype) > 1 ), -- Naam
van de kabel
coretype VARCHAR(16) NOT NULL CHECK ( length(coretype) > 1 ) -- Type
kabel/aantal aders
);
CREATE UNIQUE INDEX cabletypes_idx ON cabletypes (id);
CREATE FUNCTION f_check_cabletypes() RETURNS OPAQUE AS '
DECLARE
check RECORD;
BEGIN
SELECT INTO check * FROM cabletypes WHERE cabletype=NEW.cabletype AND
coretype=NEW.coretype LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION ''[0001] cabletype and coretype combination already
exsists in cabletypes!'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER tr_cabletypes
BEFORE INSERT OR UPDATE ON cabletypes FOR EACH ROW
EXECUTE PROCEDURE f_check_cabletypes();
CREATE TABLE marktypes (
id SERIAL,
name VARCHAR(24) NOT NULL UNIQUE, -- Naam van de markering
color INTEGER NOT NULL -- Eventuele kleur
);
CREATE UNIQUE INDEX marktypes_idx ON marktypes (id);
CREATE TABLE projectcodes (
id SERIAL,
projectcode VARCHAR(16) NOT NULL UNIQUE, -- Project code naam
projectname VARCHAR(64) NOT NULL, -- Project uitleg
deleted BOOLEAN DEFAULT 'false' NOT NULL
);
CREATE UNIQUE INDEX projectcodes_idx ON projectcodes (id);
CREATE TABLE libitems (
id SERIAL,
projectcodeid INTEGER DEFAULT 0 NOT NULL REFERENCES projectcodes(id) ON
DELETE CASCADE,
name VARCHAR(32) NOT NULL UNIQUE -- Naam van de item bv boiler
);
CREATE UNIQUE INDEX libitems_idx ON libitems(id);
CREATE FUNCTION f_check_libitems() RETURNS OPAQUE AS '
DECLARE
check RECORD;
BEGIN
-- Update van de name mag wel
IF TG_OP = ''UPDATE'' THEN
IF NEW.projectcodeid = OLD.projectcodeid AND NEW.name = OLD.name THEN
RETURN NEW;
END IF;
END IF;
-- Controleer of the combinatie projectcode en ribnummer unique is
SELECT INTO check * FROM libitems WHERE projectcodeid=new.projectcodeid AND
name=new.name LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION ''[0005] projectcodide and name combination already
exsists in shiplocations!'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER tr_linitems
BEFORE INSERT OR UPDATE ON libitems FOR EACH ROW
EXECUTE PROCEDURE f_check_libitems();
CREATE TABLE libconnections (
id SERIAL,
libitemid INTEGER CONSTRAINT libitemid_con NOT NULL REFERENCES
libitems(id) ON DELETE CASCADE,
name VARCHAR(32), -- Naam van de aansluiting aan een item
cableno VARCHAR(8)
);
CREATE UNIQUE INDEX libconnections_idx ON libconnections(id);
CREATE FUNCTION f_check_libconnections() RETURNS OPAQUE AS '
DECLARE
check RECORD;
BEGIN
-- Update van de name mag wel
IF TG_OP = ''UPDATE'' THEN
IF NEW.libitemid = OLD.libitemid AND NEW.name = OLD.name THEN
RETURN NEW;
END IF;
END IF;
SELECT INTO check * FROM libconnections WHERE libitemid=NEW.libitemid AND
name=NEW.name LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION ''[0002] name and item combination already exsists in
libconnections!'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER tr_libconnections
BEFORE INSERT OR UPDATE ON libconnections FOR EACH ROW
EXECUTE PROCEDURE f_check_libconnections();
---------------
CREATE TABLE shiplocations (
id SERIAL,
projectcodeid INTEGER NOT NULL REFERENCES projectcodes(id) ON DELETE
CASCADE,
rib SMALLINT DEFAULT 0 NOT NULL CHECK (rib>0), -- rib nummer
name VARCHAR(32) NOT NULL, -- Naam van de locatie (bv voorschip,
middenschip, achterschip)
loc SMALLINT DEFAULT 0 NOT NULL CHECK (loc>=0 AND loc<5)
);
CREATE VIEW shiplocationst AS SELECT id, projectcodeid, rib, name,
CASE loc WHEN 0 THEN 'ries'
when 1 THEN 'ries1'
when 2 THEN 'ries2'
ELSE 'other'
END
FROM shiplocations;
CREATE UNIQUE INDEX shiplocations_idx ON shiplocations(id);
CREATE TABLE cablelist (
id SERIAL,
cableno VARCHAR(8),
projectcodeid INTEGER CONSTRAINT cablelist_projectcodes_con NOT NULL
REFERENCES projectcodes(id) ON DELETE CASCADE,
fromconnid INTEGER CONSTRAINT cablelist_fromconnid_con NOT NULL
REFERENCES libconnections(id) ON DELETE CASCADE,
toconnid INTEGER CONSTRAINT cablelist_toconnid_con NOT NULL REFERENCES
libconnections(id) ON DELETE CASCADE,
fromshiplocationid INTEGER CONSTRAINT cablelist_fromshiplocationid_con
NOT NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
toshiplocationid INTEGER CONSTRAINT cablelist_toshiplocationid_con NOT
NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
marktypesid INTEGER CONSTRAINT cablelist_tomarktypeid_con NOT NULL
REFERENCES marktypes(id) ON DELETE CASCADE,
cabletypeid INTEGER CONSTRAINT cablelist_cabletypeid_con NOT NULL
REFERENCES cabletypes(id) ON DELETE CASCADE,
cut BOOLEAN DEFAULT 'false' NOT NULL,
placed BOOLEAN DEFAULT 'false' NOT NULL,
ok BOOLEAN DEFAULT 'false'
);
CREATE VIEW full_cablelist AS
SELECT cl.id,
cl.cableno AS cableno,
pc.projectcode AS projectcode,
pc.id AS projectcodeid,
lcf.name AS fconnection, lct.name AS tconnection,
lif.name AS fitem, lit.name AS titem,
slf.rib AS frib,slt.rib AS trib,
slf.name AS fname, slt.name AS tname,
ct.cabletype AS cabletype, ct.coretype AS coretype,
cl.cut,
cl.placed,
cl.ok
FROM cablelist AS cl,
libconnections AS lcf, libconnections AS lct,
libitems AS lif, libitems AS lit,
shiplocations AS slf, shiplocations AS slt,
projectcodes AS pc,
cabletypes AS ct
WHERE
pc.id=cl.projectcodeid AND
lcf.id=cl.fromconnid AND lct.id=cl.toconnid AND
lif.id=lcf.libitemid AND lit.id=lct.libitemid AND
slf.id=cl.fromshiplocationid AND slt.id=cl.toshiplocationid AND
ct.id=cl.cabletypeid
;
CREATE FUNCTION f_find_free_cableno(INTEGER,VARCHAR(4)) RETURNS VARCHAR(8)
AS '
DECLARE
pcid ALIAS FOR $1;
prefix ALIAS FOR $2;
cnmax INTEGER;
newcableno INTEGER;
CHECK RECORD;
cablename VARCHAR(10);
BEGIN
newcableno=0;
SELECT INTO cnmax count(cableno) FROM cablelist WHERE projectcodeid = pcid;
WHILE newcableno <= cnmax LOOP
newcableno=newcableno+1;
-- Grote getallen dan 9998 worden niet toegstaan omdat de lpad functie
-- ook een truncate doet
IF newcableno > 998 THEN
RETURN NULL;
END IF;
-- Controleer op prefix, zo ja gebruik deze
IF prefix != '''' THEN
cablename = prefix || ''.'' || lpad(newcableno, 3, ''0'');
ELSE
cablename = lpad(newcableno, 3, ''0'');
END IF;
-- Controleer of dit nummer al bestaad
SELECT INTO CHECK * FROM cablelist WHERE projectcodeid = pcid AND
cableno=cablename;
IF NOT FOUND THEN
RETURN cablename;
END IF;
END LOOP;
-- Niets gevonden, hier zouden we normaal gesproken niet
-- komen. Dit omdat we <= testen en dis altijd cnmax+1 testen
RETURN NULL;
-- Alle nummer zijn in gebruik, kies nu een volgt nummer
-- newcableno zal ALTIJD <= 998 zijn en dus altijd maar 3 positie innemen
newcableno=newcableno+1;
IF prefix != '''' THEN
cablename = prefix || ''.'' || lpad(newcableno, 3, ''0'');
ELSE
cablename = lpad(newcableno, 3, ''0'');
END IF;
RETURN cableno;
END;
' LANGUAGE 'plpgsql';
> -----Oorspronkelijk bericht-----
> Van: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]Namens Tomasz Myrta
> Verzonden: woensdag 22 januari 2003 16:46
> Aan: Ries van Twisk
> CC: pgsql-sql@postgresql.org
> Onderwerp: Re: [SQL] To use a VIEW or not to use a View.....
>
>
> Ries van Twisk wrote:
>
> >Dear PostgreSQL users,
> >
> >I have a view and a table,
> >
> >I understand that when a frontend accesses a VIEW that
> PostgreSQL cannot use
> >a index on that view.
> >For example when I do this: SELECT * FROM full_cablelist WHERE
> >projectocode=5; Correct?
> >
> >Now I just want to make sure for myself if the VIEW I
> created is the right
> >way to go, or is it better
> >to contruct a SQL in my application that looks like the view
> and send it to
> >postgreSQL so it will
> >use all indexes correctly. I use postgreSQL 7.2.1
> >
> >I beliefe there is a change in postgreSQL 7.3.x on which I
> can cache a
> >view??? Not sure what the issue was.
> >
> >I ask this because I expect pore performance in feature when
> the cablelist
> >table holds up to around 20.000 rows.
> >Each query to full_cablelist will return around 1200 rows.
> >
> >best regards,
> >Ries van Twisk
> >
> >
> >
> >-- CABLE LIST
> >CREATE TABLE cablelist (
> > id SERIAL,
> > cableno VARCHAR(8),
> > projectcodeid INTEGER CONSTRAINT
> cablelist_projectcodes_con NOT NULL
> >REFERENCES projectcodes(id) ON DELETE CASCADE,
> > fromconnid INTEGER CONSTRAINT
> cablelist_fromconnid_con NOT NULL
> >REFERENCES libconnections(id) ON DELETE CASCADE,
> > toconnid INTEGER CONSTRAINT
> cablelist_toconnid_con NOT NULL REFERENCES
> >libconnections(id) ON DELETE CASCADE,
> > fromshiplocationid INTEGER CONSTRAINT
> cablelist_fromshiplocationid_con
> >NOT NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
> > toshiplocationid INTEGER CONSTRAINT
> cablelist_toshiplocationid_con NOT
> >NULL REFERENCES shiplocations(id) ON DELETE CASCADE,
> > marktypesid INTEGER CONSTRAINT
> cablelist_tomarktypeid_con NOT NULL
> >REFERENCES marktypes(id) ON DELETE CASCADE,
> > cabletypeid INTEGER CONSTRAINT
> cablelist_cabletypeid_con NOT NULL
> >REFERENCES cabletypes(id) ON DELETE CASCADE,
> > cut BOOLEAN DEFAULT 'false' NOT NULL,
> > placed BOOLEAN DEFAULT 'false' NOT NULL,
> > ok BOOLEAN DEFAULT 'false'
> >);
> >
> >
> >-- CABLE LIST VIEW
> >CREATE VIEW full_cablelist AS
> >SELECT cl.id,
> >cl.cableno AS cableno,
> >pc.projectcode AS projectcode,
> >pc.id AS projectcodeid,
> >lcf.name AS fconnection, lct.name AS tconnection,
> >lif.name AS fitem, lit.name AS titem,
> >slf.rib AS frib,slt.rib AS trib,
> >slf.name AS fname, slt.name AS tname,
> >ct.cabletype AS cabletype, ct.coretype AS coretype,
> >cl.cut,
> >cl.placed,
> >cl.ok
> >
> >FROM cablelist AS cl,
> >libconnections AS lcf, libconnections AS lct,
> >libitems AS lif, libitems AS lit,
> >shiplocations AS slf, shiplocations AS slt,
> >projectcodes AS pc,
> >cabletypes AS ct
> >
> >WHERE
> >pc.id=cl.projectcodeid AND
> >lcf.id=cl.fromconnid AND lct.id=cl.toconnid AND
> >lif.id=lcf.libitemid AND lit.id=lct.libitemid AND
> >slf.id=cl.fromshiplocationid AND slt.id=cl.toshiplocationid AND
> >ct.id=cl.cabletypeid
>
> How can we help you with table indexing, if you didn't write anything
> about indexes you have already created on your tables? Anyway
> you don't need
> indexes on a view, but indexes on your tables. You need also
> a well constructed
> view.
>
> For your query:
> - make sure, you have index on projectcodes(projectcode) - if
> you have many projectcodes
> and index on cablelist(projectcodeid)
> - make sure, you did "vacuum analyze" before you test your query.
> - send result of "explain analyze SELECT * FROM full_cablelist WHERE
> projectocode=5" to us.
>
> Anyway result can't be too fast, because query returns >1000
> rows which is rather
> a big amount of data.
>
> Regards,
> Tomasz Myrta
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 10:49 Tomasz Myrta <jasiek@klaster.net>
parent: Ries van Twisk <ries@jongert.nl>
0 siblings, 0 replies; 23+ messages in thread
From: Tomasz Myrta @ 2003-01-23 10:49 UTC (permalink / raw)
To: Ries van Twisk <ries@jongert.nl>; +Cc: pgsql-sql
Ries van Twisk wrote:
> First of all I want to thank you for all responses! I was overwhelmed with
> it :D
>
> Below you find the schema I'm currently using and the output of explain. I
> removed all comments so the mail will be small, the schema is still work in
> progress. I especially I need to take a look at the indexes. Any hints will
> be appreciated.
>
> best reghards,
> Ries van Twisk
>
>
> <-----------
> Here you find the output of the explain again:
> I cannot yet read the output of explain si I'm not sure if the output looks
> good or bad.
>
>
It looks like your cablelist table doesn't contain too many records,
so result is inacurate. Postgresql doesn't use indexes if you have too
little rows.
First look on your explain is ok, your query should work fine if tables
are well indexed.
Make additional tests with tables containing more rows, "explain analyze"
helps a bit, because it shows real times.
Tomasz Myrta
> echo "VACUUM ANALYZE; EXPLAIN SELECT * FROM full_cablelist WHERE
> projectcode=5" | psql testdb > /tmp/explain.txt
>
> NOTICE: QUERY PLAN:
>
> Hash Join (cost=26.28..39.00 rows=23 width=200)
> -> Hash Join (cost=24.85..37.17 rows=23 width=182)
> -> Hash Join (cost=23.43..35.34 rows=23 width=164)
> -> Seq Scan on libitems lit (cost=0.00..7.39 rows=339
> width=27)
> -> Hash (cost=23.37..23.37 rows=23 width=137)
> -> Hash Join (cost=11.05..23.37 rows=23 width=137)
> -> Hash Join (cost=9.75..21.67 rows=23
> width=120)
> -> Seq Scan on libitems lif
> (cost=0.00..7.39 rows=339 width=27)
> -> Hash (cost=9.69..9.69 rows=23 width=93)
> -> Hash Join (cost=4.76..9.69
> rows=23 width=93)
> -> Hash Join (cost=3.46..7.99
> rows=23 width=76)
> -> Hash Join
> (cost=2.42..6.32 rows=69 width=63)
> -> Seq Scan on
> cablelist cl (cost=0.00..2.69 rows=69 width=41)
> -> Hash
> (cost=2.06..2.06 rows=106 width=22)
> -> Seq Scan
> on cabletypes ct (cost=0.00..2.06 rows=106 width=22)
> -> Hash (cost=1.04..1.04
> rows=1 width=13)
> -> Seq Scan on
> projectcodes pc (cost=0.00..1.04 rows=1 width=13)
> -> Hash (cost=1.24..1.24
> rows=24 width=17)
> -> Seq Scan on
> libconnections lcf (cost=0.00..1.24 rows=24 width=17)
> -> Hash (cost=1.24..1.24 rows=24 width=17)
> -> Seq Scan on libconnections lct
> (cost=0.00..1.24 rows=24 width=17)
> -> Hash (cost=1.34..1.34 rows=34 width=18)
> -> Seq Scan on shiplocations slt (cost=0.00..1.34 rows=34
> width=18)
> -> Hash (cost=1.34..1.34 rows=34 width=18)
> -> Seq Scan on shiplocations slf (cost=0.00..1.34 rows=34
> width=18)
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 16:53 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 23+ messages in thread
From: Stephan Szabo @ 2003-01-23 16:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
On Wed, 22 Jan 2003, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Wed, 22 Jan 2003, Tom Lane wrote:
> >> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> >>> The filter is applied only to a. So, if you really wanted the
> >>> c.a=3 condition to be applied for whatever reason you're out of
> >>> luck.
> >>
> >> FWIW, CVS tip is brighter: the condition does propagate to both relations.
>
> > Yeah. I was going to ask how hard you thought it would be to do for
> > this particular sort of case. I thought about the simple case of using
> > and realized it'd probably be reasonable in amount of work, but it seems
> > I don't have to think about it. :)
>
> It could still use more eyeballs looking at it. One thing I'm concerned
> about is whether the extra (derived) conditions lead to double-counting
> restrictivity and thus underestimating the number of result rows. I
> haven't had time to really test that, but I suspect there may be a problem.
I haven't looked at code yet but tried examples like Tomasz's and some
simple ones and have gotten reasonable seeming output for the estimates
given accurate statistics (joining two estimate 3 outputs, getting 8 for
the estimated rows, joining that with another copy getting 50 some odd
where in this case the real would be 81). Not that I did a
particularly thorough test. I hope to get a chance over the next couple
of days to look and run more tests.
Tomasz, if you have the chance, you might want to try CVS and see what it
does for the queries you've been working with.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 17:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 1 reply; 23+ messages in thread
From: Tom Lane @ 2003-01-23 17:21 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Wed, 22 Jan 2003, Tom Lane wrote:
>> It could still use more eyeballs looking at it. One thing I'm concerned
>> about is whether the extra (derived) conditions lead to double-counting
>> restrictivity and thus underestimating the number of result rows. I
>> haven't had time to really test that, but I suspect there may be a problem.
> I haven't looked at code yet but tried examples like Tomasz's and some
> simple ones and have gotten reasonable seeming output for the estimates
> given accurate statistics
I realized this morning that there definitely is a problem. Consider
this example using the regression database:
regression=# explain analyze select * from tenk1 a join tenk1 b using(ten)
regression-# where ten = 3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Merge Join (cost=1055.45..2102.12 rows=83006 width=488) (actual time=582.97..65486.57 rows=1000000 loops=1)
Merge Cond: ("outer".ten = "inner".ten)
-> Sort (cost=527.73..530.00 rows=910 width=244) (actual time=373.57..382.48 rows=1000 loops=1)
Sort Key: a.ten
-> Seq Scan on tenk1 a (cost=0.00..483.00 rows=910 width=244) (actual time=8.98..330.39 rows=1000 loops=1)
Filter: (ten = 3)
-> Sort (cost=527.73..530.00 rows=910 width=244) (actual time=209.19..8057.64 rows=999001 loops=1)
Sort Key: b.ten
-> Seq Scan on tenk1 b (cost=0.00..483.00 rows=910 width=244) (actual time=0.40..193.93 rows=1000 loops=1)
Filter: (3 = ten)
Total runtime: 73291.01 msec
(11 rows)
The condition "ten=3" will select 1000 rows out of the 10000 in the
table. But, once we have applied that condition to both sides of the
join, the join condition "a.ten = b.ten" is a no-op --- it will not
reject any pair of rows coming out of the seqscans. Presently we count
its restrictivity anyway, so the estimated row count at the merge is a
bad underestimate.
Not only should we ignore the join condition for selectivity purposes,
but it's a waste of time for execution as well. We could have
implemented the above query as a nestloop with no join condition, and
saved the effort of the sort and merge logic.
What I was thinking was that any time the code sees a "var = const"
clause as part of a mergejoin equivalence set, we could mark all the
"var = var" clauses in the same set as no-ops. For example, given
WHERE a.f1 = b.f2 AND b.f2 = c.f3 AND c.f3 = 42
then after we finish deducing a.f1 = 42 and b.f2 = 42, there is no
longer any value in either of the original clauses a.f1 = b.f2 and
b.f2 = c.f3, nor in the other deduced clause a.f1 = c.f3. This would
take a little bit of restructuring of generate_implied_equalities() and
process_implied_equality(), but it doesn't seem too difficult to do.
Thoughts? Are there any holes in that logic?
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 17:46 jasiek@klaster.net
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 23+ messages in thread
From: jasiek@klaster.net @ 2003-01-23 17:46 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
On Thu, Jan 23, 2003 at 08:53:53AM -0800, Stephan Szabo wrote:
> On Wed, 22 Jan 2003, Tom Lane wrote:
>
> I haven't looked at code yet but tried examples like Tomasz's and some
> simple ones and have gotten reasonable seeming output for the estimates
> given accurate statistics (joining two estimate 3 outputs, getting 8 for
> the estimated rows, joining that with another copy getting 50 some odd
> where in this case the real would be 81). Not that I did a
> particularly thorough test. I hope to get a chance over the next couple
> of days to look and run more tests.
>
> Tomasz, if you have the chance, you might want to try CVS and see what it
> does for the queries you've been working with.
Not too easy. Currently I have only windows machine with
Postgresql/cygwin. I use dial-up for accessing internet, which isn't
nice to use. I will try this if I find some free computer to install
postgresql/linux ;-)
Anyway I already gave up this kind of query, especially I can't use CVS
as production server (should I?)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 18:21 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 23+ messages in thread
From: Stephan Szabo @ 2003-01-23 18:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
On Thu, 23 Jan 2003, Tom Lane wrote:
> regression=# explain analyze select * from tenk1 a join tenk1 b using(ten)
> regression-# where ten = 3;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=1055.45..2102.12 rows=83006 width=488) (actual time=582.97..65486.57 rows=1000000 loops=1)
> Merge Cond: ("outer".ten = "inner".ten)
> -> Sort (cost=527.73..530.00 rows=910 width=244) (actual time=373.57..382.48 rows=1000 loops=1)
> Sort Key: a.ten
> -> Seq Scan on tenk1 a (cost=0.00..483.00 rows=910 width=244) (actual time=8.98..330.39 rows=1000 loops=1)
> Filter: (ten = 3)
> -> Sort (cost=527.73..530.00 rows=910 width=244) (actual time=209.19..8057.64 rows=999001 loops=1)
> Sort Key: b.ten
> -> Seq Scan on tenk1 b (cost=0.00..483.00 rows=910 width=244) (actual time=0.40..193.93 rows=1000 loops=1)
> Filter: (3 = ten)
> Total runtime: 73291.01 msec
> (11 rows)
Yeah, I see it once I got the estimated selectivity being smaller in the
joins in my test database as well.
> Not only should we ignore the join condition for selectivity purposes,
> but it's a waste of time for execution as well. We could have
> implemented the above query as a nestloop with no join condition, and
> saved the effort of the sort and merge logic.
>
> What I was thinking was that any time the code sees a "var = const"
> clause as part of a mergejoin equivalence set, we could mark all the
> "var = var" clauses in the same set as no-ops. For example, given
>
> WHERE a.f1 = b.f2 AND b.f2 = c.f3 AND c.f3 = 42
>
> then after we finish deducing a.f1 = 42 and b.f2 = 42, there is no
> longer any value in either of the original clauses a.f1 = b.f2 and
> b.f2 = c.f3, nor in the other deduced clause a.f1 = c.f3. This would
> take a little bit of restructuring of generate_implied_equalities() and
> process_implied_equality(), but it doesn't seem too difficult to do.
>
> Thoughts? Are there any holes in that logic?
The main thing I can think of is being careful when the types are
different (like padding vs no padding in strings). Playing with text and
char() the explain output appears to be resulting in the right thing
for the clauses but I'm not 100% sure.
Given that it only appears to bring across equality conditions and
not the parts of conditions with or, I think you're right in general.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: To use a VIEW or not to use a View.....
@ 2003-01-23 19:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 23+ messages in thread
From: Tom Lane @ 2003-01-23 19:54 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Thu, 23 Jan 2003, Tom Lane wrote:
>> What I was thinking was that any time the code sees a "var = const"
>> clause as part of a mergejoin equivalence set, we could mark all the
>> "var = var" clauses in the same set as no-ops. For example, given
>> WHERE a.f1 = b.f2 AND b.f2 = c.f3 AND c.f3 = 42
>> then after we finish deducing a.f1 = 42 and b.f2 = 42, there is no
>> longer any value in either of the original clauses a.f1 = b.f2 and
>> b.f2 = c.f3, nor in the other deduced clause a.f1 = c.f3. This would
>> take a little bit of restructuring of generate_implied_equalities() and
>> process_implied_equality(), but it doesn't seem too difficult to do.
>>
>> Thoughts? Are there any holes in that logic?
> The main thing I can think of is being careful when the types are
> different (like padding vs no padding in strings).
This is a matter of being careful about marking cross-datatype operators
as mergejoinable. We do not mark 'bpchar = text' as mergejoinable ---
in fact we don't even have such an operator. AFAICS any pitfalls in
those semantics come up already from the existing logic to treat
mergejoinable equality as transitive for variables. Extending that
transitivity to constants can't create problems that wouldn't exist
anyway.
For reference, these are the only cross-datatype mergejoinable operators
as of CVS tip:
regression=# select oid::regoperator,oprcode from pg_operator where oprlsortop!=0 and oprleft!=oprright;
oid | oprcode
--------------------------+-----------
=(integer,bigint) | int48eq
=(bigint,integer) | int84eq
=(smallint,integer) | int24eq
=(integer,smallint) | int42eq
=(real,double precision) | float48eq
=(double precision,real) | float84eq
=(smallint,bigint) | int28eq
=(bigint,smallint) | int82eq
(8 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2003-01-23 19:54 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-22 15:12 To use a VIEW or not to use a View..... Ries van Twisk <ries@jongert.nl>
2003-01-22 15:46 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-23 08:08 ` Ries van Twisk <ries@jongert.nl>
2003-01-23 10:49 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-22 15:46 ` Jan Wieck <JanWieck@Yahoo.com>
2003-01-22 16:20 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-22 17:09 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-22 18:38 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-22 18:55 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-22 21:09 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-22 21:54 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-23 00:52 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-22 21:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-23 00:45 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 01:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-23 16:53 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 17:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-23 18:21 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-23 19:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-23 17:46 ` jasiek@klaster.net
2003-01-22 17:30 ` Jan Wieck <JanWieck@Yahoo.com>
2003-01-22 15:48 ` Bruno Wolff III <bruno@wolff.to>
2003-01-22 16:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox