Re: [PERFORM] Tunning FreeeBSD and PostgreSQL

2003-07-14 Thread Richard Huxton
approaching this. Any help or direction would be greatly appreciated. There are two articles recently posted here: http://www.varlena.com/GeneralBits/ They should provide a good start. -- Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9

Re: [PERFORM] factoring problem with view in 7.3.3

2003-07-23 Thread Richard Huxton
about an operator =$ if you miss the spaces around the =. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] PostgreSQL performance problem - tuning

2003-08-14 Thread Richard Huxton
On Thursday 07 August 2003 17:30, Yaroslav Mazurak wrote: Hi All! First, thanks for answers! Richard Huxton wrote: On Thursday 07 August 2003 09:24, Yaroslav Mazurak wrote: IIRC there is a limit on filesystem cache on freeBSD. 300MB by default. If that is the case, you might have

Re: [PERFORM] PostgreSQL performance problem - tuning

2003-08-14 Thread Richard Huxton
wouldn't be here. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] PostgreSQL performance problem - tuning

2003-08-14 Thread Richard Huxton
the document at: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Performance problems on a fairly big table with two key columns.

2003-09-05 Thread Richard Huxton
change during the statement). Alternatively, you can do the calculation in the application and use an explicit time. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] [GENERAL] Seq scan of table?

2003-09-05 Thread Richard Huxton
should find plenty of discussion of why in the archives, but the short reason is that PG's type structure is quite flexible which means it can't afford to make too many assumptions. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5

Re: [PERFORM] [GENERAL] Seq scan of table?

2003-09-05 Thread Richard Huxton
On Friday 05 September 2003 19:20, Neil Conway wrote: On Fri, 2003-09-05 at 06:07, Richard Huxton wrote: PG's parser will assume an explicit number is an int4 - if you need an int8 etc you'll need to cast it, yes. Or enclose the integer literal in single quotes. You should find plenty

Re: [PERFORM] pgsql inserts problem

2003-08-28 Thread Richard Huxton
might be to write a short Perl script to reproduce the problem. Without that, people are likely to be sceptical - if PG tended to do this sort of thing, none of us would use it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2

Re: [PERFORM] Need advice about triggers

2003-09-09 Thread Richard Huxton
v7.3.4, shared_buffers=4096 max_fsm settings also bumped up 10 times. Well effective_cache_size is useful for reads, but won't help with writing. You might want to look at wal_buffers and see if increasing that helps, but I couldn't say for sure. -- Richard Huxton Archonet Ltd

Re: [PERFORM] A Basic Question

2003-10-03 Thread Richard Huxton
. But get's slow when it returns zero tuple. Now how shud I got abt it. If PG has to examine a lot of tuples to rule them out, then returning no rows can take longer. If you post EXPLAIN ANALYSE output for both queries, someone will be able to explain why in your case. -- Richard Huxton Archonet

Re: [PERFORM] [SQL] sql performance and cache

2003-10-11 Thread Richard Huxton
it at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php Given the explain attached, 16 secs seems slow. Could you post an EXPLAIN ANALYSE of either/both queries to the performance list. I'd drop the sql list when we're just talking about performance. -- Richard Huxton Archonet Ltd

Re: [PERFORM] [SQL] sql performance and cache

2003-10-11 Thread Richard Huxton
its got. If we reach the statistics tinkering stage, it might be better to wait til Monday if you can - more people on the list then. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose

Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread Richard Huxton
by apache/java. If your database grows radically, you'll probably want to re-tune as it grows. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Problem with insert into select...

2003-11-24 Thread Richard Huxton
always been because I've got an unconstrained join due to pilot error. Try an EXPLAIN on the select part and see if that pops up anything. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ

Re: [PERFORM] expression (functional) index use in joins

2003-11-26 Thread Richard Huxton
Scan on program p (cost=0.00..15192.35 rows=4335 width=20) planner results on 7.3.4: - Index Scan using idx_program_mri_id_no_program on program p (cost=0.00..3209.16 rows=870 width=20) -- Richard Huxton Archonet Ltd ---(end of broadcast

Re: [PERFORM] Followup - expression (functional) index use in joins

2003-11-26 Thread Richard Huxton
4335 rows, then this might be a more sensible plan. First step is to run: VACUUM ANALYSE program; Then, check the definition of your function fn_mri_id_no_program() and make sure it is marked immutable/stable (depending on what it does) and that it's returning a varchar. -- Richard Huxton

Re: [PERFORM] tuning questions

2003-12-04 Thread Richard Huxton
. -- Richard Huxton Archonet Ltd ---(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] Slow UPADTE, compared to INSERT

2003-12-04 Thread Richard Huxton
- that's something odd. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] tuning questions

2003-12-04 Thread Richard Huxton
According to this your cache is currently 1,899,856 KB which in 8KB blocks is 237,482 - be frugal and say effective_cache_size = 20 (or even 15 if the trace above isn't typical). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-05 Thread Richard Huxton
are using the index. Well - I must admit I'm stumped. Unless you have a *lot* of indexes and foreign keys to check, I can't see why it would take so long to update a single row. Can you post the schema for the table? -- Richard Huxton Archonet Ltd ---(end

Re: [PERFORM] Nested loop question

2003-12-17 Thread Richard Huxton
of the manuals). I'm not sure that will help you here though. The fact that it's taking you 9ms to do each index lookup suggests to me that it's going to disk each time. Does that sound plausible, or do you think you have enough RAM to cache your large indexes? -- Richard Huxton Archonet

Re: [PERFORM] Slow query problem

2004-01-09 Thread Richard Huxton
). Then it should not need to sort at all to do the grouping and it should all be fast. Not sure if that would make a difference here, since the whole table is being read. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched

Re: [PERFORM] freebsd 5.2 and max_connections

2004-01-13 Thread Richard Huxton
distribution and shouldn't cost you too much to keep accurate, since I'm guessing a low rate of updating. You might want to play with the random page cost (?or is it random access cost?) but more RAM for a bigger disk cache is probably the simplest tweak. -- Richard Huxton Archonet Ltd

Re: [PERFORM] Trigger question

2004-01-15 Thread Richard Huxton
. do the same, but write the trigger function in 'C' -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] shared_buffer value

2004-01-15 Thread Richard Huxton
that are used in the UPDATE statements. A REINDEX might be worthwhile. Details on this and VACUUM in the manuals. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan

Re: [PERFORM] Trigger question

2004-01-20 Thread Richard Huxton
that. If anyone wants this feature, I'd encourage them to step up to the plate -- I'm not sure when I'll get the opportunity/motivation to implement this myself. I didn't think they'd be meaningful for a statement-level trigger. Surely OLD/NEW are by definition row-level details. -- Richard

Re: [PERFORM] Trigger question

2004-01-20 Thread Richard Huxton
2. step through one row at a time, doing something. I suppose there might be cases where you'd want to GROUP BY... which would mean you'd need some oid/row-id added to a real recordset. -- Richard Huxton Archonet Ltd ---(end of broadcast

Re: [PERFORM] Queries with timestamps

2004-01-21 Thread Richard Huxton
..0.79 rows=0 loops=1) Index Cond: (log_ts ((('now'::text)::date - '7 days'::interval))::timestamp with time zone) Total runtime: 1.03 msec (3 rows) It seems to help an accurate estimate of number-of-rows if you put an upper and lower limit in. -- Richard Huxton Archonet Ltd

Re: [PERFORM] Explain plan for 2 column index

2004-01-29 Thread Richard Huxton
as timestamp(6) - why the different accuracies. Also, note that 'now' is deprecated - now() or CURRENT_TIMESTAMP/DATE/etc are preferred. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map

Bulk Record upload (was Re: [PERFORM] Increasing number of PG connections)

2004-02-02 Thread Richard Huxton
on statement logging for PG and then we can see what commands your GUI is sending. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Database conversion woes...

2004-02-03 Thread Richard Huxton
corrects all problems.Not sure how convenient that'll be with dozens of gigs of data. Might be practical to start with the smaller databases, let your script grow in capabilities before importing the larger ones. -- Richard Huxton Archonet Ltd ---(end of broadcast

Re: [PERFORM] Database conversion woes...

2004-02-03 Thread Richard Huxton
of guy. When this is over and you've got the time, I don't suppose you could put together a few hundred words describing your experiences with the Mammoth replicator - there are a couple of places they could be posted. -- Richard Huxton Archonet Ltd ---(end

Re: [PERFORM] Index Performance Help

2004-02-05 Thread Richard Huxton
config settings, hardware, number of clients etc... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] cacheable stored functions?

2004-02-20 Thread Richard Huxton
versions. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] A cache for the results of queries ?

2004-02-26 Thread Richard Huxton
a precalculated selection_in_cache, especially when selection_in_cache is a very long list of joins... You might want to search the archives for the -sql list for a message Materialized View Summary - some time this week. That's almost exactly what you want. -- Richard Huxton Archonet Ltd

Re: [PERFORM] Select-Insert-Query

2004-02-27 Thread Richard Huxton
shouldn't take any time in itself, although if you are sorting then PG may need to sort all the rows before discarding all except the first 99. If this new query is no better, make sure you have vacuum analyse'd the tables and post the output of EXPLAIN ANALYSE for the query. -- Richard Huxton

Re: [PERFORM] WAL Optimisation - configuration and usage

2004-02-28 Thread Richard Huxton
this is sensible - you may be compensating for some other parameter out-of-range. -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] syslog slowing the database?

2004-03-09 Thread Richard Huxton
- was PG logging a lot of info, or is some other application the culprit? Tip: put a minus - in front of the file-path in your syslog.conf and it won't sync to disk after every entry. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6

Re: [PERFORM] atrocious update performance

2004-03-17 Thread Richard Huxton
: 1. Do the stats tables record FK checks, or just explicit table accesses? 2. If not, should they? If the only real activity is this update then simple before/after views of the stats might be revealing. -- Richard Huxton Archonet Ltd ---(end of broadcast

Re: [PERFORM] two seperate queries run faster than queries ORed together

2004-03-22 Thread Richard Huxton
did have several hundred thousand matches then a seq-scan might be sensible. I'd start by analyze-ing the table in question, and if that doesn't have any effect look at the column stats and see what spread of values it thinks you have. -- Richard Huxton Archonet Ltd

Re: [PERFORM] Help with query plan inconsistencies

2004-03-24 Thread Richard Huxton
case is so much faster, I suspect the data wasn't cached at the beginning of this run. In any case #2 is faster than #1. If the planner is getting things wrong, you're not showing it here. -- Richard Huxton Archonet Ltd ---(end of broadcast

Re: [PERFORM] [ADMIN] Raw vs Filesystem

2004-03-30 Thread Richard Huxton
provided in the archives, but whether they apply to your setup is open to argument. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] [ADMIN] Raw vs Filesystem

2004-03-30 Thread Richard Huxton
for better performance. I think SuSE offer Reiser though, so maybe we'll see a wider selection available by default. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] select slow?

2004-03-31 Thread Richard Huxton
::smallint; By default, PG will treat a numeric constant as integer not smallint, so when it looks for an index it can't find one for integer, so scans instead. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our

Re: [PERFORM] What index for 'like (%keyword%)' ???

2004-03-31 Thread Richard Huxton
anyone give me some advise on what kind of index I can use here? Or shouldn't I use one in this case? You probably want to look at the contrib/tsearch2 full-text indexing module. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1

[PERFORM] Back to Linux 2.6 kernel thoughts...

2004-04-06 Thread Richard Huxton
. Morton has experienced up to 15 percent increases on database loads while using deadline scheduling. http://story.news.yahoo.com/news?tmpl=storycid=75e=2u=/nf/20040405/tc_nf/23603 Nothing very in-depth in the story. -- Richard Huxton Archonet Ltd ---(end of broadcast

Re: [PERFORM] good pc but bad performance,why?

2004-04-07 Thread Richard Huxton
corrupted. If you are happy the possibility if losing your data, write performance will improve noticably. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] plan problem

2004-04-07 Thread Richard Huxton
On Wednesday 07 April 2004 10:03, Ken Geis wrote: Richard Huxton wrote: On Tuesday 06 April 2004 21:25, Ken Geis wrote: I am trying to find an efficient way to draw a random sample from a complex query. I also want it to be easy to use within my application. So I've defined a view

Re: [PERFORM] select count(*) very slow on an already vacuumed table.

2004-04-14 Thread Richard Huxton
though: 1. Is this the actual query, or just a representation? 2. Do you need an accurate figure or just something near enough? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] select count(*) very slow on an already vacuumed table.

2004-04-15 Thread Richard Huxton
for it to become reasonable fast. If you've only got one disk, then a second disk for OS/logging. Difficult to say more without knowing numbers of users/activity etc. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked

Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already

2004-04-15 Thread Richard Huxton
and copy how it does it. -- Richard Huxton Archonet Ltd ---(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

Re: [PERFORM] [OFF-TOPIC] - Known maximum size of the PostgreSQL

2004-05-06 Thread Richard Huxton
happy. I think it's safe to assume this is not on a spare Dell 600SC though. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] LIKE and INDEX

2004-05-05 Thread Richard Huxton
index??? I've seen people define a reverse(text) function via plperl or similar then build a functional index on reverse(url). Of course, that would rely on your knowing which end of your search pattern has the % wildcard. -- Richard Huxton Archonet Ltd ---(end

Re: [PERFORM] PostgreSQL caching

2004-05-21 Thread Richard Huxton
://www.varlena.com/varlena/GeneralBits/Tidbits/index.php 2. Post a sample query/explain analyse that runs very slowly when not cached. 3. If needs be, you can write a simple timed script that performs a query. Or, the autovacuum daemon might be what you want. -- Richard Huxton Archonet Ltd

Re: [PERFORM] select max(id) from aTable is very slow

2004-06-03 Thread Richard Huxton
of PG's aggregate function system, it can't see inside the max() function to realise it doesn't need all the values. Fortune favours the flexible however - the simple workaround is to use the equivalent: SELECT id FROM theTable ORDER BY id DESC LIMIT 1; -- Richard Huxton Archonet Ltd

Re: [PERFORM] [SQL] Materialized View Summary

2004-06-07 Thread Richard Huxton
in all cases. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] Major differences between oracle and postgres performance

2004-06-18 Thread Richard Huxton
. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] memory allocation

2004-06-18 Thread Richard Huxton
to remember is that the sort_mem is the amount of memory available *per sort* and some queries can use several sorts. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] after using pg_resetxlog, db lost

2004-06-22 Thread Richard Huxton
- would there be any value in adding this to a pg_dump? I'm assuming the numbers attached to tables etc are their OIDs anyway, so it might be a useful reference in cases like this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5

Re: [PERFORM] postgres 7.4 at 100%

2004-06-25 Thread Richard Huxton
mess that up much though. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Query performance

2004-06-29 Thread Richard Huxton
queries. Presumably from yesterday back, the ratios/averages won't change. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Query performance

2004-06-29 Thread Richard Huxton
that one day at a time. Then #2,#3 would be easier. -- Richard Huxton Archonet Ltd ---(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] [GENERAL] How to know which queries are to be optimised?

2004-08-12 Thread Richard Huxton
grows awefully big after a short time. You might want to look at the Practical Query Analyser - haven't used it myself yet, but it seems a sensible idea. http://pqa.projects.postgresql.org/ -- Richard Huxton Archonet Ltd ---(end of broadcast

Re: [PERFORM] Weird Database Performance problem!

2004-08-13 Thread Richard Huxton
, the primary-key side will already have an index being used as part of the constraint. I've cc:ed the list on this, the question pops up quite commonly. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free

Re: [PERFORM] using an index worst performances

2004-08-19 Thread Richard Huxton
. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] What is the best way to do attribute/values?

2004-08-24 Thread Richard Huxton
Not pretty, but might give you the speed you want. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message

Re: [PERFORM] Optimizer Selecting Incorrect Index

2004-08-26 Thread Richard Huxton
Dennis Bjorklund wrote: On Wed, 25 Aug 2004, Richard Huxton wrote: These queries are different. The first returns 687 rows and the second 713 rows. The 687 and 713 are the number of rows in the plan, not the number of rows the queries return. D'OH! Thanks Dennis -- Richard Huxton Archonet

Re: [PERFORM] [ADMIN] slower every day

2004-09-01 Thread Richard Huxton
G u i d o B a r o s i o wrote: Conclusion: If you comment a line on the conf file, and reload it, will remain in the last state. (either wast true or false, while I expected a default) Yes, that's correct. No, you're not the only one to have been caught out by this. -- Richard Huxton

Re: [PERFORM] View Query Performance

2004-10-15 Thread Richard Huxton
apply further conditions, e.g. SELECT * FROM my_view WHERE id = 123; then you should see any index on id being used. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http

Re: [PERFORM] [pgsql-hackers-win32] Poor Performance for large queries

2004-09-29 Thread Richard Huxton
that many rows, you wouldn't want to use the index - it would mean fetching values twice. The only work-around if you are using plpgsql functions is to use EXECUTE to make sure your queries are planned for each value provided. -- Richard Huxton Archonet Ltd ---(end

Re: [PERFORM] inconsistent/weird index usage

2004-10-01 Thread Richard Huxton
by serial_num, name, day order by serial_num, day desc Try restricting the timestamp too WHERE ts BETWEEN (current_date -7) AND current_timestamp Hopefully that will give the planner enough smarts to know it can skip most of the sample_200x tables. -- Richard Huxton Archonet Ltd

Re: [PERFORM] index not used if using IN or OR

2004-11-04 Thread Richard Huxton
the times with and without, and don't forget to account for the effects of caching. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: FW: [PERFORM] FW: Index usage

2004-11-24 Thread Richard Huxton
: 1. How many rows do you have in chatlogs? 2. Is this the only problem you are experiencing, or just one from many? 3. Have you tuned any configuration settings? e.g. as suggested in: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -- Richard Huxton Archonet Ltd

Re: [PERFORM] Postgres vs. MySQL

2004-11-24 Thread Richard Huxton
(and why) then we might be able to help, otherwise there's not much information here. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Postgres backend using huge amounts of ram

2004-11-26 Thread Richard Huxton
exceeded config-file limits. If you could reproduce a simple test case I'm sure someone would be interested in squashing this bug. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL

Re: [PERFORM] Normalization or Performance

2004-12-02 Thread Richard Huxton
for this (I know, the Dell issue), a Dual Xeon 2.8, SCSI HD, 1 GB mem. Do we need better hardware for our system? Swap one of your processors for more RAM and disks, perhaps. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you

Re: [PERFORM] Seqscan rather than Index

2004-12-17 Thread Richard Huxton
to consider is the block size, but people have tried increasing this in the past with only marginal success. Must admit this puzzles me. Are you saying you can't saturate your disk I/O? Or are you saying other DBMS store records in 0.5 to 0.2 times less space than PG? -- Richard Huxton Archonet Ltd

Re: [PERFORM] Postgres on Linux Cluster!

2004-12-21 Thread Richard Huxton
... Probably not, and almost certainly not. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Performance delay

2005-01-13 Thread Richard Huxton
. -- Richard Huxton Archonet Ltd ---(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] sum of all values

2005-01-14 Thread Richard Huxton
be you want to alter your database design. -- Richard Huxton Archonet Ltd ---(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] sum of all values

2005-01-14 Thread Richard Huxton
Madison Kelly wrote: Richard Huxton wrote: Madison Kelly wrote: Hi all, Is there a fast(er) way to get the sum of all integer values for a certain condition over many thousands of rows? What I am currently doing is this (which takes ~5-10sec.): OK, I'm assuming you've configured PG to your

Re: [PERFORM] Increasing RAM for more than 4 Gb. using postgresql

2005-01-19 Thread Richard Huxton
, remember to backup *all* the directories. -- Richard Huxton Archonet Ltd ---(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] [SQL] OFFSET impact on Performance???

2005-01-20 Thread Richard Huxton
if the OFFSET is bigger. How can I somehow improve the performance on this? There's really only one way to do an offset of 1000 and that's to fetch 1000 rows and then some and discard the first 1000. If you're using this to provide pages of results, could you use a cursor? -- Richard Huxton Archonet Ltd

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-20 Thread Richard Huxton
; CLOSE mycursor; Repeated FETCHes would let you step through your results. That won't work if you have a web-app making repeated connections. If you've got a web-application then you'll probably want to insert the results into a cache table for later use. -- Richard Huxton Archonet Ltd

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-20 Thread Richard Huxton
. Temp tables don't fsync 2. A cursor will spill to disk beyond a certain size -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-20 Thread Richard Huxton
Ron Mayer wrote: Richard Huxton wrote: If you've got a web-application then you'll probably want to insert the results into a cache table for later use. If I have quite a bit of activity like this (people selecting 1 out of a few million rows and paging through them in a web browser), would

Re: [PERFORM] [SQL] OFFSET impact on Performance???

2005-01-20 Thread Richard Huxton
results as people insert/delete rows. This might or might not be what you want. A similar solution is to partition by date/alphabet or similar, then page those results. That can reduce your resultset to a manageable size. -- Richard Huxton Archonet Ltd ---(end

Re: [PERFORM] Profiling a function...

2005-01-21 Thread Richard Huxton
it doesn't know the actual values. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] Possibly slow query

2005-01-26 Thread Richard Huxton
on how many of each. You're not going to know for sure whether you'll have problems without testing. Generate 500k rows of plausible looking test-data and give it a try. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you

Re: [PERFORM] Possibly slow query

2005-01-26 Thread Richard Huxton
Peter Darley wrote: Folks, I'm using PostgreSQL 7.4.1 on Linux Oh, and move to the latest in the 7.4 series too. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your

Re: [PERFORM] Very important choice

2005-02-01 Thread Richard Huxton
it takes to recover from a crash - you can run PostgreSQL on ext2, but checking a large disk can take hours after a crash. That's the real benefit of journalling for PG - speed of recovery. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7

Re: [PERFORM] Tunning postgresql on linux (fedora core 3)

2005-02-03 Thread Richard Huxton
' and complete documentation about this tweaks ... ? Try the performance tuning article linked from this page: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9

Re: [PERFORM] Tunning postgresql on linux (fedora core 3)

2005-02-03 Thread Richard Huxton
. It doesn't make sense to turn off the WAL. -- Richard Huxton Archonet Ltd ---(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

Re: [PERFORM] Tunning postgresql on linux (fedora core 3)

2005-02-03 Thread Richard Huxton
I'll repeat myself: Please CC the mailing list as well as replying to me, so that others can help too. Din Adrian wrote: On Thu, 03 Feb 2005 13:56:50 +, Richard Huxton dev@archonet.com wrote: Please CC the mailing list as well as replying to me, so that others can help too. b

Re: [PERFORM] How to interpret this explain analyse?

2005-02-11 Thread Richard Huxton
is the number of rows. If PG expects say 100 rows but there are instead 10,000 then it may choose the wrong plan. In this case the estimate is 1,100,836 and the actual is 1,104,380 - very close. -- Richard Huxton Archonet Ltd ---(end of broadcast

Re: [PERFORM] Help with tuning this query

2005-03-02 Thread Richard Huxton
the current_date - 31 as a suitable ago(31) function then you can use an index on cs.date 4. Are you familiar with the configuration setting join_collapse_limit? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your

Re: [PERFORM] bad plan

2005-03-08 Thread Richard Huxton
. Can you provide the definition? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] bad plan

2005-03-08 Thread Richard Huxton
to be wrong? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] What's better: Raid 0 or disk for seperate pg_xlog

2005-03-11 Thread Richard Huxton
that, use the tablespace feature to balance your read load as far as you can. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

  1   2   3   4   5   >