[SQL] optimal sql

[Michael Hostbaek]

Hi,

I am running postgresql 7.2.3 on a test server (with potential of
becoming my production server).

On the server I have a perl script, that is grabbing some data from a
inventory database (local) - with some subselects.
The query is like this:


my $sth = $ppdb->prepare("
select partno, create_date, mfg, condition, gescode, qty,
cmup,(SELECT partno_main FROM partno_lookup where 
partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and mfg
ilike ? limit 1) 
as partno_main, (SELECT subcat FROM partno_lookup where
partno_lookup.partno_alias ilike 
(?|| inventory.partno ||?) and mfg ilike ? limit 1) as subcat, 
(SELECT key_search FROM partno_lookup where
partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and 
 mfg ilike ? limit 1) as key_search,
(SELECT text_desc FROM descriptions where
descriptions.partno=(SELECT partno_main FROM partno_lookup 
where partno_lookup.partno_alias ilike (?|| inventory.partno ||?)
and mfg ilike ? limit 1) 
 limit 1) as descri from inventory where mfg ilike ? and ? <
create_date $refurbed order by key_search,
subcat, partno_main, status DESC ");


It takes quite a while for the query to get processed - and the script
to return my values.
The inventory table has approx. 23000 records - and the partno_lookup
has approx. 1100.

Is there anyway I can optimize the sql - og perhaps optimize my
postgresql db settings ? ( I am running my postgresql on FreeBSD, on a
fairly adequite machine with 1GB RAM)

I look forward to any pointers or hints you might have.

Thanks.

/mich

-- 
Best Regards,
Michael Landin Hostbaek 
FreeBSDCluster.org - an International Community

*/ PGP-key available upon request /*

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

http://archives.postgresql.org

[SQL] NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index

[David Durst]

Can anyone tell me why postgres is creating a implicit index when
I already have a PKEY specified

Or am I just interpreting this all wrong?

Here is the entry I am putting in:

create sequence journal_line_id_seq increment 1 start 1;

create table journal_lines (
  journal_line_id int4 PRIMARY KEY DEFAULT NEXTVAL('journal_line_id_seq'),
  entry_id int4,
  account_id int4,
  line_type int2 CHECK (line_type >= 1 AND line_type <= 2),
  line_amount money,
  CONSTRAINT eid FOREIGN KEY(entry_id) REFERENCES journal(entry_id),
  CONSTRAINT aid FOREIGN KEY(account_id) REFERENCES accounts(account_id)
);

Here is the notice postgres spits out:
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'journal_lines_pkey' for table 'journal_lines'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE




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

http://archives.postgresql.org

Re: [SQL] optimal sql

[Achilleus Mantzios]

On Wed, 22 Jan 2003, Michael Hostbaek wrote:

> Hi,

I would suggest looking at the problem in three directions:

a) PostgreSQL system wise
b) PostgreSQL sql wise
c) FreeBSD wise.

For a) do all the necessary tuning on PostgreSQL.
With 1GB of Mem, you could set a value of shared_buffers to 10.
Also check the fsync setting.
Minimising logging may be a good idea.
Read the docs on the site.

For b) do explain analyze to be sure you have the right index
usage, or create indexes where appropriate.
Check the statitistics of your tables, distributions,
counts etc...

For c) check all kern.ipc.shm* and kern.ipc.sem* kernel variables.
(you will need to set some of those in order to get the desired
shared_buffers in a))
Rebuild a custom kernel fitting your needs. Check
http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/config-tuning.html
Also do man 7 tuning.
And, check
http://www.freebsd.org/doc/en_US.ISO8859-1/books/developers-handbook/index.html

(Look at DMA access in your kernel CONFIG, consider turning on IDE write
caching, etc).

Also during your perl script, a good idea is to have

iostat 3 , vmstat 3, running.
This will give you hints of where your system starves.
If for instance your system cache is small, and CPU usage is small and you
have
a lot of IO, then increase shared_buffers, and tune your disks.
(also do man 8 tunefs)

IF you have nearly ~ 100% CPU usage, then the system may look
healthier but your query not.


>
> I am running postgresql 7.2.3 on a test server (with potential of
> becoming my production server).
>
> On the server I have a perl script, that is grabbing some data from a
> inventory database (local) - with some subselects.
> The query is like this:
>
> 
> my $sth = $ppdb->prepare("
> select partno, create_date, mfg, condition, gescode, qty,
> cmup,(SELECT partno_main FROM partno_lookup where
> partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and mfg
> ilike ? limit 1)
> as partno_main, (SELECT subcat FROM partno_lookup where
> partno_lookup.partno_alias ilike
> (?|| inventory.partno ||?) and mfg ilike ? limit 1) as subcat,
> (SELECT key_search FROM partno_lookup where
> partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and
>  mfg ilike ? limit 1) as key_search,
> (SELECT text_desc FROM descriptions where
> descriptions.partno=(SELECT partno_main FROM partno_lookup
> where partno_lookup.partno_alias ilike (?|| inventory.partno ||?)
> and mfg ilike ? limit 1)
>  limit 1) as descri from inventory where mfg ilike ? and ? <
> create_date $refurbed order by key_search,
> subcat, partno_main, status DESC ");
> 
>
> It takes quite a while for the query to get processed - and the script
> to return my values.
> The inventory table has approx. 23000 records - and the partno_lookup
> has approx. 1100.
>
> Is there anyway I can optimize the sql - og perhaps optimize my
> postgresql db settings ? ( I am running my postgresql on FreeBSD, on a
> fairly adequite machine with 1GB RAM)
>
> I look forward to any pointers or hints you might have.
>
> Thanks.
>
> /mich
>
> --
> Best Regards,
>   Michael Landin Hostbaek
>   FreeBSDCluster.org - an International Community
>
>   */ PGP-key available upon request /*
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email:  [EMAIL PROTECTED]
[EMAIL PROTECTED]


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

Re: [SQL] NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index

[Ludwig Lim]

--- David Durst <[EMAIL PROTECTED]> wrote:
> Can anyone tell me why postgres is creating a
> implicit index when
> I already have a PKEY specified
> 
> Or am I just interpreting this all wrong?

  PostgreSQL uses UNIQUE INDEX to enforce PRIMARY KEY
constraint. Therefore creating a PRIMARY KEY will
automatically create a UNIQUE INDEX.

ludwig.


__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

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

Re: [SQL] optimal sql

[Michael Paesold]

Achilleus Mantzios <[EMAIL PROTECTED]> wrote:

> For a) do all the necessary tuning on PostgreSQL.
> With 1GB of Mem, you could set a value of shared_buffers to 10.

Perhaps just a type, but that is way to much! It would mean about 800 Mb
shared buffers! I would rather suggest a value between 1000 and 1. See
recent descussions on -performance and -hackers mailing lists.

Best Regards,
Michael Paesold

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

http://archives.postgresql.org

Re: [SQL] optimal sql

[Michael Hostbaek]

Tomasz Myrta (jasiek) writes:
> 
> 3. Explain analyze would be helpful like in most performance cases...
> The same with SQL query instead of Perl script.

Explain analyze:


NOTICE:  QUERY PLAN:

Limit  (cost=27.55..27.55 rows=1 width=183) (actual
time=35364.89..35365.04 rows=10 loops=1)
  ->  Sort  (cost=27.55..27.55 rows=1 width=183) (actual
time=35364.87..35364.92 rows=11 loops=1)
->  Group  (cost=27.51..27.54 rows=1 width=183) (actual
time=35350.49..35359.96 rows=411 loops=1)
  ->  Sort  (cost=27.51..27.51 rows=1 width=183) (actual
time=35350.43..35352.52 rows=411 loops=1)
->  Seq Scan on inventory  (cost=0.00..27.50 rows=1
width=183) (actual time=168.52..35342.92 rows=411 loops=1)
  SubPlan
->  Limit  (cost=0.00..30.00 rows=1
width=48) (actual time=4.99..6.14 rows=0 loops=411)
  ->  Seq Scan on partno_lookup
(cost=0.00..30.00 rows=1 width=48) (actual time=4.96..6.11 rows=1
loops=411)
->  Limit  (cost=0.00..30.00 rows=1
width=93) (actual time=4.97..6.13 rows=0 loops=411)
  ->  Seq Scan on partno_lookup
(cost=0.00..30.00 rows=1 width=93) (actual time=4.95..6.10 rows=1
loops=411)
->  Limit  (cost=0.00..4.50 rows=1 width=32)
(actual time=57.94..73.46 rows=0 loops=411)
  InitPlan
->  Limit  (cost=0.00..30.00 rows=1
width=48) (actual time=5.00..6.16 rows=0 loops=411)
  ->  Seq Scan on partno_lookup
(cost=0.00..30.00 rows=1 width=48) (actual time=4.98..6.13 rows=1
loops=411)
  ->  Seq Scan on descriptions
(cost=0.00..22.50 rows=5 width=32) (actual time=57.91..73.43 rows=1
loops=411)
Total runtime: 35365.50 msec

EXPLAIN



explain analyze select partno, create_date, mfg, condition, gescode,
qty, cmup,(SELECT partno_main FROM 
partno_lookup where partno_lookup.partno_alias ilike
'%'||inventory.partno||'%' and mfg ilike 'CISCO' limit 1)
as partno_main, (SELECT subcat FROM partno_lookup where
partno_lookup.partno_alias ilike 
'%'||inventory.partno||'%' and mfg ilike 'CISCO' limit 1) as subcat, 
(SELECT text_desc FROM descriptions where descriptions.partno=(SELECT
partno_main FROM partno_lookup 
where partno_lookup.partno_alias ilike '%'||inventory.partno||'%' and
mfg ilike 'CISCO' limit 1) limit 1) as 
descri from inventory where mfg ilike 'CISCO' and '2003-01-15' <
create_date and condition not like 'REFURB'
group by partno_main, partno, create_date, mfg, condition, gescode, qty,
cmup, subcat, descri, status order by
subcat, partno_main, status DESC limit 10;


Here is a sample of how a partno_lookup record looks like:

ppdb=> select * from partno_lookup where partno_main = 'WIC-2T';
 partno_main |partno_alias |  mfg  |
subcat| key_search 
-+-+---+-+
 WIC-2T  | WIC2TB,WIC-2T,WIC-2T=,WIC2T,WIC2T=,WIC2TREF | CISCO | WIC
MODULES | A
(1 row)

Any help very much appreciated.

/mich

-- 
Best Regards,
Michael Landin Hostbaek 
FreeBSDCluster.org - an International Community

*/ PGP-key available upon request /*

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] optimal sql

[Tomasz Myrta]

Michael Hostbaek wrote:


Tomasz Myrta (jasiek) writes:


3. Explain analyze would be helpful like in most performance cases...
The same with SQL query instead of Perl script.



Explain analyze:


NOTICE:  QUERY PLAN:

Limit  (cost=27.55..27.55 rows=1 width=183) (actual
time=35364.89..35365.04 rows=10 loops=1)
 ->  Sort  (cost=27.55..27.55 rows=1 width=183) (actual
time=35364.87..35364.92 rows=11 loops=1)
   ->  Group  (cost=27.51..27.54 rows=1 width=183) (actual
time=35350.49..35359.96 rows=411 loops=1)
 ->  Sort  (cost=27.51..27.51 rows=1 width=183) (actual
time=35350.43..35352.52 rows=411 loops=1)
   ->  Seq Scan on inventory  (cost=0.00..27.50 rows=1
width=183) (actual time=168.52..35342.92 rows=411 loops=1)
 SubPlan
   ->  Limit  (cost=0.00..30.00 rows=1
width=48) (actual time=4.99..6.14 rows=0 loops=411)
 ->  Seq Scan on partno_lookup
(cost=0.00..30.00 rows=1 width=48) (actual time=4.96..6.11 rows=1
loops=411)
   ->  Limit  (cost=0.00..30.00 rows=1
width=93) (actual time=4.97..6.13 rows=0 loops=411)
 ->  Seq Scan on partno_lookup
(cost=0.00..30.00 rows=1 width=93) (actual time=4.95..6.10 rows=1
loops=411)
   ->  Limit  (cost=0.00..4.50 rows=1 width=32)
(actual time=57.94..73.46 rows=0 loops=411)
 InitPlan
   ->  Limit  (cost=0.00..30.00 rows=1
width=48) (actual time=5.00..6.16 rows=0 loops=411)
 ->  Seq Scan on partno_lookup
(cost=0.00..30.00 rows=1 width=48) (actual time=4.98..6.13 rows=1
loops=411)
 ->  Seq Scan on descriptions
(cost=0.00..22.50 rows=5 width=32) (actual time=57.91..73.43 rows=1
loops=411)
Total runtime: 35365.50 msec

EXPLAIN



explain analyze select partno, create_date, mfg, condition, gescode,
qty, cmup,(SELECT partno_main FROM 
partno_lookup where partno_lookup.partno_alias ilike
'%'||inventory.partno||'%' and mfg ilike 'CISCO' limit 1)
as partno_main, (SELECT subcat FROM partno_lookup where
partno_lookup.partno_alias ilike 
'%'||inventory.partno||'%' and mfg ilike 'CISCO' limit 1) as subcat, 
(SELECT text_desc FROM descriptions where descriptions.partno=(SELECT
partno_main FROM partno_lookup 
where partno_lookup.partno_alias ilike '%'||inventory.partno||'%' and
mfg ilike 'CISCO' limit 1) limit 1) as 
descri from inventory where mfg ilike 'CISCO' and '2003-01-15' <
create_date and condition not like 'REFURB'
group by partno_main, partno, create_date, mfg, condition, gescode, qty,
cmup, subcat, descri, status order by
subcat, partno_main, status DESC limit 10;


Here is a sample of how a partno_lookup record looks like:

Main problem of your query is this:
Seq Scan on inventory  (cost=0.00..27.50 rows=1
width=183) (actual time=168.52..35342.92 rows=411 loops=1)

Do you have to use "ilike" condition in all cases?
Database won't use index on this table at all, which
compared to thousands of records isn't good.

Next problem - your table isn't too normalized...

I don't know, how much have you done to your database,
but I think, you should reorganize it.

Example:
Create table manufacturers
( mfgid integer,
 name varchar (for example "Cisco")
)
In table inventory change field mfg into mfgid.
In table partno_aliases change field mfg into mfgid.

Your query would have something like this:
select ...
from manufacturers M join inventory I using (mfgid)
join partno_aliases PA using (mfgid)
where M.name ilike 'Cisco' and ...

After this create index on inventory(mfgid,createdate)

If you don't want to change anything, 
create at least index on inventory(createdate).
This will speed up queries with recent products - for
not too old createdate.

Regards,
Tomasz Myrta


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

Re: [SQL] optimal sql

[Tomasz Myrta]

Michael Hostbaek wrote:


Hi,

I am running postgresql 7.2.3 on a test server (with potential of
becoming my production server).

On the server I have a perl script, that is grabbing some data from a
inventory database (local) - with some subselects.
The query is like this:


my $sth = $ppdb->prepare("
   select partno, create_date, mfg, condition, gescode, qty,
cmup,(SELECT partno_main FROM partno_lookup where 
   partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and mfg
ilike ? limit 1) 
   as partno_main, (SELECT subcat FROM partno_lookup where
partno_lookup.partno_alias ilike 
   (?|| inventory.partno ||?) and mfg ilike ? limit 1) as subcat, 
   (SELECT key_search FROM partno_lookup where
partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and 
mfg ilike ? limit 1) as key_search,
   (SELECT text_desc FROM descriptions where
descriptions.partno=(SELECT partno_main FROM partno_lookup 
   where partno_lookup.partno_alias ilike (?|| inventory.partno ||?)
and mfg ilike ? limit 1) 
limit 1) as descri from inventory where mfg ilike ? and ? <
create_date $refurbed order by key_search,
   subcat, partno_main, status DESC ");

1. Probably your query  can't use index on table partno_lookup.partno_alias.
Consider creating table aliases which contains all possible parts aliases. 
You can change then "ilike" into "=" which will use indexes.

2. You don't need subselects in your query. You can change them into ordinary
table joins and use "group by" or "distinct on". In your case selecting from
partno_lookup is executed several times per one row.

3. Explain analyze would be helpful like in most performance cases...
The same with SQL query instead of Perl script.

4. This is rather a sql problem, than hardware/configuration one.

Regards,
Tomasz Myrta


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

Re: [SQL] optimal sql

[Achilleus Mantzios]

On Wed, 22 Jan 2003, Michael Paesold wrote:

> Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
>
> > For a) do all the necessary tuning on PostgreSQL.
> > With 1GB of Mem, you could set a value of shared_buffers to 10.
>
> Perhaps just a type, but that is way to much! It would mean about 800 Mb
> shared buffers! I would rather suggest a value between 1000 and 1. See
> recent descussions on -performance and -hackers mailing lists.

Personally i found only performance improvement when increasing
shared_buffers. (but then again i speak for me and my queries).
The 100,000 value was certainly not a typo (provided he doesnt run
X11,KDE, mozilla, etc... on his server) but maybe too high.
Some people say 25% of the total Mem is a good rule of thumb, but
testing for his specific query must be made.


>
> Best Regards,
> Michael Paesold
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email:  [EMAIL PROTECTED]
[EMAIL PROTECTED]


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

http://archives.postgresql.org

[SQL] PostgreSQL + SSL

[Pedro Igor Craveiro e Silva]

I´m trying to config PG with SSL, but i got a 
error. I create the key and the certificate and put both in $PGDATA 
directory.
I also enabled the ssl option in 
postgresql.conf.
But when i run postmaster i got a error saying that 
server.key has wrong permissions.
 
Thanks,
 
Pedro Igor

[SQL] To use a VIEW or not to use a View.....

[Ries van Twisk]

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,
toconnidINTEGER 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,
toshiplocationidINTEGER 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


---(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] To use a VIEW or not to use a View.....

[Bruno Wolff III]

On Wed, Jan 22, 2003 at 16:12:52 +0100,
  Ries van Twisk <[EMAIL PROTECTED]> 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.

---(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] To use a VIEW or not to use a View.....

[Jan Wieck]

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.  #
#== [EMAIL PROTECTED] #

---(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] To use a VIEW or not to use a View.....

[Tomasz Myrta]

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 [EMAIL PROTECTED])

Re: [SQL] To use a VIEW or not to use a View.....

[Stephan Szabo]

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.



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

Re: [SQL] To use a VIEW or not to use a View.....

[Tomasz Myrta]

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 <[EMAIL PROTECTED]> 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



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

http://archives.postgresql.org

Re: [SQL] To use a VIEW or not to use a View.....

[Stephan Szabo]

On Wed, 22 Jan 2003, Tomasz Myrta wrote:

> >> Tomasz Myrta <[EMAIL PROTECTED]> 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.


---(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] To use a VIEW or not to use a View.....

[Jan Wieck]

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.  #
#== [EMAIL PROTECTED] #

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

http://archives.postgresql.org

Re: [SQL] To use a VIEW or not to use a View.....

[Tomasz Myrta]

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


---(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] To use a VIEW or not to use a View.....

[Tomasz Myrta]

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



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

Re: [SQL] Speed depending of Join Order.

[Tom Lane]

=?iso-8859-1?Q?Ra=FAl=20Guti=E9rrez=20S=E1nchez?= <[EMAIL PROTECTED]> writes:
> Note that the only difference is the order of the join elements. Using
> version 7.2.2, which I have been using untill now, the time expended in
> both of them was the same, using the right indexes. However, using
> version 7.3.1 which I have instaled recently, the results of the explain
> are the following:

It turns out that the problem was inaccuracy in some recently-added code
that tries to account for the possibility that a mergejoin won't scan
all the way to the end.  Your sample data had only one possible value
for the mis_id and mod_mis_id columns; this boundary case exposed the
fact that the code was testing for "x < max" where it should be testing
"x <= max".  Coupled with a lack of sanity-checking, the bogus
calculation affected the estimated costs in an asymmetrical way.  This
is why the choice of a bad plan only occurred in one case out of two.

I've applied the attached patch for 7.3.2.  Thanks for the report!

regards, tom lane


*** src/backend/optimizer/path/costsize.c.orig  Wed Sep  4 16:31:20 2002
--- src/backend/optimizer/path/costsize.c   Wed Jan 22 15:10:20 2003
***
*** 645,652 
innerscansel = firstclause->left_mergescansel;
}
  
!   outer_rows = outer_path->parent->rows * outerscansel;
!   inner_rows = inner_path->parent->rows * innerscansel;
  
/* cost of source data */
  
--- 645,666 
innerscansel = firstclause->left_mergescansel;
}
  
!   /* convert selectivity to row count; must scan at least one row */
! 
!   outer_rows = ceil(outer_path->parent->rows * outerscansel);
!   if (outer_rows < 1)
!   outer_rows = 1;
!   inner_rows = ceil(inner_path->parent->rows * innerscansel);
!   if (inner_rows < 1)
!   inner_rows = 1;
! 
!   /*
!* Readjust scan selectivities to account for above rounding.  This is
!* normally an insignificant effect, but when there are only a few rows
!* in the inputs, failing to do this makes for a large percentage error.
!*/
!   outerscansel = outer_rows / outer_path->parent->rows;
!   innerscansel = inner_rows / inner_path->parent->rows;
  
/* cost of source data */
  
*** src/backend/utils/adt/selfuncs.c.orig   Fri Oct 18 22:56:16 2002
--- src/backend/utils/adt/selfuncs.cWed Jan 22 15:12:05 2003
***
*** 1740,1746 
rsortop,
ltop,
gtop,
!   revltop;
Datum   leftmax,
rightmax;
double  selec;
--- 1740,1748 
rsortop,
ltop,
gtop,
!   leop,
!   revgtop,
!   revleop;
Datum   leftmax,
rightmax;
double  selec;
***
*** 1779,1813 
/* Look up the "left < right" and "left > right" operators */
op_mergejoin_crossops(opno, op, NULL, NULL);
  
!   /* Look up the "right < left" operator */
!   revltop = get_commutator(gtop);
!   if (!OidIsValid(revltop))
!   return; /* shouldn't happen */
  
/*
 * Now, the fraction of the left variable that will be scanned is the
 * fraction that's <= the right-side maximum value.  But only believe
 * non-default estimates, else stick with our 1.0.
 */
!   selec = scalarineqsel(root, ltop, false, left,
  rightmax, right->vartype);
if (selec != DEFAULT_INEQ_SEL)
*leftscan = selec;
  
/* And similarly for the right variable. */
!   selec = scalarineqsel(root, revltop, false, right,
  leftmax, left->vartype);
if (selec != DEFAULT_INEQ_SEL)
*rightscan = selec;
  
/*
 * Only one of the two fractions can really be less than 1.0; believe
!* the smaller estimate and reset the other one to exactly 1.0.
 */
if (*leftscan > *rightscan)
*leftscan = 1.0;
!   else
*rightscan = 1.0;
  }
  
  /*
--- 1781,1829 
/* Look up the "left < right" and "left > right" operators */
op_mergejoin_crossops(opno, op, NULL, NULL);
  
!   /* Look up the "left <= right" operator */
!   leop = get_negator(gtop);
!   if (!OidIsValid(leop))
!   return; /* insufficient info in 
catalogs */
! 
!   /* Look up the "right > left" operator */
!   revgtop = get_commutator(ltop);
!   if (!OidIsValid

Re: [SQL] To use a VIEW or not to use a View.....

[Stephan Szabo]

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.



---(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] To use a VIEW or not to use a View.....

[Tom Lane]

Stephan Szabo <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] To use a VIEW or not to use a View.....

[Tomasz Myrta]

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





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

http://archives.postgresql.org

Re: [SQL] To use a VIEW or not to use a View.....

[Stephan Szabo]

On Wed, 22 Jan 2003, Tom Lane wrote:

> Stephan Szabo <[EMAIL PROTECTED]> 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. :)



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

Re: [SQL] To use a VIEW or not to use a View.....

[Stephan Szabo]

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.


---(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] To use a VIEW or not to use a View.....

[Tom Lane]

Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Wed, 22 Jan 2003, Tom Lane wrote:
>> Stephan Szabo <[EMAIL PROTECTED]> 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

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

[SQL] DBCC CheckIdent in a stored proc?

[smoghe]

Hello All,

 

I need to know the last id of the last record added to the
table from an ASP page. 

 

How do I package DBCC CheckIdent(@tableName) in a output 
parameter?

 

Thanx!

Shrirang

 

p.s: I am a total newbie to DB.