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
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]
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
wouldn't be here.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
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
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
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
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
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
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
.
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
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
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
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
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
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
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
.
--
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])
- 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
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
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
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
). 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
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
. 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]
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
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
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
..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
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
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
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
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
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
versions.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
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
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
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
- 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
:
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
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
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
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
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]
::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
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
. 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
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
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
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
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
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
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
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
://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
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
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
.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
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
- 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
mess that up much though.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
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]
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])
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
, 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
.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
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
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
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
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
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
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
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
:
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
(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
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
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
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
...
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
.
--
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])
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])
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
,
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
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
;
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
. 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
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
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
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
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
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
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
' 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
. 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
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
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
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
.
Can you provide the definition?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
to be wrong?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
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 - 100 of 456 matches
Mail list logo