One 7.3 item left

Schema handling - ready? interfaces? client apps?

What is the state of the Perl interface?

Will it work when 7.3 is released
Will it work, but no schema support
Will it pop up later on CPAN

--
Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
Kaki Data tshirts, merchandize Fax: 3816 2501
Howitzvej 75 �ben 12.00-18.00 Email: kar@kakidata.dk
2000 Frederiksberg L�rdag 12.00-16.00 Web: www.suse.dk

Kaare Rasmussen wrote:

Schema handling - ready? interfaces? client apps?

What is the state of the Perl interface?

Will it work when 7.3 is released
Will it work, but no schema support
Will it pop up later on CPAN

We have a separate gborg project for the old perl5 in interface and
dbd-pg. The DBD group is making improvements right now. Not sure how
it works with 7.3 but I am sure they will get to testing it soon. David
Wheeler is working on it, and he is involved in 7.3.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian writes:

OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
We had discussion today on this so it should be completed shortly.

I hate to spoil the fun, but we have at least the Linux + Perl
5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:

Bruce Momjian writes:

OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
We had discussion today on this so it should be completed shortly.

I hate to spoil the fun, but we have at least the Linux + Perl
5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.

We should not, however, wait longer before pushing out a beta3 release.
Portability problems on individual platforms may hold up RC1, but we're
overdue to put out a final beta...

regards, tom lane

On Tue, 22 Oct 2002, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Bruce Momjian writes:

OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
We had discussion today on this so it should be completed shortly.

I hate to spoil the fun, but we have at least the Linux + Perl
5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.

We should not, however, wait longer before pushing out a beta3 release.
Portability problems on individual platforms may hold up RC1, but we're
overdue to put out a final beta...

Was just about to ask that ... Friday sound reasonable for beta3 then?
Bruce, can you have all your files updated by then?

Marc G. Fournier wrote:

On Tue, 22 Oct 2002, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Bruce Momjian writes:

OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
We had discussion today on this so it should be completed shortly.

I hate to spoil the fun, but we have at least the Linux + Perl
5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.

We should not, however, wait longer before pushing out a beta3 release.
Portability problems on individual platforms may hold up RC1, but we're
overdue to put out a final beta...

Was just about to ask that ... Friday sound reasonable for beta3 then?
Bruce, can you have all your files updated by then?

I can, sure.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Tue, Oct 22, 2002 at 19:01:20 +0200,
Kaare Rasmussen <kar@kakidata.dk> wrote:

Schema handling - ready? interfaces? client apps?

What is the state of the Perl interface?

Will it work when 7.3 is released
Will it work, but no schema support
Will it pop up later on CPAN

I am using Pg with 7.3b1 and it works OK for what I am doing. I am not
explicitly naming schemas when referencing objects though.

Peter Eisentraut wrote:

Bruce Momjian writes:

OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
We had discussion today on this so it should be completed shortly.

I hate to spoil the fun, but we have at least the Linux + Perl
5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.

I was hoping those had gone away. :-(

Open items updated.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Marc G. Fournier wrote:

On Tue, 22 Oct 2002, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Bruce Momjian writes:

OK, we are down to one open item, related to pg_dumping on 64-bit off_t.
We had discussion today on this so it should be completed shortly.

I hate to spoil the fun, but we have at least the Linux + Perl
5.8.1 + _GNU_SOURCE and the AIX + Large File + Flex problems to fix.

We should not, however, wait longer before pushing out a beta3 release.
Portability problems on individual platforms may hold up RC1, but we're
overdue to put out a final beta...

Was just about to ask that ... Friday sound reasonable for beta3 then?
Bruce, can you have all your files updated by then?

Done.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Marc G. Fournier wrote:

Was just about to ask that ... Friday sound reasonable for beta3 then?
Bruce, can you have all your files updated by then?

Done.

Are we going to back-merge CVS tip into the REL7_3_STABLE branch now?
What about opening CVS tip for 7.4 development?

regards, tom lane

On Wed, 23 Oct 2002, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Marc G. Fournier wrote:

Was just about to ask that ... Friday sound reasonable for beta3 then?
Bruce, can you have all your files updated by then?

Done.

Are we going to back-merge CVS tip into the REL7_3_STABLE branch now?
What about opening CVS tip for 7.4 development?

considering the number of changes that have lead up to beta3, I'd saw wait
... I'm almost thinking that rc1 makes more sense to do it, since once rc1
goes out, then we're at the point of "only critical changes", which means
alot less commits then what we see through beta ...

I came across a quite interesting issue I don't really understand but
maybe Tom will know.
This happened rather accidentally.

I have a rather complex query which executes efficiently.

There is one interesting thing - let's have a look at the query:

SELECT t_struktur.id, t_text.code, COUNT(t_wert.wert) AS x
FROM t_struktur JOIN t_sportstruktur
ON (t_struktur.id = t_sportstruktur.strukturid),
t_text, t_master, t_strukturtyp,
t_master AS a JOIN t_struktur AS b
ON (a.slave_id = b.id) JOIN t_strukturtyp AS c
ON (b.typid = c.id),
t_wert JOIN t_werttyp
ON (t_werttyp.id = t_wert.werttypid)
WHERE t_struktur.id = t_text.suchid
AND t_text.sprache = 1
AND t_text.texttyp IS NULL
AND t_text.icode = 'struktur'

AND t_master.master_id IN (11, 6, 10, 9, 5, 3, 7, 8, 13)
AND t_master.slave_id = t_struktur.id
AND t_struktur.typid = t_strukturtyp.id
AND t_strukturtyp.kommentar = 'geoort'

AND a.master_id = t_struktur.id
AND c.sortierung = '60005'

AND t_sportstruktur.sportid IN (1, 2, 3, 4, 5)
AND t_struktur.id = t_wert.strukturid
AND t_werttyp.id = t_wert.werttypid
AND t_werttyp.anzeige IN (40550, 40555, 40525,
41070, 41073, 41075, 41077, 41080,
40745, 40750, 40775, 40735, 40780,
40785, 40760, 40710, 41110, 41115,
41090, 41120, 40265, 41085, 41030,
41570, 41550)
AND (t_wert.wert > '0' OR t_wert.wert = 't')
GROUP BY t_struktur.id, t_text.code
ORDER BY x DESC;

On my good old P166 it takes

root@actionscouts:/tmp# time psql action < c.sql
id | code | x
-----+------------+----
301 | Schladming | 16
204 | Kitzb�hel | 8
(2 rows)

real 0m1.475s
user 0m0.050s
sys 0m0.010s

It takes around 5 seconds to execute the query without explicit joins
(brief comment to the discussion we had yesterday).

As you can see the query is redundant:

t_wert JOIN t_werttyp
ON (t_werttyp.id = t_wert.werttypid)

I also use:

AND t_werttyp.id = t_wert.werttypid

I have done with join twice since I have forgotten to remove the line
below when tweaking the stuff.

However, when I remove this AND the query is logically the same but ...

root@actionscouts:/tmp# time psql action < c.sql
id | code | x
-----+------------+----
301 | Schladming | 16
204 | Kitzb�hel | 8
(2 rows)

real 0m2.280s
user 0m0.060s
sys 0m0.010s

It is 50% slower ...
Does anybody have an idea why?

Here are the execution plans - the first one uses the redundant query;
the second one does not use the AND in the WHERE clause.

root@actionscouts:/tmp# time psql action < c.sql
NOTICE: QUERY PLAN:

Sort (cost=425.34..425.34 rows=1 width=132)
-> Aggregate (cost=425.32..425.33 rows=1 width=132)
-> Group (cost=425.32..425.33 rows=1 width=132)
-> Sort (cost=425.32..425.32 rows=1 width=132)
-> Nested Loop (cost=240.47..425.31 rows=1 width=132)
-> Nested Loop (cost=240.47..415.76 rows=1
width=124)
-> Hash Join (cost=240.47..399.06
rows=1 width=101)
-> Nested Loop
(cost=0.00..154.76 rows=765 width=29)
-> Seq Scan on t_werttyp
(cost=0.00..14.69 rows=23 width=8)
-> Index Scan using
idx_wert_werttypid on t_wert (cost=0.00..5.98 rows=1 width=21)
-> Hash (cost=240.47..240.47
rows=1 width=72)
-> Hash Join
(cost=114.57..240.47 rows=1 width=72)
-> Hash Join
(cost=22.45..148.23 rows=24 width=40)
-> Hash Join
(cost=18.82..128.85 rows=3091 width=32)
-> Seq
Scan on t_master a (cost=0.00..55.59 rows=3159 width=16)
-> Hash
(cost=16.66..16.66 rows=866 width=16)
->
Seq Scan on t_struktur b (cost=0.00..16.66 rows=866 width=16)
-> Hash
(cost=3.62..3.62 rows=1 width=8)
-> Seq
Scan on t_strukturtyp c (cost=0.00..3.62 rows=1 width=8)
-> Hash
(cost=92.11..92.11 rows=3 width=32)
-> Hash Join
(cost=41.12..92.11 rows=3 width=32)
-> Hash
Join (cost=37.49..86.40 rows=273 width=24)
->
Seq Scan on t_sportstruktur (cost=0.00..44.13 rows=273 width=8)
->
Hash (cost=16.66..16.66 rows=866 width=16)

-> Seq Scan on t_struktur (cost=0.00..16.66 rows=866 width=16)
-> Hash
(cost=3.62..3.62 rows=1 width=8)
->
Seq Scan on t_strukturtyp (cost=0.00..3.62 rows=1 width=8)
-> Index Scan using idx_text_suchid on
t_text (cost=0.00..16.68 rows=1 width=23)
-> Index Scan using idx_master_slaveid on
t_master (cost=0.00..9.54 rows=1 width=8)

EXPLAIN

real 0m0.616s
user 0m0.050s
sys 0m0.010s

oot@actionscouts:/tmp# time psql action < c.sql
NOTICE: QUERY PLAN:

Sort (cost=824.56..824.56 rows=1 width=132)
-> Aggregate (cost=824.55..824.55 rows=1 width=132)
-> Group (cost=824.55..824.55 rows=1 width=132)
-> Sort (cost=824.55..824.55 rows=1 width=132)
-> Nested Loop (cost=255.22..824.54 rows=1 width=132)
-> Nested Loop (cost=255.22..814.98 rows=1
width=124)
-> Hash Join (cost=255.22..798.28
rows=1 width=101)
-> Hash Join (cost=14.75..553.98
rows=765 width=29)
-> Seq Scan on t_wert
(cost=0.00..501.03 rows=5729 width=21)
-> Hash (cost=14.69..14.69
rows=23 width=8)
-> Seq Scan on
t_werttyp (cost=0.00..14.69 rows=23 width=8)
-> Hash (cost=240.47..240.47
rows=1 width=72)
-> Hash Join
(cost=114.57..240.47 rows=1 width=72)
-> Hash Join
(cost=22.45..148.23 rows=24 width=40)
-> Hash Join
(cost=18.82..128.85 rows=3091 width=32)
-> Seq
Scan on t_master a (cost=0.00..55.59 rows=3159 width=16)
-> Hash
(cost=16.66..16.66 rows=866 width=16)
->
Seq Scan on t_struktur b (cost=0.00..16.66 rows=866 width=16)
-> Hash
(cost=3.62..3.62 rows=1 width=8)
-> Seq
Scan on t_strukturtyp c (cost=0.00..3.62 rows=1 width=8)
-> Hash
(cost=92.11..92.11 rows=3 width=32)
-> Hash Join
(cost=41.12..92.11 rows=3 width=32)
-> Hash
Join (cost=37.49..86.40 rows=273 width=24)
->
Seq Scan on t_sportstruktur (cost=0.00..44.13 rows=273 width=8)
->
Hash (cost=16.66..16.66 rows=866 width=16)

-> Seq Scan on t_struktur (cost=0.00..16.66 rows=866 width=16)
-> Hash
(cost=3.62..3.62 rows=1 width=8)
->
Seq Scan on t_strukturtyp (cost=0.00..3.62 rows=1 width=8)
-> Index Scan using idx_text_suchid on
t_text (cost=0.00..16.68 rows=1 width=23)
-> Index Scan using idx_master_slaveid on
t_master (cost=0.00..9.54 rows=1 width=8)

EXPLAIN

real 0m0.659s
user 0m0.040s
sys 0m0.030s

The execution plans are slightly different.
Is it "normal"?

Also: My third PostgreSQL book is ready. It is in German - does anybody
of those PostgreSQL hackers out there want a free issue?

Hans

--
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at&gt;, cluster.postgresql.at
<http://cluster.postgresql.at&gt;, www.cybertec.at
<http://www.cybertec.at&gt;, kernel.cybertec.at <http://kernel.cybertec.at&gt;

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:

I came across a quite interesting issue I don't really understand but
maybe Tom will know.

Interesting. We seem to recognize the fact that the extra clause is
redundant in nearly all places ... but not in indexscan plan generation.

I tried this simplified test case:

create table t_wert(werttypid int);
create table t_werttyp(id int);
create index idx_wert_werttypid on t_wert(werttypid);

explain select * from
t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
where t_werttyp.id = t_wert.werttypid;

explain select * from
t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);

I got identical merge-join plans and row count estimates both ways.
I then turned off enable_mergejoin, and got identical hash-join plans
and row counts. But with enable_hashjoin also off:

regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
regression-# where t_werttyp.id = t_wert.werttypid;
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..4858.02 rows=5000 width=8)
-> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4)
-> Index Scan using idx_wert_werttypid on t_wert (cost=0.00..4.83 rows=1 width=4)
Index Cond: (("outer".id = t_wert.werttypid) AND ("outer".id = t_wert.werttypid))
(4 rows)

regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);
QUERY PLAN
----------------------------------------------------------------------------------------
Nested Loop (cost=0.00..17150.00 rows=5000 width=8)
-> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4)
-> Index Scan using idx_wert_werttypid on t_wert (cost=0.00..17.07 rows=5 width=4)
Index Cond: ("outer".id = t_wert.werttypid)
(4 rows)

Looks like a bug is lurking someplace ...

regards, tom lane

I wrote:

Interesting. We seem to recognize the fact that the extra clause is
redundant in nearly all places ... but not in indexscan plan generation.

I tried this simplified test case:

create table t_wert(werttypid int);
create table t_werttyp(id int);
create index idx_wert_werttypid on t_wert(werttypid);

explain select * from
t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
where t_werttyp.id = t_wert.werttypid;

explain select * from
t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);

FYI, I have committed changes that seem to fix this problem in CVS tip.

regression=# set enable_mergejoin to 0;
SET
regression=# set enable_hashjoin to 0;
SET
regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid)
regression-# where t_werttyp.id = t_wert.werttypid;
QUERY PLAN
----------------------------------------------------------------------------------------
Nested Loop (cost=0.00..17150.00 rows=5000 width=8)
-> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4)
-> Index Scan using idx_wert_werttypid on t_wert (cost=0.00..17.07 rows=5 width=4)
Index Cond: ("outer".id = t_wert.werttypid)
(4 rows)

regression=# explain select * from
regression-# t_wert JOIN t_werttyp ON (t_werttyp.id = t_wert.werttypid);
QUERY PLAN
----------------------------------------------------------------------------------------
Nested Loop (cost=0.00..17150.00 rows=5000 width=8)
-> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4)
-> Index Scan using idx_wert_werttypid on t_wert (cost=0.00..17.07 rows=5 width=4)
Index Cond: ("outer".id = t_wert.werttypid)
(4 rows)

regards, tom lane