Обсуждение: One 7.3 item left
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.
---------------------------------------------------------------------------
P O S T G R E S Q L
7 . 3 O P E N I T E M S
Current at ftp://momjian.postgresql.org/pub/postgresql/open_items.
Required Changes
-------------------
Schema handling - ready? interfaces? client apps?
Drop column handling - ready for all clients, apps?
Fix pg_dump to handle 64-bit off_t offsets for custom format (Philip)
Optional Changes
----------------
Documentation Changes
---------------------
-- 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,
Pennsylvania19073
> 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, Pennsylvania19073
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, Pennsylvania19073
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 P O S T G R E S Q L 7 . 3 O P E N I T E M S Current at ftp://momjian.postgresql.org/pub/postgresql/open_items. Required Changes ------------------- Schema handling - ready? interfaces? client apps? Drop column handling - ready for all clients, apps? Add configure check for sizeof(off_t) > sizeof(long) and no fseek() Fix Linux + Perl 5.8.1 + _GNU_SOURCE problem Fix AIX + Large File + Flex problem Optional Changes ---------------- Documentation Changes ---------------------
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, Pennsylvania19073
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_masterAS 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
BYx DESC;
On my good old P166 it takes
root@actionscouts:/tmp# time psql action < c.sqlid | code | x
-----+------------+----301 | Schladming | 16204 | 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.sqlid | code | x
-----+------------+----301 | Schladming | 16204 | 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.33rows=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_suchidon
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.55rows=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_suchidon
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>, cluster.postgresql.at
<http://cluster.postgresql.at>, www.cybertec.at
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>
Hans-Jürgen Schönig <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.02rows=5000 width=8) -> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4) -> Index Scan
usingidx_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.00rows=5000 width=8) -> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4) -> Index Scan
usingidx_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.00rows=5000 width=8) -> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4) -> Index Scan
usingidx_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.00rows=5000 width=8) -> Seq Scan on t_werttyp (cost=0.00..20.00 rows=1000 width=4) -> Index Scan
usingidx_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