[PERFORM] PostgreSQL is extremely slow on Windows

[Vig, Sandor (G/FI-2)]

Hi,

I've downloaded the latest release (PostgreSQL 8.0) for windows.
Installation was OK, but I have tried to restore a database.
It had more than ~100.000 records. Usually I use PostgreSQL
under Linux, and it used to be done under 10 minutes.

Under W2k und XP it took 3 hours(!) Why is it so slow

The commands I used:

Under Linux: (duration: 1 minute)
pg_dump -D databasename > databasename.db

Under Windows: (duration: 3 - 3.5 hours(!))
psql databasename < databasename.db >nul

It seemed to me, that only 20-30 transactions/sec were
writen to the database.

I need to write scripts for automatic (sheduled) database
backup and recovery.

Help anyone?

Bye,

Vig Sándor


The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.

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

Re: [PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function

[amrit]

> > I used you perl script and found the error =>
> > [EMAIL PROTECTED] tmp]# perl relacl.pl
> > DBI connect('dbname=template1;port=5432','postgres',...) failed: FATAL:
> IDENT
> > authentication failed for user "postgres" at relacl.pl line 21
> > Error in connect to DBI:Pg:dbname=template1;port=5432:
> >
> >
> Excellent - we know what is going on now!
>
>
> > And my pg_hba.conf is
> >
> > # IPv4-style local connections:
> > hostall all 127.0.0.1 255.255.255.255   trust
> > hostall all 192.168.0.0 255.255.0.0 trust
> >
> > trusted for every user.
>
> Ok, what I think has happened is that there is another Pg installation
> (or another initdb'ed cluster) on this machine that you are accidentally
> talking to. Try
>
> $ rpm -qa|grep -i postgres
>
> which will spot another software installation, you may just have to
> search for files called pg_hba.conf to find another initdb'ed cluster
>
> This other installation should have a pg_hba.conf that looks something
> like :
>
> local   all allident
> hostall all   127.0.0.1  255.255.255.255   ident
>
> So a bit of detective work is in order :-)
>
> Mark
After being a detector I found that
[EMAIL PROTECTED] ~]# rpm -qa|grep -i postgres
postgresql-7.4.5-3.1.tlc
postgresql-python-7.4.5-3.1.tlc
postgresql-jdbc-7.4.5-3.1.tlc
postgresql-tcl-7.4.5-3.1.tlc
postgresql-server-7.4.5-3.1.tlc
postgresql-libs-7.4.5-3.1.tlc
postgresql-docs-7.4.5-3.1.tlc
postgresql-odbc-7.3-8.1.tlc
postgresql-pl-7.4.5-3.1.tlc
postgresql-test-7.4.5-3.1.tlc
postgresql-contrib-7.4.5-3.1.tlc
[EMAIL PROTECTED] ~]#

no other pg installation except the pgsql for windows in samba folder which I
think it isn't matter ,is it?
No other  pg being run.
[EMAIL PROTECTED] ~]# ps ax|grep postmaster
 2228 ?S  0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
 3308 pts/0S+ 0:00 grep postmaster
[EMAIL PROTECTED] ~]#

Is it possible that it is related to pg_ident.conf ?

Any comment please.
Amrit,Thailand




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

Re: [PERFORM] PostgreSQL is extremely slow on Windows

[Mitch Pirtle]

On Tue, 22 Feb 2005 16:00:59 +0100, Vig, Sandor (G/FI-2)
<[EMAIL PROTECTED]> wrote:
> 
> 
> Hi,
> 
> I've downloaded the latest release (PostgreSQL 8.0) for windows.
> Installation was OK, but I have tried to restore a database.
> It had more than ~100.000 records. Usually I use PostgreSQL
> under Linux, and it used to be done under 10 minutes.
> 
> Under W2k und XP it took 3 hours(!) Why is it so slow

Can you tell us your postgresql.conf configuration settings? We cannot
help without some information about your environment...

-- Mitch

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

   http://www.postgresql.org/docs/faq

Re: [PERFORM] PostgreSQL is extremely slow on Windows

[Magnus Hagander]

>I've downloaded the latest release (PostgreSQL 8.0) for windows.
>Installation was OK, but I have tried to restore a database.
>It had more than ~100.000 records. Usually I use PostgreSQL
>under Linux, and it used to be done under 10 minutes.
>
>Under W2k und XP it took 3 hours(!) Why is it so slow
>
>The commands I used:
>
>Under Linux: (duration: 1 minute)
>   pg_dump -D databasename > databasename.db
>
>Under Windows: (duration: 3 - 3.5 hours(!))
>   psql databasename < databasename.db >nul
>
>It seemed to me, that only 20-30 transactions/sec were
>writen to the database.

20-30 transactionsi s about what you'll get on a single disk on Windows
today.
We have a patch in testing that will bring this up to about 80.
You can *never* get above 80 without using write cache, regardless of
your OS, if you have a single disk. You might want to look into wether
write cacheing is enabled on your linux box, and disable it. (unless you
are using RAID) A lot points towards write cache enabled on your system.

If you need the performance that equals the one with write cache on, you
can set fsync=off. But then you will lose the guarantee that your
machine will survive an unclean shutdown or crash. I would strongly
advice against it on a production system - same goes for running with
write cache!

//Magnus

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[PERFORM] is pg_autovacuum so effective ?

[Gaetano Mendola]

Hi all,
I'm running since one week without use any vacuum full,
I'm using ony pg_autovacuum. I expect that disk usage will reach
a steady state but is not. PG engine: 7.4.5

Example:

The message table is touched by pg_autvacuum at least 2 time a day:

$ cat pg_autovacuum.log  | grep VACUUM | grep messages
[2005-02-15 16:41:00 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-16 03:31:47 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-16 12:44:18 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-16 23:26:09 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-17 09:25:41 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-17 19:57:11 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-18 05:38:46 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-18 14:28:55 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-19 02:22:20 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-19 13:43:02 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-20 02:05:40 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-20 14:06:33 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-20 23:54:32 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-21 08:57:20 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-21 19:24:53 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-22 05:25:03 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-22 15:20:39 CET] Performing: VACUUM ANALYZE "public"."messages"



this is what gave me the vacuum full on that table:


# vacuum full verbose messages;
INFO:  vacuuming "public.messages"
INFO:  "messages": found 77447 removable, 1606437 nonremovable row versions in 
69504 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 97 to 2033 bytes long.
There were 633541 unused item pointers.
Total free space (including removable row versions) is 52819600 bytes.
1690 pages are or will become empty, including 0 at the end of the table.
22217 pages containing 51144248 free bytes are potential move destinations.
CPU 2.39s/0.55u sec elapsed 31.90 sec.
INFO:  index "idx_type_message" now contains 1606437 row versions in 7337 pages
DETAIL:  77447 index row versions were removed.
446 index pages have been deleted, 446 are currently reusable.
CPU 0.33s/0.75u sec elapsed 16.56 sec.
INFO:  index "messages_pkey" now contains 1606437 row versions in 5628 pages
DETAIL:  77447 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.15s/0.80u sec elapsed 4.22 sec.
INFO:  index "idx_service_message" now contains 1606437 row versions in 6867 
pages
DETAIL:  77447 index row versions were removed.
499 index pages have been deleted, 499 are currently reusable.
CPU 0.67s/0.99u sec elapsed 8.85 sec.
INFO:  index "idx_service_message_expired" now contains 135313 row versions in 
3308 pages
DETAIL:  77375 index row versions were removed.
512 index pages have been deleted, 512 are currently reusable.
CPU 0.21s/0.32u sec elapsed 6.88 sec.
INFO:  index "idx_expired_messages" now contains 1606437 row versions in 7070 
pages
DETAIL:  77447 index row versions were removed.
448 index pages have been deleted, 448 are currently reusable.
CPU 0.34s/1.10u sec elapsed 29.77 sec.
INFO:  index "idx_messages_target" now contains 1606437 row versions in 14480 
pages
DETAIL:  77447 index row versions were removed.
643 index pages have been deleted, 643 are currently reusable.
CPU 0.84s/1.61u sec elapsed 25.72 sec.
INFO:  index "idx_messages_source" now contains 1606437 row versions in 10635 
pages
DETAIL:  77447 index row versions were removed.
190 index pages have been deleted, 190 are currently reusable.
CPU 0.68s/1.04u sec elapsed 31.96 sec.
INFO:  "messages": moved 55221 row versions, truncated 69504 to 63307 pages
DETAIL:  CPU 5.46s/25.14u sec elapsed 280.20 sec.
INFO:  index "idx_type_message" now contains 1606437 row versions in 7337 pages
DETAIL:  55221 index row versions were removed.
2304 index pages have been deleted, 2304 are currently reusable.
CPU 0.42s/0.49u sec elapsed 53.35 sec.
INFO:  index "messages_pkey" now contains 1606437 row versions in 5628 pages
DETAIL:  55221 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.31s/0.34u sec elapsed 13.27 sec.
INFO:  index "idx_service_message" now contains 1606437 row versions in 6867 
pages
DETAIL:  55221 index row versions were removed.
2024 index pages have been deleted, 2024 are currently reusable.
CPU 0.51s/0.57u sec elapsed 16.60 sec.
INFO:  index "idx_service_message_expired" now contains 135313 row versions in 
3308 pages
DETAIL:  41411 index row versions were removed.
1918 index pages have been deleted, 1918 are currently reusable.
CPU 0.30s/0.31u sec elapsed 36.01 sec.
INFO:  index "idx_expired_messages" now contains 1606437 row versions in 7064 
pages
DETAIL:  55221 index row versions were removed.

Re: [PERFORM] Problem with 7.4.5 and webmin 1.8 in grant function

[Mark Kirkwood]

[EMAIL PROTECTED] wrote:
After being a detector I found that
[EMAIL PROTECTED] ~]# rpm -qa|grep -i postgres
postgresql-7.4.5-3.1.tlc
postgresql-python-7.4.5-3.1.tlc
postgresql-jdbc-7.4.5-3.1.tlc
postgresql-tcl-7.4.5-3.1.tlc
postgresql-server-7.4.5-3.1.tlc
postgresql-libs-7.4.5-3.1.tlc
postgresql-docs-7.4.5-3.1.tlc
postgresql-odbc-7.3-8.1.tlc
postgresql-pl-7.4.5-3.1.tlc
postgresql-test-7.4.5-3.1.tlc
postgresql-contrib-7.4.5-3.1.tlc
[EMAIL PROTECTED] ~]#
no other pg installation except the pgsql for windows in samba folder which I
think it isn't matter ,is it?
No other  pg being run.
[EMAIL PROTECTED] ~]# ps ax|grep postmaster
 2228 ?S  0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
 3308 pts/0S+ 0:00 grep postmaster
[EMAIL PROTECTED] ~]#
Well, sure looks like you only have one running. Your data directory is
/var/lib/pgsql/data so lets see the files:
/var/lib/pgsql/data/pg_hba.conf
/var/lib/pgsql/data/pg_ident.conf
/var/lib/pgsql/data/postmaster.opts
Might also be useful to know any nondefault settings in postgresql.conf too.
As I understand it, these vendor shipped rpms have ident *enabled*.
I will download FC3 Pg and check this out... I'm a compile it from
source guy :-)
Mark

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [PERFORM] is pg_autovacuum so effective ?

[Matthew T. O'Connor]

Gaetano Mendola wrote:
pg_class after the vacuum full for that table
relfilenode | relname  | relpages |  reltuples
-+--+--+-
  18376 | messages |63307 | 1.60644e+06
pg_class before the vacuum full for that table
relfilenode | relname  | relpages |  reltuples
-+--+--+-
  18376 | messages |69472 | 1.60644e+06

how was possible accumulate 6000 pages wasted on that table?
Between these two calls:
[2005-02-22 05:25:03 CET] Performing: VACUUM ANALYZE "public"."messages"
[2005-02-22 15:20:39 CET] Performing: VACUUM ANALYZE "public"."messages"
1768 rows where inserted, and I had 21578 updated for that rows ( each
row have a counter incremented for each update ) so that table is not
so heavy updated
I'm running autovacuum with these parameters:
pg_autovacuum -d 3 -v 300 -V 0.1 -S 0.8 -a 200 -A 0.1 -D
shall I run it in a more aggressive way ? May be I'm missing
something.
Well without thinking too much, I would first ask about your FSM 
settings?  If they aren't big enought that will cause bloat.  Try 
bumping your FSM settings and then see if you reach steady state.

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

Re: [PERFORM] is pg_autovacuum so effective ?

[Tom Lane]

Gaetano Mendola <[EMAIL PROTECTED]> writes:
> I'm using ony pg_autovacuum. I expect that disk usage will reach
> a steady state but is not. PG engine: 7.4.5

One data point doesn't prove that you're not at a steady state.

> # vacuum full verbose messages;
> INFO:  vacuuming "public.messages"
> INFO:  "messages": found 77447 removable, 1606437 nonremovable row versions 
> in 69504 pages
> ...
> INFO:  "messages": moved 55221 row versions, truncated 69504 to 63307 pages

10% overhead sounds fairly reasonable to me.  How does that compare to
the amount of updating you do on the table --- ie, do you turn over 10%
of the table in a day?

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] VACUUM ANALYZE slows down query

[Tom Lane]

I wrote:
> Well, with the increased (and much more accurate) rowcount estimate,
> the estimated cost of the nestloop naturally went up a lot: it's
> proportional to the number of rows involved.  It appears that the
> estimated cost of the mergejoin actually went *down* quite a bit
> (else it'd have been selected the first time too).  That seems odd to
> me.

Nah, I just can't count :-(.  What I forgot about was the sub-select in
the output list:

>> select ToolRepairRequest.RequestID, (Select
>> count(ToolHistory.HistoryID) from ToolHistory where
>> ToolRepairRequest.RepairID=ToolHistory.RepairID) as
>> CountOfTH

which shows up in the (un-analyzed) EXPLAIN output here:

   SubPlan
 ->  Aggregate  (cost=524.17..524.17 rows=1 width=4) (actual 
time=0.032..0.035 rows=1 loops=1518)
   ->  Index Scan using th_repair_key on toolhistory  
(cost=0.00..523.82 rows=140 width=4) (actual time=0.013..0.018 rows=1 
loops=1518)
 Index Cond: ($0 = repairid)

Now in this case the planner is estimating 79 rows out, so the estimated
cost of the nestloop plan includes a charge of 79*524.17 for evaluating
the subplan.  If we discount that then the estimated cost of the
nestloop plan is 3974.74..6645.99 (48055.42-79*524.17).

In the ANALYZEd case the subplan is estimated to be a lot cheaper:

   SubPlan
 ->  Aggregate  (cost=6.98..6.98 rows=1 width=4) (actual time=0.038..0.042 
rows=1 loops=1518)
   ->  Index Scan using th_repair_key on toolhistory  (cost=0.00..6.97 
rows=2 width=4) (actual time=0.016..0.021 rows=1 loops=1518)
 Index Cond: ($0 = repairid)

It's estimated to be needed 1533 times, but that still adds up to less
of a charge than before.  Discounting that, the mergejoin plan was
estimated at 18310.59..18462.10 (29162.44 - 1533*6.98).  So it's not
true that the estimated cost of the join went down in the ANALYZEd case.

Werner sent me a data dump off-list, and trawling through the planner I
got these numbers for the estimated costs without the output subquery:

without any statistics:
mergejoin cost  9436.42 .. 9571.81
nestloop cost   3977.74 .. 6700.71

with statistics:
mergejoin cost  18213.04 .. 18369.73
nestloop cost4054.93 .. 24042.85

(these are a bit different from his results because of different ANALYZE
samples etc, but close enough)

So the planner isn't going crazy: in each case it chose what seemed the
cheapest total-cost plan.

regards, tom lane

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

Re: [PERFORM] is pg_autovacuum so effective ?

[Gaetano Mendola]

Matthew T. O'Connor wrote:

> Well without thinking too much, I would first ask about your FSM
> settings?  If they aren't big enought that will cause bloat.  Try
> bumping your FSM settings and then see if you reach steady state.

FSM settings are big enough:

 max_fsm_pages  | 200
 max_fsm_relations  | 1000

at least after a vacuum full I see that these numbers are an overkill...




REgards
Gaetano Mendola








---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] is pg_autovacuum so effective ?

[Gaetano Mendola]

Tom Lane wrote:
> Gaetano Mendola <[EMAIL PROTECTED]> writes:
> 
>>I'm using ony pg_autovacuum. I expect that disk usage will reach
>>a steady state but is not. PG engine: 7.4.5
> 
> 
> One data point doesn't prove that you're not at a steady state.

I do a graph about my disk usage and it's a ramp since one week,
I'll continue to wait in order to see if it will decrease.
I was expecting the steady state at something like 4 GB
( after a full vacuum and reindex ) + 10 % = 4.4 GB
I'm at 4.6 GB and increasing. I'll see how it will continue.

>># vacuum full verbose messages;
>>INFO:  vacuuming "public.messages"
>>INFO:  "messages": found 77447 removable, 1606437 nonremovable row versions 
>>in 69504 pages
>>...
>>INFO:  "messages": moved 55221 row versions, truncated 69504 to 63307 pages
> 
> 
> 10% overhead sounds fairly reasonable to me.  How does that compare to
> the amount of updating you do on the table --- ie, do you turn over 10%
> of the table in a day?

Less, that table have 1.6 milion rows, and I insert 2000 rows in a day
with almost ~ 4 update in one day. So it's something like: 2.5 %


Regards
Gaetano Mendola







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

   http://archives.postgresql.org

Re: [PERFORM] PostgreSQL is extremely slow on Windows

[Neil Conway]

Magnus Hagander wrote:
You can *never* get above 80 without using write cache, regardless of
your OS, if you have a single disk.
Why? Even with, say, a 15K RPM disk? Or the ability to fsync() multiple 
concurrently-committing transactions at once?

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

Re: [PERFORM] is pg_autovacuum so effective ?

[Christopher Browne]

Gaetano Mendola <[EMAIL PROTECTED]> writes:

> Tom Lane wrote:
>> Gaetano Mendola <[EMAIL PROTECTED]> writes:
>> 
>>>I'm using ony pg_autovacuum. I expect that disk usage will reach
>>>a steady state but is not. PG engine: 7.4.5
>> 
>> 
>> One data point doesn't prove that you're not at a steady state.
>
> I do a graph about my disk usage and it's a ramp since one week,
> I'll continue to wait in order to see if it will decrease.
> I was expecting the steady state at something like 4 GB
> ( after a full vacuum and reindex ) + 10 % = 4.4 GB
> I'm at 4.6 GB and increasing. I'll see how it will continue.

You probably want for the "experiment" to last more than a week.

After all, it might actually be that with your usage patterns, that
table would stabilize at 15% "overhead," and that might take a couple
or three weeks.

Unless it's clear that it's growing perilously quickly, just leave it
alone so that there's actually some possibility of reaching an
equilibrium.  Any time you "VACUUM FULL" it, that _destroys_ any
experimental results or any noticeable patterns, and it guarantees
that you'll see "seemingly perilous growth" for a while.

And if the table is _TRULY_ growing "perilously quickly," then it is
likely that you should add in some scheduled vacuums on the table.
Not VACUUM FULLs; just plain VACUUMs.

I revised cron scripts yet again today to do hourly and "4x/day"
vacuums of certain tables in some of our systems where we know they
need the attention.  I didn't schedule any VACUUM FULLs; it's
unnecessary, and would lead directly to system outages, which is
totally unacceptable.
-- 
"cbbrowne","@","ca.afilias.info"

Christopher Browne
(416) 673-4124 (land)

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

Re: [PERFORM] is pg_autovacuum so effective ?

[Christopher Browne]

Gaetano Mendola <[EMAIL PROTECTED]> writes:
> Matthew T. O'Connor wrote:
>
>> Well without thinking too much, I would first ask about your FSM
>> settings?  If they aren't big enought that will cause bloat.  Try
>> bumping your FSM settings and then see if you reach steady state.
>
> FSM settings are big enough:
>
>  max_fsm_pages  | 200
>  max_fsm_relations  | 1000
>
> at least after a vacuum full I see that these numbers are an overkill...

When you do a VACUUM FULL, the FSM is made irrelevant because VACUUM
FULL takes the time to reclaim all possible space without resorting to
_any_ use of the FSM.

If you VACUUM FULL, then it's of little value to bother having a free
space map because you're obviating the need to use it.

In any case, the FSM figures you get out of a VACUUM are only really
meaningful if you're moving towards the "equilibrium point" where the
FSM is large enough to cope with the growth between VACUUM cycles.
VACUUM FULL pushes the system away from equilibrium, thereby making
FSM estimates less useful.
-- 
"cbbrowne","@","ca.afilias.info"

Christopher Browne
(416) 673-4124 (land)

---(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

[PERFORM] Joins, Deletes and Indexes

[Butkus_Charles]

I've got 2 tables defined as follows:

CREATE TABLE "cluster"
(
  id int8 NOT NULL DEFAULT nextval('serial'::text),
  clusterid varchar(255) NOT NULL,
  ...
  CONSTRAINT pk_cluster PRIMARY KEY (id)
) 

CREATE TABLE sensorreport
(
  id int8 NOT NULL DEFAULT nextval('serial'::text),
  clusterid int8 NOT NULL,
  ...
  CONSTRAINT pk_sensorreport PRIMARY KEY (id),
  CONSTRAINT fk_sensorreport_clusterid FOREIGN KEY (clusterid) REFERENCES
"cluster" (id) ON UPDATE RESTRICT ON DELETE RESTRICT
) 

I've defined an Index on the clusterid field of sensorreport.


So I've run into 2 issues, one a SELECT, the other a DELETE;

SELECT issue:
So the following query:
EXPLAIN ANALYZE select * from sensorreport where clusterid = 25000114;

Yields:
"Index Scan using idx_sensorreport_clusterid on sensorreport
(cost=0.00..2.01 rows=1 width=129) (actual time=0.000..0.000 rows=38
loops=1)"
"  Index Cond: (clusterid = 25000114)"
"Total runtime: 0.000 ms"

However, when using a join as follows (in the cluster table id=25000114
clusterid='clusterid1'):
EXPLAIN ANALYZE select * from sensorreport as a join cluster as c on c.id =
a.clusterid where c.clusterid = 'clusterid1';

Yields:
Hash Join  (cost=1.18..566211.51 rows=1071429 width=287) (actual
time=150025.000..150025.000 rows=38 loops=1)
  Hash Cond: ("outer".clusterid = "inner".id)
  ->  Seq Scan on sensorreport a  (cost=0.00..480496.03 rows=1503
width=129) (actual time=10.000..126751.000 rows=1539 loops=1)
  ->  Hash  (cost=1.18..1.18 rows=1 width=158) (actual time=0.000..0.000
rows=0 loops=1)
->  Seq Scan on "cluster" c  (cost=0.00..1.18 rows=1 width=158)
(actual time=0.000..0.000 rows=1 loops=1)
  Filter: ((clusterid)::text = 'clusterid1'::text)
Total runtime: 150025.000 ms

My question is can I get the join query to use the
idx_sensorreport_clusterid index on the sensorreport table?

DELETE issue:
The statement:
EXPLAIN ANALYZE delete from cluster where clusterid='clusterid99'

Yields:
 Seq Scan on "cluster"  (cost=0.00..1.18 rows=1 width=6) (actual
time=0.000..0.000 rows=1 loops=1)
   Filter: ((clusterid)::text = 'clusterid99'::text)
 Total runtime: 275988.000 ms

I'm assuming that the length of the delete is because the "DELETE RESTRICT"
on the foreign key from sensortable.
Again, is there any way to get the delete to use the
idx_sensorreport_clusterid index?

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[PERFORM] Inefficient Query Plans

[Luke Chambers]

The following query plans both result from the very same query run on
different servers. They obviously differ drastically, but I don't why
as one db is a slonied copy of the other with identical postgresql.conf
files.
Both databases are vacuum analyzed nightly.

Here is the query:

EXPLAIN ANALYZE
SELECT COUNT(DISTINCT(t.id)) FROM (
  SELECT m_object_paper.id
FROM m_object_paper, m_assignment, m_class,
r_comment_rubric_user_object
WHERE m_object_paper.assignment=m_assignment.id
AND m_assignment.class=m_class.id
AND m_class.account IN (SELECT * FROM children_of(32660) as acts)
AND m_object_paper.id = r_comment_rubric_user_object.objectid
 UNION
  SELECT m_object_paper.id
FROM m_object_paper, m_assignment, m_class, r_quickmark_user_object
WHERE m_object_paper.assignment=m_assignment.id
AND m_assignment.class=m_class.id
AND m_class.account IN (SELECT * FROM children_of(32660) acts)
AND m_object_paper.id = r_quickmark_user_object.objectid)as t;


---
DB1 QUERY PLAN

--
Aggregate  (cost=314616.49..314616.49 rows=1 width=4) (actual
time=853.483..853.484 rows=1 loops=1)
   ->  Subquery Scan t  (cost=314568.97..314609.70 rows=2715 width=4)
(actual time=848.574..852.912 rows=354 loops=1)
 ->  Unique  (cost=314568.97..314582.55 rows=2715 width=4)
(actual time=848.568..852.352 rows=354 loops=1)
   ->  Sort  (cost=314568.97..314575.76 rows=2715 width=4)
(actual time=848.565..850.264 rows=2428 loops=1)
 Sort Key: id
 ->  Append  (cost=153181.39..314414.12 rows=2715
width=4) (actual time=224.984..844.714 rows=2428 loops=1)
   ->  Subquery Scan "*SELECT* 1"
(cost=153181.39..159900.66 rows=2250 width=4) (actual
time=224.981..700.687 rows=2116 loops=1)
 ->  Hash Join
(cost=153181.39..159878.16 rows=2250 width=4) (actual
time=224.975..696.639 rows=2116 loops=1)
   Hash Cond: ("outer".objectid =
"inner".id)
   ->  Seq Scan on
r_comment_rubric_user_object  (cost=0.00..5144.18 rows=306018 width=4)
(actual time=0.021..405.881 rows=306392 loops=1)
   ->  Hash
(cost=153072.40..153072.40 rows=43595 width=4) (actual
time=32.311..32.311 rows=0 loops=1)
 ->  Nested Loop
(cost=15.00..153072.40 rows=43595 width=4) (actual time=0.554..29.762
rows=2033 loops=1)
   ->  Nested Loop
(cost=15.00..16071.65 rows=3412 width=4) (actual time=0.512..3.657
rows=180 loops=1)
 ->  Nested
Loop  (cost=15.00..3769.73 rows=1666 width=4) (actual time=0.452..0.943
rows=50 loops=1)
   ->
HashAggregate  (cost=15.00..15.00 rows=200 width=4) (actual
time=0.388..0.394 rows=1 loops=1)
 ->
 Function Scan on children_of acts  (cost=0.00..12.50 rows=1000
width=4) (actual time=0.376..0.377 rows=1 loops=1)
   ->
Index Scan using m_class_account_idx on m_class  (cost=0.00..18.67
rows=8 width=8) (actual time=0.057..0.416 rows=50 loops=1)

Index Cond: (m_class.account = "outer".acts)
 ->  Index Scan
using m_assignment_class_idx on m_assignment  (cost=0.00..7.25 rows=11
width=8) (actual time=0.023..0.043 rows=4 loops=50)
   Index
Cond: (m_assignment."class" = "outer".id)
   ->  Index Scan using
m_object_paper_assignment_idx on m_object_paper  (cost=0.00..39.24
rows=73 width=8) (actual time=0.026..0.118 rows=11 loops=180)
 Index Cond:
(m_object_paper."assignment" = "outer".id)
   ->  Subquery Scan "*SELECT* 2"
(cost=153181.39..154513.46 rows=465 width=4) (actual
time=54.883..140.747 rows=312 loops=1)
 ->  Hash Join
(cost=153181.39..154508.81 rows=465 width=4) (actual
time=54.875..140.161 rows=312 loops=1)
   Hash Cond: ("outer".objectid =
"inner".id)
   ->  Seq Scan on
r_quickmark_user_object  (cost=0.00..1006.85 rows=63185 width=4)
(actual time=0.007..70.446 rows=63268 loops=1)
   ->  Hash
(cost=153072.40..153072.40 rows=43595 width=4) (actual
time=17.633..17.

[PERFORM] PostgreSQL is extremely slow on Windows

[Vig, Sandor (G/FI-2)]

Hi,

I've downloaded the latest release (PostgreSQL 8.0) for windows.
Installation was OK, but I have tried to restore a database.
It had more than ~100.000 records. Usually I use PostgreSQL
under Linux, and it used to be done under 10 minutes.

Under W2k und XP it took 3 hours(!) Why is it so slow

The commands I used:

Under Linux: (duration: 1 minute)
pg_dump -D databasename > databasename.db

Under Windows: (duration: 3 - 3.5 hours(!))
psql databasename < databasename.db >nul

It seemed to me, that only 20-30 transactions/sec were
writen to the database.

I need to write scripts for automatic (sheduled) database
backup and recovery.

Help anyone?

Bye,

Vig Sándor


The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.

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

[PERFORM] Help me please !

[Asatryan, Anahit]

I am running postgreSQL 8.0.1 under the Windows 2000. I want
to use COPY FROM STDIN function from Java application, but it doesn’t
work, it throws:

“org.postgresql.util.PSQLException: Unknown Response
Type G”  error.

Please help me!

Note: COPY FROM filename works properly.

 

 

[PERFORM] subquery vs join on 7.4.5

[David Haas]

Hi -
This is based on a discussion I was having with neilc on IRC.  He  
suggested I post it here.  Sorry for the length - I'm including  
everything he requested

I'm comparing the speeds of the following two queries on 7.4.5.  I was  
curious why query 1 was faster than query 2:

query 1:
Select layer_number
FROM batch_report_index
WHERE device_id = (SELECT device_id FROM device_index WHERE device_name  
='CP8M')
AND technology_id = (SELECT technology_id FROM technology_index WHERE  
technology_name = 'G12');

query 2:
Select b.layer_number
FROM batch_report_index b, device_index d, technology_index t
WHERE b.device_id = d.device_id
AND b.technology_id = t.technology_id
AND d.device_name = 'CP8M'
AND t.technology_name = 'G12';
Here were my first runs:
(query 1 explain analyze)
 Seq Scan on batch_report_index  (cost=6.05..12370.66 rows=83 width=4)  
(actual time=19.274..1903.110 rows=61416 loops=1)
   Filter: ((device_id = $0) AND (technology_id = $1))
   InitPlan
 ->  Index Scan using device_index_device_name_key on device_index   
(cost=0.00..4.88 rows=1 width=4) (actual time=0.310..0.320 rows=1  
loops=1)
   Index Cond: (device_name = 'CP8M'::text)
 ->  Seq Scan on technology_index  (cost=0.00..1.18 rows=1 width=4)  
(actual time=0.117..0.149 rows=1 loops=1)
   Filter: (technology_name = 'G12'::text)
 Total runtime: 1947.896 ms
(8 rows)

(query 2 explain analyze)
 Hash Join  (cost=6.06..12380.70 rows=46 width=4) (actual  
time=35.509..2831.685 rows=61416 loops=1)
   Hash Cond: ("outer".technology_id = "inner".technology_id)
   ->  Hash Join  (cost=4.88..12375.87 rows=638 width=8) (actual  
time=34.584..2448.862 rows=61416 loops=1)
 Hash Cond: ("outer".device_id = "inner".device_id)
 ->  Seq Scan on batch_report_index b  (cost=0.00..10182.74  
rows=436374 width=12) (actual time=0.100..1373.085 rows=436374 loops=1)
 ->  Hash  (cost=4.88..4.88 rows=1 width=4) (actual  
time=0.635..0.635 rows=0 loops=1)
   ->  Index Scan using device_index_device_name_key on  
device_index d  (cost=0.00..4.88 rows=1 width=4) (actual  
time=0.505..0.520 rows=1 loops=1)
 Index Cond: (device_name = 'CP8M'::text)
   ->  Hash  (cost=1.18..1.18 rows=1 width=4) (actual time=0.348..0.348  
rows=0 loops=1)
 ->  Seq Scan on technology_index t  (cost=0.00..1.18 rows=1  
width=4) (actual time=0.198..0.239 rows=1 loops=1)
   Filter: (technology_name = 'G12'::text)
 Total runtime: 2872.252 ms
(12 rows)

On neilc's suggestion, I did a vacuum analyze, then turned off hash  
joins.  Here's query 2, no hash joins:

(query 2 explain analyze)
 Nested Loop  (cost=0.00..15651.44 rows=46 width=4) (actual  
time=22.079..2741.103 rows=61416 loops=1)
   Join Filter: ("inner".technology_id = "outer".technology_id)
   ->  Seq Scan on technology_index t  (cost=0.00..1.18 rows=1 width=4)  
(actual time=0.178..0.218 rows=1 loops=1)
 Filter: (technology_name = 'G12'::text)
   ->  Nested Loop  (cost=0.00..15642.29 rows=638 width=8) (actual  
time=21.792..2530.470 rows=61416 loops=1)
 Join Filter: ("inner".device_id = "outer".device_id)
 ->  Index Scan using device_index_device_name_key on  
device_index d  (cost=0.00..4.88 rows=1 width=4) (actual  
time=0.331..0.346 rows=1 loops=1)
   Index Cond: (device_name = 'CP8M'::text)
 ->  Seq Scan on batch_report_index b  (cost=0.00..10182.74  
rows=436374 width=12) (actual time=0.070..1437.938 rows=436374 loops=1)
 Total runtime: 2782.628 ms
(10 rows)

He then suggested I turn hash_joins back on and put an index on the  
batch_report_table's device_id.  Here's query 2 again:

(query 2 explain analyze)
Hash Join  (cost=1.18..2389.06 rows=46 width=4) (actual  
time=1.562..2473.554 rows=61416 loops=1)
   Hash Cond: ("outer".technology_id = "inner".technology_id)
   ->  Nested Loop  (cost=0.00..2384.24 rows=638 width=8) (actual  
time=0.747..2140.160 rows=61416 loops=1)
 ->  Index Scan using device_index_device_name_key on  
device_index d  (cost=0.00..4.88 rows=1 width=4) (actual  
time=0.423..0.435 rows=1 loops=1)
   Index Cond: (device_name = 'CP8M'::text)
 ->  Index Scan using b_r_device_index on batch_report_index b   
(cost=0.00..2365.82 rows=1083 width=12) (actual time=0.288..1868.118  
rows=61416 loops=1)
   Index Cond: (b.device_id = "outer".device_id)
   ->  Hash  (cost=1.18..1.18 rows=1 width=4) (actual time=0.359..0.359  
rows=0 loops=1)
 ->  Seq Scan on technology_index t  (cost=0.00..1.18 rows=1  
width=4) (actual time=0.198..0.237 rows=1 loops=1)
   Filter: (technology_name = 'G12'::text)
 Total runtime: 2515.950 ms
(11 rows)

He then suggested I increase the statistics on batch_report_index & run  
the query again.  I "set statistics" for both
the device_id and technology_id column to 900, vacuum analyzed, and  
re-ran the query (it's still slower than query
1 run after the same contortions ):

(query

[PERFORM] join vs. subquery

[David Haas]

Hi -
This is based on a discussion I was having with neilc on IRC.  He  
suggested I post it here.  Sorry for the length - I'm including  
everything he requested.

I'm comparing the speeds of the following two queries.  I was curious  
why query 1 was faster than query 2:

query 1:
Select layer_number
FROM batch_report_index
WHERE device_id = (SELECT device_id FROM device_index WHERE device_name  
='CP8M')
AND technology_id = (SELECT technology_id FROM technology_index WHERE  
technology_name = 'G12');

query 2:
Select b.layer_number
FROM batch_report_index b, device_index d, technology_index t
WHERE b.device_id = d.device_id
AND b.technology_id = t.technology_id
AND d.device_name = 'CP8M'
AND t.technology_name = 'G12';
Here were my first runs:
(query 1 explain analyze)
 Seq Scan on batch_report_index  (cost=6.05..12370.66 rows=83 width=4)  
(actual time=19.274..1903.110 rows=61416 loops=1)
   Filter: ((device_id = $0) AND (technology_id = $1))
   InitPlan
 ->  Index Scan using device_index_device_name_key on device_index   
(cost=0.00..4.88 rows=1 width=4) (actual time=0.310..0.320 rows=1  
loops=1)
   Index Cond: (device_name = 'CP8M'::text)
 ->  Seq Scan on technology_index  (cost=0.00..1.18 rows=1 width=4)  
(actual time=0.117..0.149 rows=1 loops=1)
   Filter: (technology_name = 'G12'::text)
 Total runtime: 1947.896 ms
(8 rows)

(query 2 explain analyze)
 Hash Join  (cost=6.06..12380.70 rows=46 width=4) (actual  
time=35.509..2831.685 rows=61416 loops=1)
   Hash Cond: ("outer".technology_id = "inner".technology_id)
   ->  Hash Join  (cost=4.88..12375.87 rows=638 width=8) (actual  
time=34.584..2448.862 rows=61416 loops=1)
 Hash Cond: ("outer".device_id = "inner".device_id)
 ->  Seq Scan on batch_report_index b  (cost=0.00..10182.74  
rows=436374 width=12) (actual time=0.100..1373.085 rows=436374 loops=1)
 ->  Hash  (cost=4.88..4.88 rows=1 width=4) (actual  
time=0.635..0.635 rows=0 loops=1)
   ->  Index Scan using device_index_device_name_key on  
device_index d  (cost=0.00..4.88 rows=1 width=4) (actual  
time=0.505..0.520 rows=1 loops=1)
 Index Cond: (device_name = 'CP8M'::text)
   ->  Hash  (cost=1.18..1.18 rows=1 width=4) (actual time=0.348..0.348  
rows=0 loops=1)
 ->  Seq Scan on technology_index t  (cost=0.00..1.18 rows=1  
width=4) (actual time=0.198..0.239 rows=1 loops=1)
   Filter: (technology_name = 'G12'::text)
 Total runtime: 2872.252 ms
(12 rows)

On neilc's suggestion, I did a vacuum analyze, then turned off hash  
joins.  Here's query 2, no hash joins:

(query 2 explain analyze)
 Nested Loop  (cost=0.00..15651.44 rows=46 width=4) (actual  
time=22.079..2741.103 rows=61416 loops=1)
   Join Filter: ("inner".technology_id = "outer".technology_id)
   ->  Seq Scan on technology_index t  (cost=0.00..1.18 rows=1 width=4)  
(actual time=0.178..0.218 rows=1 loops=1)
 Filter: (technology_name = 'G12'::text)
   ->  Nested Loop  (cost=0.00..15642.29 rows=638 width=8) (actual  
time=21.792..2530.470 rows=61416 loops=1)
 Join Filter: ("inner".device_id = "outer".device_id)
 ->  Index Scan using device_index_device_name_key on  
device_index d  (cost=0.00..4.88 rows=1 width=4) (actual  
time=0.331..0.346 rows=1 loops=1)
   Index Cond: (device_name = 'CP8M'::text)
 ->  Seq Scan on batch_report_index b  (cost=0.00..10182.74  
rows=436374 width=12) (actual time=0.070..1437.938 rows=436374 loops=1)
 Total runtime: 2782.628 ms
(10 rows)

He then suggested I turn hash_joins back on and put an index on the  
batch_report_table's device_id.  Here's query 2 again:

(query 2 explain analyze)
Hash Join  (cost=1.18..2389.06 rows=46 width=4) (actual  
time=1.562..2473.554 rows=61416 loops=1)
   Hash Cond: ("outer".technology_id = "inner".technology_id)
   ->  Nested Loop  (cost=0.00..2384.24 rows=638 width=8) (actual  
time=0.747..2140.160 rows=61416 loops=1)
 ->  Index Scan using device_index_device_name_key on  
device_index d  (cost=0.00..4.88 rows=1 width=4) (actual  
time=0.423..0.435 rows=1 loops=1)
   Index Cond: (device_name = 'CP8M'::text)
 ->  Index Scan using b_r_device_index on batch_report_index b   
(cost=0.00..2365.82 rows=1083 width=12) (actual time=0.288..1868.118  
rows=61416 loops=1)
   Index Cond: (b.device_id = "outer".device_id)
   ->  Hash  (cost=1.18..1.18 rows=1 width=4) (actual time=0.359..0.359  
rows=0 loops=1)
 ->  Seq Scan on technology_index t  (cost=0.00..1.18 rows=1  
width=4) (actual time=0.198..0.237 rows=1 loops=1)
   Filter: (technology_name = 'G12'::text)
 Total runtime: 2515.950 ms
(11 rows)

He then suggested I increase the statistics on batch_report_index & run  
the query again.  I "set statistics" for both
the device_id and technology_id column to 900, vacuum analyzed, and  
re-ran the query (it's still slower than query
1 run after the same contortions ):

(query 2 expla