from:"peter"

Am Dienstag, 8. Mai 2007 17:53 schrieb Tom Lane:
> Hmm, I'd have expected it to discount the repeated indexscans a lot more
> than it seems to be doing for you.  As an example in the regression
> database, note what happens to the inner indexscan cost estimate when
> the number of outer tuples grows:

I can reproduce your results in the regression test database. 8.2.1 and 8.2.4 
behave the same.

I checked the code around cost_index(), and the assumptions appear to be 
correct (at least this query doesn't produce wildly unusual data).  
Apparently, however, the caching effects are much more significant than the 
model takes into account.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

[PERFORM] Apparently useless bitmap scans

AND mime_part_id = 0

from the query, but why does it need three of them to do it, when all
of them have the same predicate and none of them has an indexed
expression that appears in the query?

There are more partial indexes with the same predicate, but it appears
to always use three.  (The two "dummy" indexes are just leftovers from
these experiments.)

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [PERFORM] Apparently useless bitmap scans

Am Mittwoch, 9. Mai 2007 16:29 schrieb Alvaro Herrera:
> Peter Eisentraut wrote:
> > There's another odd thing about this plan from yesterday.
>
> Is this still 8.2.1?  The logic to choose bitmap indexes was rewritten
> just before 8.2.4,

OK, upgrading to 8.2.4 fixes this odd plan choice.  The query does run
a bit faster too, but the cost estimate has actually gone up!

8.2.1:


  QUERY PLAN
   
---
 GroupAggregate  (cost=87142.18..87366.58 rows=11220 width=184) (actual 
time=7883.541..8120.647 rows=35000 loops=1)
   ->  Sort  (cost=87142.18..87170.23 rows=11220 width=184) (actual 
time=7883.471..7926.031 rows=35000 loops=1)
 Sort Key: eh_subj.header_body
 ->  Hash Join  (cost=46283.30..86387.42 rows=11220 width=184) (actual 
time=5140.182..7635.615 rows=35000 loops=1)
   Hash Cond: (eh_subj.email_id = email.email_id)
   ->  Bitmap Heap Scan on email_header eh_subj  
(cost=11853.68..50142.87 rows=272434 width=104) (actual time=367.956..1719.736 
rows=280989 loops=1)
 Recheck Cond: ((mime_part_id = 0) AND (header_name = 
'subject'::text))
 ->  BitmapAnd  (cost=11853.68..11853.68 rows=27607 
width=0) (actual time=326.507..326.507 rows=0 loops=1)
   ->  Bitmap Index Scan on 
idx__email_header__header_body_subject  (cost=0.00..5836.24 rows=272434 
width=0) (actual time=178.041..178.041 rows=280989 loops=1)
   ->  Bitmap Index Scan on 
idx__email_header__header_name  (cost=0.00..5880.97 rows=281247 width=0) 
(actual time=114.574..114.574 rows=280989 loops=1)
 Index Cond: (header_name = 'subject'::text)
   ->  Hash  (cost=34291.87..34291.87 rows=11020 width=120) (actual 
time=4772.148..4772.148 rows=35000 loops=1)
 ->  Hash Join  (cost=24164.59..34291.87 rows=11020 
width=120) (actual time=3131.067..4706.997 rows=35000 loops=1)
   Hash Cond: (mime_part.email_id = email.email_id)
   ->  Seq Scan on mime_part  (cost=0.00..8355.81 
rows=265804 width=12) (actual time=0.038..514.291 rows=267890 loops=1)
 Filter: (mime_part_id = 0)
   ->  Hash  (cost=24025.94..24025.94 rows=11092 
width=112) (actual time=3130.982..3130.982 rows=35000 loops=1)
 ->  Hash Join  (cost=22244.54..24025.94 
rows=11092 width=112) (actual time=996.556..3069.280 rows=35000 loops=1)
   Hash Cond: (eh_from.email_id = 
email.email_id)
   ->  Bitmap Heap Scan on email_header 
eh_from  (cost=15576.58..16041.55 rows=107156 width=104) (actual 
time=569.762..1932.017 rows=280990 loops=1)
 Recheck Cond: ((mime_part_id = 0) 
AND (header_name = 'from'::text))
 ->  BitmapAnd  
(cost=15576.58..15576.58 rows=160 width=0) (actual time=532.217..532.217 rows=0 
loops=1)
   ->  Bitmap Index Scan on 
dummy_index  (cost=0.00..3724.22 rows=107156 width=0) (actual 
time=116.386..116.386 rows=280990 loops=1)
   ->  Bitmap Index Scan on 
idx__email_header__from_local  (cost=0.00..5779.24 rows=107156 width=0) (actual 
time=174.883..174.883 rows=280990 loops=1)
   ->  Bitmap Index Scan on 
dummy2_index  (cost=0.00..5992.25 rows=107156 width=0) (actual 
time=173.575..173.575 rows=280990 loops=1)
   ->  Hash  (cost=6321.79..6321.79 
rows=27694 width=8) (actual time=426.739..426.739 rows=35000 loops=1)
 ->  Index Scan using 
idx__email__time on email  (cost=0.00..6321.79 rows=27694 width=8) (actual 
time=50.000..375.021 rows=35000 loops=1)
   Index Cond: (("time" >= 
'2007-05-05 17:01:59'::timestamp without time zone) AND ("time" < '2007-05-05 
18:01:59'::timestamp without time zone))
 Total runtime: 8160.442 ms


8.2.4:


QUERY PLAN  
  
-

Re: [PERFORM] Nested loops overpriced

Am Mittwoch, 9. Mai 2007 16:11 schrieb Tom Lane:
> Well, there's something funny going on here.  You've got for instance
>
>->  Index Scan using email_pkey on email  (cost=0.00..3.85
> rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=280990) Index Cond:
> (email.email_id = eh_from.email_id)
>  Filter: (("time" >= '2007-05-05 17:01:59'::timestamp
> without time zone) AND ("time" < '2007-05-05 18:01:59'::timestamp without
> time zone))
>
> on the inside of a nestloop whose outer side is predicted to return
> 107156 rows.  That should've been discounted to *way* less than 3.85
> cost units per iteration.

This is the new plan with 8.2.4.  It's still got the same problem, though.


QUERY PLAN  
   
---
 GroupAggregate  (cost=5627064.21..5627718.73 rows=32726 width=184) (actual 
time=4904.834..5124.585 rows=35000 loops=1)
   ->  Sort  (cost=5627064.21..5627146.03 rows=32726 width=184) (actual 
time=4904.771..4947.892 rows=35000 loops=1)
 Sort Key: eh_subj.header_body
 ->  Nested Loop  (cost=0.00..5624610.06 rows=32726 width=184) (actual 
time=0.397..4628.141 rows=35000 loops=1)
   ->  Nested Loop  (cost=0.00..1193387.12 rows=28461 width=120) 
(actual time=0.322..3960.360 rows=35000 loops=1)
 ->  Nested Loop  (cost=0.00..1081957.26 rows=28648 
width=112) (actual time=0.238..3572.023 rows=35000 loops=1)
   ->  Index Scan using dummy_index on email_header 
eh_from  (cost=0.00..13389.15 rows=280662 width=104) (actual 
time=0.133..1310.248 rows=280990 loops=1)
   ->  Index Scan using email_pkey on email  
(cost=0.00..3.79 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=280990)
 Index Cond: (email.email_id = eh_from.email_id)
 Filter: (("time" >= '2007-05-05 
17:01:59'::timestamp without time zone) AND ("time" < '2007-05-05 
18:01:59'::timestamp without time zone))
 ->  Index Scan using mime_part_pkey on mime_part  
(cost=0.00..3.88 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=35000)
   Index Cond: ((email.email_id = mime_part.email_id) 
AND (mime_part.mime_part_id = 0))
   ->  Index Scan using idx__email_header__email_id__mime_part_id 
on email_header eh_subj  (cost=0.00..155.47 rows=18 width=104) (actual 
time=0.009..0.014 rows=1 loops=35000)
 Index Cond: ((email.email_id = eh_subj.email_id) AND (0 = 
eh_subj.mime_part_id))
 Filter: (header_name = 'subject'::text)
 Total runtime: 5161.390 ms

> Are you using any nondefault planner settings?

random_page_cost = 3
effective_cache_size = 384MB

> How big are these tables, anyway?

email   35 MB
email_header421 MB
mime_part   37 MB

Everything is analyzed, vacuumed, and reindexed.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [PERFORM] Nested loops overpriced

2007-05-10 Thread Peter Eisentraut

Am Mittwoch, 9. Mai 2007 19:40 schrieb Tom Lane:
> I remember having dithered about whether
> to try to avoid counting the same physical relation more than once in
> total_table_pages, but this example certainly suggests that we
> shouldn't.  Meanwhile, do the estimates get better if you set
> effective_cache_size to 1GB or so?

Yes, that makes the plan significantly cheaper (something like 500,000 instead 
of 5,000,000), but still a lot more expensive than the hash join (about 
100,000).

> To return to your original comment: if you're trying to model a
> situation with a fully cached database, I think it's sensible
> to set random_page_cost = seq_page_cost = 0.1 or so.  You had
> mentioned having to decrease them to 0.02, which seems unreasonably
> small to me too, but maybe with the larger effective_cache_size
> you won't have to go that far.

Heh, when I decrease these parameters, the hash join gets cheaper as well.  I 
can't actually get it to pick the nested-loop join.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

   http://archives.postgresql.org

Re: [PERFORM] Nested loops overpriced

2007-05-10 Thread Peter Eisentraut

Am Mittwoch, 9. Mai 2007 19:40 schrieb Tom Lane:
> Hmmm ... I see at least part of the problem, which is that email_header
> is joined twice in this query, which means that it's counted twice in
> figuring the total volume of pages competing for cache space.  So the
> thing thinks cache space is oversubscribed nearly 3X when in reality
> the database is fully cached.

I should add that other, similar queries in this database that do not involve 
joining the same table twice produce seemingly optimal plans.  (It picks hash 
joins which are actually faster than nested loops.)

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [PERFORM] Postgres Benchmark Results

2007-05-21 Thread Peter Schuller

> - Deferred Transactions, since adding a comment to a blog post
> doesn't need the same guarantees than submitting a paid order, it makes
> sense that the application could tell postgres which transactions we
> care about if power is lost. This will massively boost performance for
> websites I believe.

This would be massively useful. Very often all I care about is that the
transaction is semantically committed; that is, that other transactions
starting from that moment will see the modifications done. As opposed to
actually persisting data to disk.

In particular I have a situation where I attempt to utilize available
hardware by using concurrency. The problem is that I have to either
hugely complicate my client code or COMMIT more often than I would like
in order to satisfy dependencies between different transactions. If a
deferred/delayed commit were possible I could get all the performance
benefit without the code complexity, and with no penalty (because in
this case persistence is not important).

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <[EMAIL PROTECTED]>'
Key retrieval: Send an E-Mail to [EMAIL PROTECTED]
E-Mail: [EMAIL PROTECTED] Web: http://www.scode.org




signature.asc
Description: OpenPGP digital signature

Re: [PERFORM] Key/Value reference table generation: INSERT/UPDATE performance

2007-05-22 Thread Peter Childs


On 22 May 2007 01:23:03 -0700, valgog <[EMAIL PROTECTED]> wrote:


I found several post about INSERT/UPDATE performance in this group,
but actually it was not really what I am searching an answer for...

I have a simple reference table WORD_COUNTS that contains the count of
words that appear in a word array storage in another table.

CREATE TABLE WORD_COUNTS
(
  word text NOT NULL,
  count integer,
  CONSTRAINT PK_WORD_COUNTS PRIMARY KEY (word)
)
WITHOUT OIDS;




Is there any reason why count is not not null? (That should siplify your
code by removing the coalesce)

insert is more efficient than update because update is always a delete
followed by an insert.

Oh and group by is nearly always quicker than distinct and can always? be
rewritten as such. I'm not 100% sure why its different but it is.

Peter.



I have some PL/pgSQL code in a stored procedure like


  FOR r
   IN select id, array_of_words
from word_storage
  LOOP
begin
  -- insert the missing words
  insert into WORD_COUNTS
  ( word, count )
  ( select word, 0
  from ( select distinct (r.array_of_words)
[s.index] as d_word
   from generate_series(1,
array_upper( r.array_of_words, 1 ) ) as s(index) ) as distinct_words
 where word not in ( select d_word from
WORD_COUNTS  ) );
  -- update the counts
  update WORD_COUNTS
 set count = COALESCE( count, 0 ) + 1
   where word in ( select distinct (r.array_of_words)[s.index] as
word
from generate_series(1,
array_upper( r.array_of_words, 1) ) as s(index) );
exception when others then
  error_count := error_count + 1;
end;
record_count := record_count + 1;
  END LOOP;

This code runs extremely slowly. It takes about 10 minutes to process
1 records and the word storage has more then 2 million records to
be processed.

Does anybody have a know-how about populating of such a reference
tables and what can be optimized in this situation.

Maybe the generate_series() procedure to unnest the array is the place
where I loose the performance?

Are the set update/inserts more effitient, then single inserts/updates
run in smaller loops?

Thanks for your help,

Valentine Gogichashvili


---(end of broadcast)---
TIP 1: 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: [PERFORM] max_fsm_pages, shared_buffers and checkpoint_segments

2007-05-23 Thread Peter Schuller

> increasing checkpoint_segments,which is also a disk thing. However, setting
> it to 25, and then increasing any of the other 2 variables, the postgresql
> daemon stops working. meaning it does not start upon reboot. When I bring

Sounds like you need to increase your shared memory limits.
Unfortunately this will require a reboot on FreeBSD :(

See:

   http://www.postgresql.org/docs/8.2/static/kernel-resources.html

Last time I checked PostgreSQL should be complaining about the shared
memory on startup rather than silently fail though. Check your logs
perhaps. Though I believe the RC script will cause the message to be
printed interactively at the console too, if you run it. (Assuming you
are using it installed from ports).

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <[EMAIL PROTECTED]>'
Key retrieval: Send an E-Mail to [EMAIL PROTECTED]
E-Mail: [EMAIL PROTECTED] Web: http://www.scode.org




signature.asc
Description: OpenPGP digital signature

Re: [PERFORM] setting up raid10 with more than 4 drives

2007-05-30 Thread Peter Childs

On 30/05/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

On Wed, 30 May 2007, Jonah H. Harris wrote:

> On 5/29/07, Luke Lonergan <[EMAIL PROTECTED]> wrote:
>>  AFAIK you can't RAID1 more than two drives, so the above doesn't make
>>  sense
>>  to me.
>
> Yeah, I've never seen a way to RAID-1 more than 2 drives either.  It
> would have to be his first one:
>
> D1 + D2 = MD0 (RAID 1)
> D3 + D4 = MD1 ...
> D5 + D6 = MD2 ...
> MD0 + MD1 + MD2 = MDF (RAID 0)
>

I don't know what the failure mode ends up being, but on linux I had no
problems creating what appears to be a massively redundant (but small)
array

md0 : active raid1 sdo1[10](S) sdn1[8] sdm1[7] sdl1[6] sdk1[5] sdj1[4]
sdi1[3] sdh1[2] sdg1[9] sdf1[1] sde1[11](S) sdd1[0]
   896 blocks [10/10] [UU]

David Lang

Good point, also if you had Raid 1 with 3 drives with some bit errors at
least you can take a vote on whats right. Where as if you only have 2 and
they disagree how do you know which is right other than pick one and hope...
But whatever it will be slower to keep in sync on a heavy write system.

Peter.

Re: [PERFORM] optimize query with a maximum(date) extraction

2007-09-05 Thread Peter Childs

On 05/09/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
>
> "Gregory Stark" <[EMAIL PROTECTED]> writes:
>
> > "JS Ubei" <[EMAIL PROTECTED]> writes:
> >
> >> I need to improve a query like :
> >>
> >> SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
> >...
> > I don't think you'll find anything much faster for this particular
> query. You
> > could profile running these two (non-standard) queries:
> >
> > SELECT DISTINCT ON (id) id, the_date AS min_date FROM my_table ORDER BY
> id, the_date ASC
> > SELECT DISTINCT ON (id) id, the_date AS max_date FROM my_table ORDER BY
> id, the_date DESC
>
> Something else you might try:
>
> select id,
>(select min(the_date) from my_table where id=x.id) as min_date,
>(select max(the_date) from my_table where id=x.id) as max_date
>   from (select distinct id from my_table)
>
> Recent versions of Postgres do know how to use the index for a simple
> ungrouped min() or max() like these subqueries.
>
> This would be even better if you have a better source for the list of
> distinct
> ids you're interested in than my_table. If you have a source that just has
> one
> record for each id then you won't need an extra step to eliminate
> duplicates.
>
>
My personal reaction is why are you using distinct at all?

why not

select id,
   min(the_date) as min_date,
   max(the_date) as max_date
  from my_table group by id;

Since 8.0 or was it earlier this will use an index should a reasonable one
exist.

Peter.

Re: [PERFORM] Long Running Commits - Not Checkpoints

2007-09-14 Thread Peter Childs

On 13/09/2007, Greg Smith <[EMAIL PROTECTED]> wrote:
>
>
> Every time the all scan writes a buffer that is frequently used, that
> write has a good chance that it was wasted because the block will be
> modified again before checkpoint time.  Your settings are beyond regular
> aggressive and into the hyperactive terrority where I'd expect such
> redundant writes are happening often.  I'd suggest you try to move toward
> dropping bgwriter_all_percent dramatically from its current setting and
> see how far down you can go before it starts to introduce blocks at
> checkpoint time.  With bgwriter_delay set to 1/4 the default, I would
> expect that even 5% would be a high setting for you.  That may be a more
> dramatic change than you want to make at once though, so lowering it in
> that direction more slowly (perhaps drop 5% each day) and seeing whether
> things improve as that happens may make more sense.
>
>
Are you suggesting that reducing bgwriter_delay and bg_writer_percent would
reduce the time spent doing commits?

I get quite a few commits that take over 500ms (the point when i start
logging queries). I always thought oh just one of those things but if they
can be reduced by changing a few config variables that would be great. I'm
just trying to workout what figures are worth trying to see if I can reduce
them.

>From time to time I get commits that take 6 or 7 seconds but not all the
time.

I'm currently working with the defaults.

Peter Childs

Re: [PERFORM] Long Running Commits - Not Checkpoints

2007-09-14 Thread Peter Childs

On 14/09/2007, Peter Childs <[EMAIL PROTECTED]> wrote:
>
>
>
> On 13/09/2007, Greg Smith <[EMAIL PROTECTED]> wrote:
> >
> >
> > Every time the all scan writes a buffer that is frequently used, that
> > write has a good chance that it was wasted because the block will be
> > modified again before checkpoint time.  Your settings are beyond regular
> >
> > aggressive and into the hyperactive terrority where I'd expect such
> > redundant writes are happening often.  I'd suggest you try to move
> > toward
> > dropping bgwriter_all_percent dramatically from its current setting and
> > see how far down you can go before it starts to introduce blocks at
> > checkpoint time.  With bgwriter_delay set to 1/4 the default, I would
> > expect that even 5% would be a high setting for you.  That may be a more
> > dramatic change than you want to make at once though, so lowering it in
> > that direction more slowly (perhaps drop 5% each day) and seeing whether
> > things improve as that happens may make more sense.
> >
> >
> Are you suggesting that reducing bgwriter_delay and bg_writer_percent
> would reduce the time spent doing commits?
>
> I get quite a few commits that take over 500ms (the point when i start
> logging queries). I always thought oh just one of those things but if they
> can be reduced by changing a few config variables that would be great. I'm
> just trying to workout what figures are worth trying to see if I can reduce
> them.
>
> From time to time I get commits that take 6 or 7 seconds but not all the
> time.
>
> I'm currently working with the defaults.
>
> Peter Childs
>

Hmm Always read the manual, Increase them from the defaults...

Peter.

Re: [PERFORM] Tablespaces and NFS

2007-09-19 Thread Peter Koczan

On 9/19/07, Carlos Moreno <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Anyone has tried a setup combining tablespaces with NFS-mounted partitions?
>
> I'm considering the idea as a performance-booster --- our problem is
> that we are
> renting our dedicated server from a hoster that does not offer much
> flexibility
> in terms of custom hardware configuration;  so, the *ideal* alternative
> to load
> the machine with 4 or 6 hard drives and use tablespaces is off the table
> (no pun
> intended).
>
> We could, however, set up a few additional servers where we could configure
> NFS shares, mount them on the main PostgreSQL server, and configure
> tablespaces to "load balance" the access to disk.
>
> Would you estimate that this will indeed boost performance??  (our system
> does lots of writing to DB --- in all forms:  inserts, updates, and deletes)
>
> As a corollary question:  what about the WALs and tablespaces??  Are the
> WALs "distributed" when we setup a tablespace and create tables in it?
> (that is, are the WALs corresponding to the tables in a tablespace stored
> in the directory corresponding to the tablespace?  Or is it only the
> data, and
> the WAL keeps being the one and only?)
>
> Thanks,
>
> Carlos

About 5 months ago, I did an experiment serving tablespaces out of
AFS, another shared file system.

You can read my full post at
http://archives.postgresql.org/pgsql-admin/2007-04/msg00188.php

On the whole, you're not going to see a performance improvement
running tablespaces on NFS (unless the disk system on the NFS server
is a lot faster) since you have to go through the network as well as
NFS, both of which add overhead.

Usually, locking mechanisms on shared file systems don't play nice
with databases. You're better off using something else to load balance
or replicate data.

Peter

P.S. Why not just set up those servers you're planning on using as NFS
shares as your postgres server(s)?

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

   http://archives.postgresql.org

Re: [PERFORM] Tablespaces and NFS

2007-09-20 Thread Peter Koczan

> Anyway...  One detail I don't understand --- why do you claim that
> "You can't take advantage of the shared file system because you can't
> share tablespaces among clusters or servers" ???

I say that because you can't set up two servers to point to the same
tablespace (i.e. you can't have server A and server B both point to
the tablespace in /mnt/nfs/postgres/), which basically defeats one of
the main purposes of using a shared file system, seeing, using, and
editing files from anywhere.

This is ill-advised and probably won't work for 2 reasons.

- Postgres tablespaces require empty directories to for
initialization. If you create a tablespace on server A, it puts files
in the previously empty directory. If you then try to create a
tablespace on server B pointing to the same location, it won't work
since the directory is no longer empty. You can get around this, in
theory, but you'd either have to directly mess with system tables or
fool Postgres into thinking that each server independently created
that tablespace (to which anyone will say, NO).

- If you do manage to fool postgres into having two servers pointing
at the same tablespace, the servers really, REALLY won't play nice
with these shared resources, since they have no knowledge of each
other (i mean, two clusters on the same server don't play nice with
memory). Basically, if they compete for the same file, either I/O will
be EXTREMELY slow because of file-locking mechanisms in the file
system, or you open things up to race conditions and data corruption.
In other words: BAD

I know this doesn't fully apply to you, but I thought I should explain
my points betters since you asked so nicely :-)

> This seems to be the killer point --- mainly because the network
> connection is a 100Mbps  (around 10 MB/sec --- less than 1/4 of
> the performance we'd expect from an internal hard drive).  If at
> least it was a Gigabit connection, I might still be tempted to
> retry the experiment.  I was thinking that *maybe* the latencies
> and contention due to heads movements (in the order of the millisec)
> would take precedence and thus, a network-distributed cluster of
> hard drives would end up winning.

If you get decently fast disks, or put some slower disks in RAID 10,
you'll easily get >100 MB/sec (and that's a conservative estimate).
Even with a Gbit network, you'll get, in theory 128 MB/sec, and that's
assuming that the NFS'd disks aren't a bottleneck.

> We're clear that that would be the *optimal* solution --- problem
> is, there's a lot of client-side software that we would have to
> change;  I'm first looking for a "transparent" solution in which
> I could distribute the load at a hardware level, seeing the DB
> server as a single entity --- the ideal solution, of course,
> being the use of tablespaces with 4 or 6 *internal* hard disks
> (but that's not an option with our current web hoster).

I sadly don't know enough networking to tell you tell the client
software "no really, I'm over here." However, one of the things I'm
fond of is using a module to store connection strings, and dynamically
loading said module on the client side. For instance, with Perl I
use...

use DBI;
use DBD::Pg;
use My::DBs;

my $dbh = DBI->connect($My::DBs::mydb);

Assuming that the module and its entries are kept up to date, it will
"just work." That way, there's only 1 module to change instead of n
client apps. I can have a new server with a new name up without
changing any client code.

> Anyway, I'll keep working on alternative solutions --- I think
> I have enough evidence to close this NFS door.

That's probably for the best.

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

   http://archives.postgresql.org

[PERFORM] sequence query performance issues

2007-09-27 Thread Peter Koczan

Hello,

I have a weird performance issue with a query I'm testing. Basically,
I'm trying to port a function that generates user uids, and since
postgres offers a sequence generator function, I figure I'd take
advantage of that. Basically, I generate our uid range, filter out
those which are in use, and randomly pick however many I need.
However, when I run it it takes forever (>10 minutes and I get nothing
so I cancelled the query) and cpu usage on the server is maxed out.

Here's my query (I'll post the explain output later so as not to
obscure my question):
=> select a.uid from generate_series(1000, 32767) as a(uid) where
a.uid not in (select uid from people) order by random() limit 1;

I thought that nulls were a problem, so I tried:
=> select a.uid from generate_series(1000, 32767) as a(uid) where
a.uid not in (select coalesce(uid,0) from people) order by random()
limit 1;
And that finished in less than a second.

I then tried:
=> select a.uid from generate_series(1000, 32767) as a(uid) where
a.uid not in (select coalesce(uid,0) from people where uid is not
null) order by random() limit 1;
And we're back to taking forever.

So I have 2 questions:

- Is there a better query for this purpose? Mine works when coalesced,
but it seems a little brute-force and the random() sorting, while
kinda nice, is slow.

- Is this in any way expected? I know that nulls sometimes cause
problems, but why is it taking forever even when trying to filter
those out?

Thanks.

Peter

The gory details:
- There is an btree index on people(uid), and there are ~6300 rows, of
which ~1300 have null uids.

- EXPLAIN output (I couldn't get EXPLAIN ANALYZE output from the first
two queries since they took too long):
=> explain select a.uid from generate_series(1000, 32767) as a(uid)
where a.uid not in (select uid from people) order by random() limit 1;
QUERY PLAN
--
 Limit  (cost=40025.57..40025.60 rows=10 width=4)
   ->  Sort  (cost=40025.57..40026.82 rows=500 width=4)
 Sort Key: random()
 ->  Function Scan on generate_series a
(cost=693.16..40003.16 rows=500 width=4)
   Filter: (NOT (subplan))
   SubPlan
 ->  Materialize  (cost=693.16..756.03 rows=6287 width=2)
   ->  Seq Scan on people  (cost=0.00..686.87
rows=6287 width=2)
(8 rows)

=> explain select a.uid from generate_series(1000, 32767) as a(uid)
where a.uid not in (select uid from people where uid is not null)
order by random() limit 1;
QUERY PLAN
--
 Limit  (cost=31486.71..31486.73 rows=10 width=4)
   ->  Sort  (cost=31486.71..31487.96 rows=500 width=4)
 Sort Key: random()
 ->  Function Scan on generate_series a
(cost=691.79..31464.29 rows=500 width=4)
   Filter: (NOT (subplan))
   SubPlan
 ->  Materialize  (cost=691.79..741.00 rows=4921 width=2)
   ->  Seq Scan on people  (cost=0.00..686.87
rows=4921 width=2)
 Filter: (uid IS NOT NULL)
(9 rows)

=> explain select a.uid from generate_series(1000, 32767) as a(uid)
where a.uid not in (select coalesce(uid, 0) from people) order by
random() limit 1;
   QUERY PLAN

 Limit  (cost=756.97..756.99 rows=10 width=4)
   ->  Sort  (cost=756.97..758.22 rows=500 width=4)
 Sort Key: random()
 ->  Function Scan on generate_series a  (cost=718.30..734.55
rows=500 width=4)
   Filter: (NOT (hashed subplan))
   SubPlan
 ->  Seq Scan on people  (cost=0.00..702.59 rows=6287 width=2)
(7 rows)

=> explain analyze select a.uid from generate_series(1000, 32767) as
a(uid) where a.uid not in (select coalesce(uid, 0) from people) order
by random() limit 1;
   QUERY PLAN
-
 Limit  (cost=756.97..756.99 rows=10 width=4) (actual
time=370.444..370.554 rows=10 loops=1)
   ->  Sort  (cost=756.97..758.22 rows=500 width=4) (actual
time=370.434..370.472 rows=10 loops=1)
 Sort Key: random()
 ->  Function Scan on generate_series a  (cost=718.30..734.55
rows=500 width=4) (actual time=70.018..199.540 rows=26808 loops=1)
   Filter: (NOT (hashed subplan))
   SubPlan
 ->  Seq Scan on people  (cost=0.00..702.59 rows=6287
width=2) (actual time=0.023..29.167 rows=6294 loops=1)
 Total runtime: 372.224 ms
(8 rows)

---

Re: [PERFORM] sequence query performance issues

2007-09-28 Thread Peter Koczan

> > Hmm - why is it doing that?
>
> I'm betting that the OP's people.uid column is not an integer.  Existing
> PG releases can't use hashed subplans for cross-data-type comparisons
> (8.3 will be a bit smarter).

*light bulb* Ahhh, that's it. So, I guess the solution is either
to cast the column or wait for 8.3 (which isn't a problem since the
port won't be done until 8.3 is released anyway).

Thanks again.

Peter

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

   http://archives.postgresql.org

[PERFORM] Non-blocking vacuum full

2007-09-28 Thread Peter Schuller

Hello,

I was wondering whether any thought has previously been given to
having a non-blocking "vacuum full", in the sense of space reclamation
and table compactation.

The motivation is that it is useful to be able to assume that
operations that span a table will *roughtly* scale linearly with the
size of the table. But when you have a table that over an extended
period of time begins small, grows large, and grows small again (where
"large" might be, say, 200 GB), that assumption is most definitely
not correct when you're on the downward slope of that graph. Having
this assumption remain true simplifies things a lot for certain
workloads (= my particular work load ;)).

I have only looked very very briefly at the PG code so I don't know
how far fetched it is, but my thought was that it should be possible
to have a slow background process (similar to normal non-full vacuums
nows) that would, instead of registering dead tuples in the FSM, move
live tuples around.

Combine that slow moving operations with a policy to a new tuple space
allocation policy that prefers earlier locations on-disk, it should in
time result in a situation where the physical on-disk file contains
only dead tuples after a certain percentage location. At this point
the file can be truncated, giving space back to the OS as well as
eliminating all that dead space from having to be covered by
sequential scans on the table.

This does of course increase the total cost of all updates and
deletes, but would be very useful in some senarios. It also has the
interesting property that the scan for live tuples to move need not
touch the entire table to be effective; it could by design be applied
to the last  percentage of the table, where  would be scaled
appropriately with the frequency of the checks relative to
update/insert frequency.

Other benefits:

  * Never vacuum full - EVER. Not even after discovering too small
max_fsm_pages or too infrequent vacuums and needing to retroactively
shrink the table.
  * Increased locality in general; even if one does not care about
the diskspace or sequential scanning. Particularly relevant for low-update 
frequency
tables suffering from sudden shrinkage, where a blocking VACUUM FULL Is not
acceptable.
  * Non-blocking CLUSTER is perhaps suddently more trivial to implement?
Or at least SORTOFCLUSTER when you want it for reasons other than
perfect order ("mostly sorted").

Opinions/thoughts?

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <[EMAIL PROTECTED]>'
Key retrieval: Send an E-Mail to [EMAIL PROTECTED]
E-Mail: [EMAIL PROTECTED] Web: http://www.scode.org



pgpFbOXmSf908.pgp
Description: PGP signature

Re: [PERFORM] sequence query performance issues

2007-10-01 Thread Peter Koczan

> *light bulb* Ahhh, that's it. So, I guess the solution is either
> to cast the column or wait for 8.3 (which isn't a problem since the
> port won't be done until 8.3 is released anyway).

Just a quick bit of follow-up:

This query works and is equivalent to what I was trying to do (minus
the randomization and limiting):
=> select a.uid from generate_series(1000, 32000) as a(uid) where
a.uid::smallint not in (select uid from people where uid is not null);

It turns out that this and using coalesce are a wash in terms of
performance, usually coming within 10 ms of each other no matter what
limit and ordering constraints you put on the queries.

Peter

=> explain analyze select a.uid from generate_series(1000, 32767) as
a(uid) where a.uid not in (select coalesce(uid, 0) from people);
 QUERY PLAN
-
 Function Scan on generate_series a  (cost=718.41..733.41 rows=500
width=4) (actual time=68.742..186.340 rows=26808 loops=1)
   Filter: (NOT (hashed subplan))
   SubPlan
 ->  Seq Scan on people  (cost=0.00..702.68 rows=6294 width=2)
(actual time=0.025..28.368 rows=6294 loops=1)
 Total runtime: 286.311 ms
(5 rows)

=> explain analyze select a.uid from generate_series(1000, 32767) as
a(uid) where a.uid::smallint not in (select uid from people where uid
is not null);
 QUERY PLAN
-
 Function Scan on generate_series a  (cost=699.34..716.84 rows=500
width=4) (actual time=58.508..177.683 rows=26808 loops=1)
   Filter: (NOT (hashed subplan))
   SubPlan
 ->  Seq Scan on people  (cost=0.00..686.94 rows=4958 width=2)
(actual time=0.017..23.123 rows=4971 loops=1)
   Filter: (uid IS NOT NULL)
 Total runtime: 277.699 ms
(6 rows)

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

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

Re: [PERFORM] Memory Settings....

2007-10-22 Thread Peter Koczan

I recently tweaked some configs for performance, so I'll let you in on
what I changed.

For memory usage, you'll want to look at shared_buffers, work_mem, and
maintenance_work_mem. Postgres defaults to very low values of this,
and to get good performance and not a lot of disk paging, you'll want
to raise those values (you will need to restart the server and
possibly tweak some memory config for lots of shared_buffers, I had to
raise SHMMAX on Linux, but I don't know the Windows analogue). The
basic rule of thumb for shared_buffers is 25%-50% of main memory,
enough to use main memory but leaving some to allow work_mem to do its
thing and allow any other programs to run smoothly. Tweak this as
necessary.

The other big thing is the free space map, which tracks free space and
helps to prevent index bloat. A VACUUM VERBOSE in a database will tell
you what these values should be set to.

Go here for full details:
http://www.postgresql.org/docs/8.2/static/runtime-config.html, especially
http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html

Peter

On 10/22/07, Lee Keel <[EMAIL PROTECTED]> wrote:
>
>
>
> I have a client server that is dedicated to being a Postgres 8.2.4 database
> server for many websites.  This server will contain approximately 15
> databases each containing between 40-100 tables.  Each database will have
> approximately 7 web applications pulling data from it, but there will
> probably be no more than 50 simultaneous requests.  The majority of the
> tables will be very small tables around 1K in total size.  However, most of
> the queries will be going to the other 10-15 tables that are in each
> database that will contain postgis shapes.  These tables will range in size
> from 50 to 730K rows and each row will range in size from a 2K to 3MB.  The
> data will be truncated and reinserted as part of a nightly process but other
> than that, there won't be many writes during the day.  I am trying to tune
> this server to its maximum capacity.  I would appreciate any advice on any
> of the settings that I should look at.  I have not changed any of the
> settings before because I have never really needed to.  And even now, I have
> not experienced any bad performance, I am simply trying to turn the track
> before the train gets here.
>
> Server Specification:
>
> Windows 2003 Enterprise R2
>
> Dual-Quad Core 2.33GHz
>
> 8GB RAM
>
> 263 GB HD (I am not 100% on drive speed, but I think it is 15K)
>
>
> Thanks in advance,
>
> Lee Keel
>
>  This email and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to whom they are addressed.
> If you have received this email in error please notify the sender. This
> message contains confidential information and is intended only for the
> individual named. If you are not the named addressee you should not
> disseminate, distribute or copy this e-mail.

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [PERFORM] pg_dump and pg_restore

2010-05-22 Thread Peter Koczan

On Mon, May 17, 2010 at 12:04 AM, Jayadevan M
 wrote:
> Hello all,
> I was testing how much time a pg_dump backup would take to get restored.
> Initially, I tried it with psql (on a backup taken with pg_dumpall). It took
> me about one hour. I felt that I should target for a recovery time of 15
> minutes to half an hour. So I went through the blogs/documentation etc and
> switched to pg_dump and pg_restore. I tested only the database with the
> maximum volume of data (about 1.5 GB). With
> pg_restore -U postgres -v -d PROFICIENT --clean -Fc proficient.dmp
> it took about 45 minutes. I tried it with
> pg_restore -U postgres -j8 -v -d PROFICIENT --clean -Fc proficient.dmp
> Not much improvement there either. Have I missed something or 1.5 GB data on
> a machine with the following configuration will take about 45 minutes? There
> is nothing else running on the machine consuming memory or CPU. Out of 300
> odd tables, about 10 tables have millions of records, rest are all having a
> few thousand records at most.
>
> Here are the specs  ( a pc class  machine)-
>
> PostgreSQL 8.4.3 on i686-pc-linux-gnu
> CentOS release 5.2
> Intel(R) Pentium(R) D CPU 2.80GHz
> 2 GB RAM
> Storage is local disk.
>
> Postgresql parameters (what I felt are relevant) -
> max_connections = 100
> shared_buffers = 64MB
> work_mem = 16MB
> maintenance_work_mem = 16MB
> synchronous_commit on

Do the big tables have lots of indexes? If so, you should raise
maintenance_work_mem.

Peter

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Add slowdown after conversion to UTF8

2010-06-17 Thread Peter Eisentraut

On tor, 2010-06-17 at 18:28 -0400, Brant Fitzsimmons wrote:
> Performance has dropped through the floor after converting my db from
> ASCI to UTF8.

Converting from ASCII to UTF8 is a noop.

If you did some configuration changes, you need to tell us which.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Questions on query planner, join types, and work_mem

2010-07-27 Thread Peter Hussey

I have spent the last couple of weeks digging into a Postgres performance
problem that ultimately boiled down to this:  the planner was choosing to
use hash joins on a set of join keys that were much larger than the
configured work_mem.  We found we could make the  performance much better by
either
1) increasing work_mem to 500MB or more, or
2) forcing the planner to choose index-backed nested loops by turning off
hash and merge joins as well as bitmap and sequential scans.

Now we are trying to decide which of these paths to choose, and asking why
the planner doesn't handle this for us.

Background:  LabKey builds an open source platform for biomedical research
data.  The platform consists of a tomcat web application and a relational
database.  we support two databases, Postgres and SQL Server.  We started
with SQL Server because we were very familiar with it.  Two of our technical
team came from the SQL Server development team.  We chose Postgres because
we assessed that it was the open source database most likely to be able to
handle our application  requirements for capacity and complex, nested,
generated SQL handling.  Postgres is now the default database for our
platform and most of our key customers use it.  In general we've been very
satisfied with Postgres' performance and compatibility, but our customers
are starting to hit situations where we really need to be able to understand
why a particular operation is slow.  We are currently recommending version
8.4 and using that ourselves.

The core of the problem query was

SELECT * INTO snapshot_table FROM
  (SELECT ... FROM  tableA A LEFT  OUTER JOIN tableB B ON (A.lsid = B.lsid)
and A.datasetid = ? )  query1

the join column, lsid, is a poor choice for a join column as it is a long
varchar value (avg length 101 characters) that us only gets unique way out
on the right hand side.  But we are stuck with this choice.  I can post the
SQL query and table definitions if it will help, but changes to either of
those would be risky and difficult, whereas setting the work_mem value or
forcing nested loop joins is less risky.

The Performance curve looks something like this

Join Type  work_mem(MB) time to populate snapshot (min)
__
Hash  5085
Hash  200   38
Hash  400   21
Hash  500   12
Hash 1000   12
___
NestedLoop5015
NestedLoop200   11
NestedLoop400   11
NestedLoop500   10
NestedLoop   1000   10


Table A contains about 3.5 million rows, and table B contains about 4.4
million rows.  By looking at the EXPLAIN ANALYZE reports I concluded that
the planner seemed to be accurately determining the approximate number of
rows returned on each side of the join node.  I also noticed that at the
work_mem = 50 test, the hash join query execution was using over a GB of
space in the pgsql_tmp, space that grew and shrank slowly over the course of
the test.

Now for the questions:
1)  If we tell the customer to set his work_mem value to 500MB or 1GB in
postgres.config, what problems might they see?  the documentation and the
guidelines we received from Rupinder Singh in support suggest a much lower
value, e.g. a max work_mem of 10MB.  Other documentation such as the "Guide
to Posting Slow Query Questions" suggest at least testing up to 1GB.  What
is a reasonable maximum to configure for all connnections?

2) How is work_mem used by a query execution?  For example, does each hash
table in an execution get allocated a full work_mem's worth of memory ?   Is
this memory released when the query is finished, or does it stay attached to
the connection or some other object?

3) is there a reason why the planner doesn't seem to recognize the condition
when the hash table won't fit in the current work_mem, and choose a
low-memory plan instead?

Excuse the long-winded post; I was trying to give the facts and nothing but
the facts.

Thanks,
Peter Hussey
LabKey Software

Re: [PERFORM] Questions on query planner, join types, and work_mem

2010-08-02 Thread Peter Hussey

I already had effective_cache_size set to 500MB.

I experimented with lowering  random_page_cost to 3 then 2.  It made no
difference in the choice of plan that I could see.  In the explain analyze
output the estimated costs of nested loop were in fact lowererd, but so were
the costs of the hash join plan, and the hash join remained the lowest
predicted costs in all tests i tried.

What seems wrong to me is that the hash join strategy shows almost no
difference in estimated costs as work_mem goes from 1MB to 500MB. The cost
function decreases by 1%, but the actual time for the query to execute
decreases by 86% as work_mem goes from 1MB to 500MB.

My questions are still
1)  Does the planner have any component of cost calculations based on the
size of work_mem, and if so why do those calculations  seem to have so
little effect here?

2) Why is the setting of work_mem something left to the admin and/or
developer?  Couldn't the optimizer say how much it thinks it needs to build
a hash table based on size of the keys and estimated number of rows?

It is difficult for a software development platform like ours to take
advantage of suggestions to set work_mem, or to change the cost function, or
turn on/off join strategies for individual queries.  The SQL we issue is
formed by user interaction with the product and rarely static.  How would we
know when to turn something on or off?  That's why I'm looking for a
configuratoin solution that I can set on a database-wide basis and have it
work well for all queries.

thanks
Peter

On Fri, Jul 30, 2010 at 7:03 AM, Tom Lane  wrote:

> Peter Hussey  writes:
> > Using the default of 1MB work_mem, the planner chooses a hash join plan :
> > "Hash Left Join  (cost=252641.82..11847353.87 rows=971572 width=111)
> (actual
> > time=124196.670..280461.604 rows=968080 loops=1)"
> > ...
> > For the same default 1MB work_mem, a nested loop plan is better
> > "Nested Loop Left Join  (cost=8.27..15275401.19 rows=971572 width=111)
> > (actual time=145.015..189957.023 rows=968080 loops=1)"
> > ...
>
> Hm.  A nestloop with nearly a million rows on the outside is pretty
> scary.  The fact that you aren't unhappy with that version of the plan,
> rather than the hash, indicates that the "object" table must be
> fully cached in memory, otherwise the repeated indexscans would be a
> lot slower than this:
>
> > "  ->  Index Scan using uq_object on object obj  (cost=0.00..3.51 rows=1
> > width=95) (actual time=0.168..0.170 rows=1 loops=968080)"
> > "Index Cond: ((sd.lsid)::text = (obj.objecturi)::text)"
>
> My take on it is that the estimate of the hash plan's cost isn't bad;
> what's bad is that the planner is mistakenly estimating the nestloop as
> being worse.  What you need to do is adjust the planner's cost
> parameters so that it has a better idea of the true cost of repeated
> index probes in your environment.  Crank up effective_cache_size if
> you didn't already, and experiment with lowering random_page_cost.
> See the list archives for more discussion of these parameters.
>
>regards, tom lane
>

-- 
Peter Hussey
LabKey Software
206-667-7193 (office)
206-291-5625 (cell)

Re: [PERFORM] help tuning queries on large database

2006-01-09 Thread peter royal


On Jan 9, 2006, at 2:01 PM, Luke Lonergan wrote:

Peter,

On 1/9/06 9:23 AM, "peter royal" <[EMAIL PROTECTED]> wrote:


This is a 2-disk RAID0


Your 2-disk results look fine - what about your 8-disk results?


after some further research the 2-disk RAID0 numbers are not bad.

I have a single drive of the same type hooked up to the SATA2 port on  
the motherboard to boot from, and its performance numbers are (linux  
2.6.15, ext3):


[EMAIL PROTECTED] ~]# time bash -c 'dd if=/dev/zero of=/tmp/bigfile bs=8k  
count=100 && sync'

100+0 records in
100+0 records out

real4m55.032s
user0m0.256s
sys 0m47.299s
[EMAIL PROTECTED] ~]# time dd if=/tmp/bigfile bs=8k of=/dev/null
100+0 records in
100+0 records out

real3m27.229s
user0m0.156s
sys 0m13.377s

so, there is a clear advantage to RAID over a single drive.


now, some stats in a 8-disk configuration:

8-disk RAID0, ext3, 16k read-ahead

[EMAIL PROTECTED] /opt/pgdata]# time bash -c 'dd if=/dev/zero of=/opt/ 
pgdata/bigfile bs=8k count=100 && sync'

100+0 records in
100+0 records out

real0m53.030s
user0m0.204s
sys 0m42.015s

[EMAIL PROTECTED] /opt/pgdata]# time dd if=/opt/pgdata/bigfile bs=8k of=/ 
dev/null

100+0 records in
100+0 records out

real0m23.232s
user0m0.144s
sys 0m13.213s


8-disk RAID0, xfs, 16k read-ahead

[EMAIL PROTECTED] /opt/pgdata]# time bash -c 'dd if=/dev/zero of=/opt/ 
pgdata/bigfile bs=8k count=100 && sync'

100+0 records in
100+0 records out

real0m32.177s
user0m0.212s
sys 0m21.277s

[EMAIL PROTECTED] /opt/pgdata]# time dd if=/opt/pgdata/bigfile bs=8k of=/ 
dev/null

100+0 records in
100+0 records out

real0m21.814s
user0m0.172s
sys 0m13.881s


... WOW.. highly impressed with the XFS write speed! going to stick  
with that!


Overall, I got a 50% boost in the overall speed of my test suite by  
using XFS and the 16k read-ahead.


Given that you want to run in production with RAID10, the most you  
should
expect is 2x the 2-disk results using all 8 of your disks.  If you  
want the

best rate for production while preserving data integrity, I recommend
running your Areca in RAID5, in which case you should expect 3.5x your
2-disk results (7 drives).  You can assume you'll get that if you  
use XFS +

readahead.  OTOH - I'd like to see your test results anyway :-)


I've been avoiding RAID5 after reading how performance drops when a  
drive is out/rebuilding. The performance benefit will outweigh the  
cost I think.


Thanks for the help!
-pete

--
(peter.royal|osi)@pobox.com - http://fotap.org/~osi



smime.p7s
Description: S/MIME cryptographic signature

Re: [PERFORM] Postgres slower than MS ACCESS

2006-02-16 Thread Peter Childs

On 15/02/06, Jay Greenfield <[EMAIL PROTECTED]> wrote:
I've been vacuuming between each test run.Not vacuuming results in times all the way up to 121 minutes.  For a directcomparison with Access, the vacuuming time with Postgres should really beincluded as this is not required with Access.


Hmm but then you would have to include Access Vacuum too I'll think you
will find "Tools -> Database Utils -> Compact Database" preforms
a simular purpose and is just as important as I've seen many Access
Databases bloat in my time.

Peter Childs

Re: [PERFORM] Large Table With Only a Few Rows

2006-02-27 Thread Peter Childs

On 27/02/06, Chris Browne <[EMAIL PROTECTED]> wrote:
"Nik" <[EMAIL PROTECTED]> writes:> I have a table that has only a few records in it at the time, and they> get deleted every few seconds and new records are inserted. Table never
> has more than 5-10 records in it.>> However, I noticed a deteriorating performance in deletes and inserts> on it. So I performed vacuum analyze on it three times (twice in a row,> and once two days later). In the statistics it says that the table size
> is 863Mb, toast table size is 246Mb, and indexes size is 134Mb, even> though the table has only 5-10 rows in it it. I was wondering how can I> reclaim all this space and improve the performance?
You need to run VACUUM ANALYZE on this table very frequently.Based on what you describe, "very frequently" should be on the orderof at least once per minute.Schedule a cron job specifically to vacuum this table, with a cron
entry like the following:* * * * * /usr/local/bin/vacuumdb -z -t my_table -p 5432 my_databaseOf course, you need to bring it back down to size, first.You could run CLUSTER on the table to bring it back down to size;
that's probably the fastest way... cluster my_table_pk on my_table;VACUUM FULL would also do the job, but probably not as quickly.--(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://cbbrowne.com/info/sgml.html"Now they can put you in jail if they *THINK* you're gonna commit acrime. Let me say that again, because it sounds vaguely important"
--george carlin---(end of broadcast)---TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not
match
You probably want to do one or two other things.

1> Switch on autovacuum.

2> improve the setting of max_fsm_pages in your postgresql.conf a restart will be required.

if you do a "vacuum verbose;" the last couple of lines should tell you
how much free space is about against how much free space the database
can actuall remember to use.

INFO: free space map contains 5464 pages in 303 relations
DETAIL: A total of 9760 page slots are in use (including overhead).
9760 page slots are required to track all free space.
Current limits are: 4 page slots, 1000 relations, using 299 KB.

if the required page slots (9760 in my case) goes above the current
limit (4 in my case) you will need to do a vacuum full to reclaim
the free space. (cluster of the relevent tables may work.

If you run Vacuum Verbose regullally you can check you are vacuuming
often enough and that your free space map is big enough to hold your
free space.

Peter Childs

[PERFORM] Index scan startup time

[Apologies if this already went through.  I don't see it in the archives.]

Normally one expects that an index scan would have a startup time of nearly 
zero.  Can anyone explain this:

EXPLAIN ANALYZE select activity_id from activity where state in (1, 10001) 
order by activity_id limit 100;

QUERY PLAN

Limit  (cost=0.00..622.72 rows=100 width=8) (actual 
time=207356.054..207356.876 rows=100 loops=1)
  ->  Index Scan using activity_pk on activity  (cost=0.00..40717259.91 
rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100 loops=1)
Filter: ((state = 1) OR (state = 10001))
Total runtime: 207357.000 ms

The table has seen VACUUM FULL and REINDEX before this.

The plan choice and the statistics look right, but why does it take 3 minutes 
before doing anything?  Or is the measurement of the actual start time 
inaccurate?  This is quite reproducible, so it's not just a case of a 
temporary I/O bottleneck, say.

(PostgreSQL 8.0.3)

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 1: 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: [PERFORM] Index scan startup time

Am Donnerstag, 30. März 2006 14:02 schrieb Steinar H. Gunderson:
> On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote:
> > EXPLAIN ANALYZE select activity_id from activity where state in (1,
> > 10001) order by activity_id limit 100;
> >
> > QUERY PLAN
> >
> > Limit  (cost=0.00..622.72 rows=100 width=8) (actual
> > time=207356.054..207356.876 rows=100 loops=1)
> >   ->  Index Scan using activity_pk on activity  (cost=0.00..40717259.91
> > rows=6538650 width=8) (actual time=207356.050..207356.722 rows=100
> > loops=1) Filter: ((state = 1) OR (state = 10001))
> > Total runtime: 207357.000 ms
> >
> > The table has seen VACUUM FULL and REINDEX before this.
>
> The index scan is by activity_id, not by state. Do you have an index on
> state at all?

There is an index on state as well but the column is not selective enough.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Re: [PERFORM] Index scan startup time

Am Donnerstag, 30. März 2006 14:06 schrieb Michael Stone:
> On Thu, Mar 30, 2006 at 01:59:10PM +0200, Peter Eisentraut wrote:
> >The table has seen VACUUM FULL and REINDEX before this.
>
> But no analyze?

ANALYZE as well, but the plan choice is not the point anyway.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

   http://archives.postgresql.org

Re: [PERFORM] Index scan startup time

Am Donnerstag, 30. März 2006 14:31 schrieb Steinar H. Gunderson:
> Well, it's logical enough; it scans along activity_id until it finds one
> with state=1 or state=10001. You obviously have a _lot_ of records with
> low activity_id and state none of these two, so Postgres needs to scan all
> those records before it founds 100 it can output. This is the “startup
> cost” you're seeing.

The startup cost is the cost until the plan is set up to start outputting 
rows.  It is not the time until the first row is found.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Re: [PERFORM] Index scan startup time

Tom Lane wrote:
> The problem here appears to be a non-random correlation between state
> and activity, such that the desired state values are not randomly
> scattered in the activity sequence.  The planner doesn't know about
> that correlation and hence can't predict the poor startup time.

So from when to when is the startup time (the "x" in "x..y") actually 
measured?  When does the clock start ticking and when does it stop?  
That is what's confusing me.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

[PERFORM] Poor performance - fixed by restart

2006-06-21 Thread Peter Wilson


I've recently configured a new high-performance database server:
2xXeon 3.4G, 2G RAM, 4x15K SCSI disks in RAID 10, h/w RAID

This has been live for a couple of weeks.

The box is running Fedora Core 4.

The only thing running on this box is PostgreSQL 8.1.4 and some stub 
applications that handle the interface to Postgres (basically taking XML service 
requests, translating into SQL and using libpq). The database is a backend for a 
big web application. The web-server and processor intensive front-end run on a 
separate server.


Postgres has probably been running for 2 weeks now.

I've just uploaded a CSV file that the web-application turns into the contents 
into multiple requests to the database. Each row in the CSV file causes a few 
transactions to fire. Bascially adding rows into a couple of table. The tables 
at the moment aren't huge (20,000 rows in on, 150,000 in the other).


Performance was appalling - taking 85 seconds to upload the CSV file and create 
the records. A separate script to delete the rows took 45 seconds. While these 
activities were taking place the Postgres process was using 97% CPU on the 
server - nothing else much running.


For comparison, my test machine (750M Athlon, RedHat 8, 256M RAM, single IDE 
hard drive) created the records in 22 seconds and deleted them again in 17.


I had autovacuum ON - but to make sure I did first a vacuum analyze (no 
difference) then vacuum full (again no difference).


I'd tweaked a couple of parameters in postgres.conf - the significant one I 
thought being random_page_cost, so I changed this back to default and did a 
'service postgresql reload' - no difference, but I wasn't sure whether this 
could be changed via reload so I restarted Postgres.


The restart fixed the problem. The 85 second insert time dropped back down to 5 
seconds!!!


To check whether the random_page_cost was making the difference I restored the 
old postgres.conf, restarted postgres and redid the upload. Rather suprisingly - 
 the upload time was still at 5 seconds.


Any thoughts? I find it hard to believe that Postgres performance could degrade 
over a couple of weeks. Read performance seemed to be fine. The postgres memory 
size didn't seem to be huge. What else am I overlooking? What could I have 
changed by simply restarting Postgres that could make such a drastic change in 
performance?


Pete

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

Re: [PERFORM] increment Rows in an SQL Result Set postgresql

2006-07-15 Thread Peter Eisentraut

Hassan Adekoya wrote:
> I will like to preserve ordering

Tables are inherently unordered.  If you want a particular order, you 
need to use the ORDER BY clause.  And you will need to have a column to 
sort by.  If you don't have one, the generate_series() function may 
help.

This has nothing to do with performance, I gather, so it might be more 
appropriate for the pgsql-sql list.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Re: [PERFORM] Forcing using index instead of sequential scan?

2006-07-21 Thread Peter Eisentraut

[EMAIL PROTECTED] wrote:
> What is the best way to force the use of indexes in these queries?

Well, the brute-force method is to use SET enable_seqscan TO off, but if 
you want to get to the bottom of this, you should look at or post the 
EXPLAIN ANALYZE output of the offending queries.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

[PERFORM] PostgreSQL runs a query much slower than BDE and MySQL

2006-08-16 Thread Peter Hardman

I'm in the process of migrating a Paradox 7/BDE 5.01 database from single-user 
Paradox to a web based interface to either MySQL or PostgreSQL.
The database is a pedigree sheep breed society database recording sheep and 
flocks (amongst other things).

My current problem is with one table and an associated query which takes 10 
times longer to execute on PostgreSQL than BDE, which in turn takes 10 times 
longer than MySQL. The table links sheep to flocks and is created as follows:

CREATE TABLE SHEEP_FLOCK
(
  regn_no varchar(7) NOT NULL,
  flock_no varchar(6) NOT NULL,
  transfer_date date NOT NULL,
  last_changed date NOT NULL,
  CONSTRAINT SHEEP_FLOCK_pkey PRIMARY KEY (regn_no, flock_no, 
transfer_date)
) 
WITHOUT OIDS;
ALTER TABLE SHEEP_FLOCK OWNER TO postgres;

I then populate the table with 

COPY SHEEP_FLOCK
FROM 'e:/ssbg/devt/devt/export_data/sheep_flock.txt'
WITH CSV HEADER

The table then has about 82000 records

The query I run is:

/* Select all sheep who's most recent transfer was into the subject flock */
SELECT DISTINCT f1.regn_no, f1.transfer_date as date_in
FROM SHEEP_FLOCK f1 JOIN 
/* The last transfer date for each sheep */
(SELECT f.regn_no, MAX(f.transfer_date) as last_xfer_date
FROM  SHEEP_FLOCK f
GROUP BY f.regn_no) f2 
ON f1.regn_no = f2.regn_no
WHERE f1.flock_no = '1359'
AND f1.transfer_date = f2.last_xfer_date

The sub-select on it's own returns about 32000 rows.

Using identically structured tables and the same primary key, if I run this on 
Paradox/BDE it takes about 120ms, on MySQL (5.0.24, local server) about 3ms, 
and on PostgresSQL (8.1.3, local server) about 1290ms). All on the same 
Windows XP Pro machine with 512MB ram of which nearly half is free.  

The query plan shows most of the time is spent sorting the 3+ rows from the 
subquery, so I added a further
subquery as follows: 

/* Select all sheep who's most recent transfer was into the subject flock */
SELECT DISTINCT f1.regn_no, f1.transfer_date as date_in
FROM SHEEP_FLOCK f1 JOIN 
/* The last transfer date for each sheep */
(SELECT f.regn_no, MAX(f.transfer_date) as last_xfer_date
FROM  SHEEP_FLOCK f
WHERE f.regn_no IN 
/* Limit the rows extracted by the outer sub-query to those relevant to 
the 
subject flock */
/* This typically reduces the time from 1297ms to 47ms - from 35000 
rows 
to 127 rows */
(SELECT s.regn_no FROM SHEEP_FLOCK s where s.flock_no = '1359')
GROUP BY f.regn_no) f2 
ON f1.regn_no = f2.regn_no
WHERE f1.flock_no = '1359'
AND f1.transfer_date = f2.last_xfer_date

then as the comment suggests I get a considerable improvement, but it's still 
an 
order of magnitude slower than MySQL.

Can anyone suggest why PostgreSQL performs the original query so much slower 
than even BDE?
 -- 
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==


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

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

Re: [PERFORM] PostgreSQL runs a query much slower than BDE and MySQL

2006-08-16 Thread Peter Hardman

On 16 Aug 2006 at 20:02, Arjen van der Meijden wrote:

> On 16-8-2006 18:48, Peter Hardman wrote:
> > Using identically structured tables and the same primary key, if I run this 
> > on 
> > Paradox/BDE it takes about 120ms, on MySQL (5.0.24, local server) about 
> > 3ms, 
> > and on PostgresSQL (8.1.3, local server) about 1290ms). All on the same 
> > Windows XP Pro machine with 512MB ram of which nearly half is free.  
> 
> Is that with or without query caching? I.e. can you test it with SELECT 
> SQL_NO_CACHE ... ?
> In a read-only environment it will still beat PostgreSQL, but as soon as 
> you'd get a read-write environment, MySQL's query cache is of less use. 
> So you should compare both the cached and non-cached version, if applicable.
It seems to make no difference - not surprising really as I'm just running the 
query 
from the command line interface.
> 
> Besides that, most advices on this list are impossible without the 
> result of 'explain analyze', so you should probably get that as well.
Here is the output of EXPLAIN ANALYZE for the slow query:

Unique  (cost=7201.65..8487.81 rows=1 width=13) (actual 
time=1649.733..1811.684 rows=32 loops=1)
  ->  Merge Join  (cost=7201.65..8487.80 rows=1 width=13) (actual 
time=1649.726..1811.528 rows=32 loops=1)
Merge Cond: ((("outer".regn_no)::text = "inner"."?column3?") AND 
("outer".transfer_date = "inner".last_xfer_date))
->  Index Scan using sheep_flock_pkey on sheep_flock f1  
(cost=0.00..1033.19 rows=77 width=13) (actual time=15.357..64.237 rows=127 
loops=1)
  Index Cond: ((flock_no)::text = '1359'::text)
->  Sort  (cost=7201.65..7285.84 rows=33676 width=15) (actual 
time=1580.198..1653.502 rows=38277 loops=1)
  Sort Key: (f2.regn_no)::text, f2.last_xfer_date
  ->  Subquery Scan f2  (cost=0.00..4261.67 rows=33676 width=15) 
(actual 
time=0.331..598.246 rows=38815 loops=1)
->  GroupAggregate  (cost=0.00..3924.91 rows=33676 
width=13) 
(actual time=0.324..473.131 rows=38815 loops=1)
  ->  Index Scan using sheep_flock_pkey on sheep_flock 
f  
(cost=0.00..3094.95 rows=81802 width=13) (actual time=0.295..232.156 
rows=81802 loops=1)
Total runtime: 1812.737 ms


> 
> I'm not sure whether this is the same query, but you might want to try:
> SELECT DISTINCT f1.regn_no, f1.transfer_date as date_in
> FROM SHEEP_FLOCK f1
> WHERE
> f1.flock_no = '1359'
> AND f1.transfer_date = (SELECT MAX(f.transfer_date) FROM SHEEP_FLOCK f 
> WHERE regn_no = f1.regn_no)
> 
That's neat - I didn't know you could make a reference from a subselect to the 
outer select. Your query has the same performance as my very complex one on 
both MySQL and PostgreSQL. However I'm not entirely sure about the times for 
MySQL - every interface gives a different answer so I'll have to try them from 
a 
script so I know whats going on.
Interestingly BDE takes 7 seconds to run your query. Just as well I didn't 
start 
from there... 
> And you might need an index on (regn_no, transfer_date) and/or one 
> combined with that flock_no.
Explain says it only uses the primary key, so it seems there' no need for a 
separate index

Thanks for the help
-- 
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==


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

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

Re: [PERFORM] PostgreSQL runs a query much slower than BDE and MySQL

On 17 Aug 2006 at 10:00, Mario Weilguni wrote:

> not really sure if this is right without any testdata, but isn't that what 
> you 
> want?
> 
> CREATE index foo on sheep_flock (flock_no);
> 
> SELECT DISTINCT on (f1.transfer_date) f1.regn_no, f1.transfer_date as date_in
> FROM SHEEP_FLOCK f1
> WHERE f1.flock_no = '1359'
> order by f1.transfer_date desc;
> 
> best regards, 
> mario weilguni
> 
> 
Mario, Thanks for the suggestion, but this query produces the wrong answer - 
but 
then I provided no data, nor properly explained what the data would be.
Each sheep will have multiple records, starting with one for when it's first 
registered, then one for each flock it's in (eg sold into) then one for when it 
dies 
and goes to the 'big flock in the sky'.

 So first I need to find the most recent record for each sheep and then select 
the 
sheep who's most recent record matches the flock in question.

Your query finds all the sheep that have been in the flock in question, then 
selects 
the first one from each set of records with the same date. So it collects data 
on 
dead sheep, and only selects one sheep if several were bought or registered on 
the same day.

Forgive me for being verbose - I want to make sure I understand it propely 
myself!

regards, 
 -- 
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==

---(end of broadcast)---
TIP 1: 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: [PERFORM] PostgreSQL runs a query much slower than BDE and MySQL

On 16 Aug 2006 at 18:51, Tom Lane wrote:

> "Peter Hardman" <[EMAIL PROTECTED]> writes:
> > I'm in the process of migrating a Paradox 7/BDE 5.01 database from 
> > single-user 


Arjen van der Meijden has proposed a very elegant query in another post. 

> What I find interesting though is that it sounds like both MSSQL and
> Paradox know something we don't about how to optimize it.  PG doesn't
> have any idea how to do the above query without forming the full output
> of the sub-select, but I suspect that the commercial DBs know a
> shortcut; perhaps they are able to automatically derive a restriction
> in the subquery similar to what you did by hand.  Does Paradox have
> anything comparable to EXPLAIN that would give a hint about the query
> plan they are using?

Sadly, no. In fact the ability to use SQL from Paradox at all is not well known 
and 
not very visible in the the documentation. 

I wonder whether Paradox and MySQL are just not doing the sort (this seems to 
be what eats up the time), since the output of the subquery is in fact already 
in the 
proper order.

> 
> Also, just as in the other thread, I'm thinking that a seqscan+hash
> aggregate would be a better idea than this bit:
> 
> >->  GroupAggregate  (cost=0.00..3924.91 rows=33676 
> > width=13) (actual time=0.324..473.131 rows=38815 loops=1)
> >  ->  Index Scan using sheep_flock_pkey on 
> > sheep_flock f (cost=0.00..3094.95 rows=81802 width=13) (actual 
> > time=0.295..232.156)
> 
> Possibly you need to raise work_mem to get it to consider the hash
> aggregation method.
> 
> BTW, are you *sure* you are testing PG 8.1?  The "Subquery Scan f2" plan
> node looks unnecessary to me, and I'd have expected 8.1 to drop it out.
> 8.0 and before would have left it in the plan though.  This doesn't make
> all that much difference performance-wise in itself, but it does make me
> wonder what you are testing.

Yes, the executables all say version 8.1.3.6044
> 
Regards,-- 
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==


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

Re: [PERFORM] PostgreSQL runs a query much slower than BDE and MySQL

On 17 Aug 2006 at 12:11, Markus Schaber wrote:

> Hi, Peter,
> 
> Peter Hardman wrote:
> 
> >> BTW, are you *sure* you are testing PG 8.1?  The "Subquery Scan f2" plan
> >> node looks unnecessary to me, and I'd have expected 8.1 to drop it out.
> >> 8.0 and before would have left it in the plan though.  This doesn't make
> >> all that much difference performance-wise in itself, but it does make me
> >> wonder what you are testing.
> > 
> > Yes, the executables all say version 8.1.3.6044
> 
> Would you mind to look at the output of "select version();", too?
> 
> I ask this because I stumbled over it myself, that I had installed the
> correct postgresql and psql versions, but accidentally connected to a
> different database installation due to strange environment and script
> settings...
select version() returns

PostgreSQL 8.1.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 
(mingw-special)

Cheers,-- 
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==


---(end of broadcast)---
TIP 1: 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: [PERFORM] PostgreSQL runs a query much slower than BDE and MySQL

On 16 Aug 2006 at 17:48, Peter Hardman wrote:

> I'm in the process of migrating a Paradox 7/BDE 5.01 database from 
> single-user 
> Paradox to a web based interface to either MySQL or PostgreSQL.

I've uploaded my data to www.shetland-sheep.org.uk/pgdata/sheep-flock.zip

The flock SSBXXX is the 'big flock in the sky' and thus there should never be 
any 
date for a sheep greater than this. 

Yes, the primary key is regn_no + flock_no + transfer_date.

Thanks again for all the help and advice.

Regards,-- 
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==

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

Re: [PERFORM] PostgreSQL runs a query much slower than BDE and MySQL

On 17 Aug 2006 at 14:33, Tom Lane wrote:

> I wrote:
> > Anywy, your point about the sort being redundant is a good one, and
> > offhand I'd have expected PG to catch that; I'll have to look into
> > why it didn't.  But that's not going to explain a 10x speed
> > difference, because the sort isn't 90% of the runtime.
> 
> I dug into this using some made-up test data, and was able to reproduce
> the plan you got after changing the order of the pkey index columns
> to (regn_no, transfer_date, flock_no) ... are you sure you quoted that
> accurately before?

Yes. Maybe the data I've uploaded to www.shetland-
sheep.org.uk/pgdata/sheep_flock.zip will help reproduce the plan.

 
> I found a couple of minor planner problems, which I've repaired in CVS
> HEAD.  You might consider using TEXT columns instead of VARCHAR(n),
> because the only bug that actually seemed to change the chosen plan
> involved the planner getting confused by the difference between
> varchar_var and varchar_var::text (which is what gets generated for
> sorting purposes because varchar doesn't have a separate sort operator).

As someone else suggested, these fields ought really to be CHAR no VARCHAR. 
I chose VARCHAR because the data mostly is shorter than the maximum lengths 
(although probably not enough to matter). I'd not really got into the 
subtleties of 
different behaviour of CHAR and VARCHAR.
> 
 

Regards,-- 
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT

== Breeder of Shetland Cattle and Shetland Sheep ==


---(end of broadcast)---
TIP 1: 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: [PERFORM] PostgreSQL runs a query much slower than BDE and MySQL