Re: [PERFORM] Substantial different index use between 9.5 and 9.6

Seems to be a replicable issue in PostGis - ticket raised at their end, so I'll wait for a resolution of the root cause. Thanks for your help/thoughts. Rgds Bill On 3/12/2016 2:41 AM, Daniel Blanch Bataller wrote: ANALYZE takes samples at random, so statistics might be different

Re: [PERFORM] Substantial different index use between 9.5 and 9.6

ple I gave uses the index, but I will take it up with them too. Rgds Bill On 2/12/2016 10:48 AM, Tom Lane wrote: Bill Measday writes: Substantial different index use between 9.5 and 9.6 Maybe you missed an ANALYZE after migrating? The plan difference seems to be due to a vast difference in r

[PERFORM] Substantial different index use between 9.5 and 9.6

e reason, 9.6 planner decides not to use the index for a small number of records returned from address_default_geocode. I have vacuum analysed both tables. Clearly a sequential scan on 10 billion records is pretty slow (to say the least). Has anyone seen anything like this/got any thoughts? I

Re: [PERFORM] Planner selects different execution plans depending on limit

Tom Lane <mailto:t...@sss.pgh.pa.us> writes: > Bill Martin writes: >> Tom Lane writes: >>> He can do it without having to change his schema --- but it's the >>> index column, not the underlying content column, that needs its >>> statistics targe

Re: [PERFORM] Planner selects different execution plans depending on limit

> Tom Lane writes: > He can do it without having to change his schema --- but it's the index > column, not the underlying content column, that needs its statistics > target adjusted. > regards, tom lane How can I adjust the statistics target of the index? -- Sent via p

Re: [PERFORM] Planner selects different execution plans depending on limit

> Tom Lane writes: >> Bill Martin writes: >> I've tried different values for the statistics but it is all the same (the >> planner decide to switch to a seqscan if the limit is 10). >> ALTER TABLE core_content ALTER column content SET STATISTICS 1000; >

Re: [PERFORM] Planner selects different execution plans depending on limit

Tom Lane writes: > Bill Martin writes: >> I´ve created following table which contains one million records. >> ... >> "Limit (cost=10091.09..19305.68 rows=3927 width=621) (actual >> time=0.255..0.255 rows=0 loops=1)" >> " -> Bitmap Heap Sca

Re: [PERFORM] Planner selects different execution plans depending on limit

(actual time=52147.149..52147.149 rows=0 loops=1)" " -> Seq Scan on core_content content (cost=0.00..98384.34 rows=20011 width=621) (actual time=52147.147..52147.147 rows=0 loops=1)" "Filter: (to_tsvector('simple'::regconfig, content) @@ '''asdasdadas'':*'::tsquery)" "Total runtime: 52147.173 ms" Is there any posibility to tune up the performance even if the limit is only 10? Is it possible to determine that the query optimizer takes only the fast bitmap heap scan instead of the slow seq scan? I use PostgreSQL 9.1.5.; Intel i5-2400 @ 3.1 GHz, 16GB; Windows 7 64 Bit Regards, Bill Martin

[PERFORM] Questions on plan with INSERT/SELECT on partitioned table

I have been trying to track down a performance issue we've been having with a INSERT INTO ... SELECT query run against a partitioned table on postgres. The problem appears to be in the plan building of the query and after some further research I think I have nailed down a simplified example of

Re: [PERFORM] Use of sequence rather than index scan for one text column on one instance of a database

s quiescent at the time? Regards, -Bill Kirtley On Sep 24, 2009, at 12:26 PM, Tom Lane wrote: Bill Kirtley writes: select xmin,* from pg_index where indexrelid = 'index_users_on_email'::regclass; xmin | indexrelid | indrelid | indnatts | indisunique | indisprimary

[PERFORM] Use of sequence rather than index scan for one text column on one instance of a database

(cleverly named email2) and copying the data (update users set email2=email) and adding the appropriate index and the query performed quickly. So we can fix the immediate problem, but I'd feel more comfortable understanding it. Do folks on this list have suggestions for how t

Re: [PERFORM] Slow insert performace, 8.3 Wal related?

Rusty Scott Marlowe wrote: On Thu, Jan 15, 2009 at 2:55 PM, Bill Preston wrote: Nothing special about that table. One index. It really seems that the system would grind to a stand-still when a lot of non-transaction inserts were run combined with the creation of some large temp tables. Sin

Re: [PERFORM] Slow insert performace, 8.3 Wal related?

nicely. The suggestions here really helped. Does anyone know of some established postgresql consultants that can be hired for emergency analysis/tuning when things come up? Rusty Alan Hodgson wrote: On Monday 12 January 2009, Bill Preston wrote: As to the second example with the delete. There

Re: [PERFORM] Slow insert performace, 8.3 Wal related?

nuary 2009, Bill Preston wrote: I had a data load that I was doing with 8.1. It involved about 250k sql statements that were inserts into a table with just one index. The index has two fields. With the upgrade to 8.3 that process started taking all night and 1/2 a day. It inserted at the r

[PERFORM] Slow insert performace, 8.3 Wal related?

Hi Group. Recently upgraded from 8.1 to 8.3 on RHEL 5 64-bit. I've noticed some performance problems that I am guessing are WAL related based on my browsing around and wondered if someone had some suggestions for tuning the WAL settings. It could also help if someone just laughed at me and tol

Re: [PERFORM] Postgres using more memory than it should

end up with this problem. 1G is probably too much memory to allocate for work_mem. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 IMPORTANT: Th

Re: [PERFORM] Improve COPY performance for large data sets

RAID-10 or similar, or somehow get a faster disk. You always have the option to turn off fsync, but be sure you understand the consequences of doing that and have an appropriate failure plan before doing so. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EM

Re: [PERFORM] select on 22 GB table causes "An I/O error occured while sending to the backend." exception

In response to Greg Smith <[EMAIL PROTECTED]>: > On Thu, 28 Aug 2008, Bill Moran wrote: > > > In linux, it's possible to tell the OOM killer never to consider > > certain processes for the axe, using /proc magic. See this page: > > http://linux-mm.org/OOM_Ki

Re: [PERFORM] select on 22 GB table causes "An I/O error occured while sending to the backend." exception

ertain processes for the axe, using /proc magic. See this page: http://linux-mm.org/OOM_Killer Perhaps this should be in the PostgreSQL docs somewhere? -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent v

Re: [PERFORM] select on 22 GB table causes "An I/O error occured while sending to the backend." exception

the solution to problems like these. Not to mention the historical data generally tells you months ahead of time when you're going to need to scale up to bigger hardware. On a side note, what version of PG are you using? If it was in a previous email, I missed it. Hope this helps. -- Bill

Re: [PERFORM] Big delete on big table... now what?

ou remove smaller groups of rows more frequently, you'll probably be able to maintain performance and table bloat at a reasonable level with normal vacuum. -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-performance mailing list (pgsq

Re: [PERFORM] Using PK value as a String

In response to Steve Atkins <[EMAIL PROTECTED]>: > > On Aug 12, 2008, at 8:21 AM, Bill Moran wrote: > > > In response to Moritz Onken <[EMAIL PROTECTED]>: > > > >> > >> Am 12.08.2008 um 17:04 schrieb Bill Moran: > >> > >>>

Re: [PERFORM] Using PK value as a String

In response to Moritz Onken <[EMAIL PROTECTED]>: > > Am 12.08.2008 um 17:04 schrieb Bill Moran: > > > In response to Moritz Onken <[EMAIL PROTECTED]>: > > > >> We chose UUID as PK because there is still some information in an > >> integer ke

Re: [PERFORM] Using PK value as a String

in a specific period of > time > (compare the id of the newest user to the id a week ago). This is > information > which is in some cases critical. So you're accidentally storing critical information in magic values instead of storing it explicitly? Good luck with that. --

Re: [PERFORM] Using PK value as a String

with a system ID (i.e. make the high 8 bits the system ID and the remaining bits come from a sequence) This allows you to still use int4 or int8. UUID is designed to be a universal solution. But universal solutions are frequently less efficient than custom-tailored solutions. -- Bill Moran

Re: [PERFORM] Difference between "Explain analyze" and "\timing"

lus any network delays and time required to process the query and result on the client side. -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 IMPORTANT: This message contains confidential info

Re: [PERFORM] how big shmmax is good for Postgres...

lable RAM" is meant the free RAM after all other applications are running, which will be 4G if this machine only runs PostgreSQL, but could be less if it runs other things like a web server. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED

Re: [PERFORM] max fsm pages question

aise the FSM settings to allow vacuum to work, then lower them back down when it's under control again. This is one of the few circumstances where you may want to VACUUM FULL. If you don't handle this, that table will continue to grow in size on the disk, taking up space unnecessarily an

Re: [PERFORM] How much work_mem to configure...

d to look at limiting it based on > how much memory your server has. I do have one thing to add: if you're using 8.3, there's a log_temp_files config variable that you can use to monitor when your sorts spill over onto disk. It doesn't change anything that Scott said, it simply give

Re: [PERFORM] Author Wanted

, and > if you have good ideas about relevant topics, please let me know. > > Please let me know, I'd be very happy if you could contribute... > (Don't worry about language!) > :-) Can you provide detailed information on submission guidelin

Re: [PERFORM] Problem with 11 M records table

G btree > ("IDLocker"); > > CREATE INDEX > idx_fk_lockerevents_idmoneysymbol_moneysymbols_id > ON "LockerEvents" > USING btree > ("IDMoneySymbol"); > > CREATE INDEX idx_fk_lockerevents_iduser_users_id > ON "LockerEvents" > USING btre

Re: [PERFORM] Background writer underemphasized ...

In response to Greg Smith <[EMAIL PROTECTED]>: > On Wed, 16 Apr 2008, Bill Moran wrote: > > >> bgwriter_delay = 1ms # 10-1ms between rounds > >> bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round > > Have you watched closely

Re: [PERFORM] Background writer underemphasized ...

pot at the values you mention. However, if that really is the demonstrable sweet spot, there may be something we all can learn. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-performance mailing lis

Re: [PERFORM] db size

anybody give me some advice on the above? I'm not sure where to > start looking or how to start looking Running VACUUM VERBOSE will give you a detailed view of space usage of each individual table. -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent

Re: [PERFORM] db size

expect that the pg_dump format is able to do more aggressive compression than the running database. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-performance mailing list (pgsql-performance@postgres

Re: [PERFORM] large tables and simple "= constant" queries using indexes

ostgreSQL's internals and see what tables are in the buffer in real time. If you're having trouble, it can (potentially) be a helpful tool. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Se

Re: [PERFORM] large tables and simple "= constant" queries using indexes

s (term) MATCH SIMPLE > > ON UPDATE NO ACTION ON DELETE NO ACTION, > > CONSTRAINT gene_prediction_view_gene_ref_key UNIQUE (gene_ref, go_id) > > ) > > WITH (OIDS=FALSE); > > ALTER TABLE gene_pre

Re: [PERFORM] Looking for bottleneck during load test

In response to Greg Smith <[EMAIL PROTECTED]>: > On Mon, 7 Apr 2008, Bill Moran wrote: > > > You know, with all the performance problems people have been bringing up > > with regard to SANs, I'm putting SAN in the same category as RAID-5 ... > > Not really

Re: [PERFORM] Looking for bottleneck during load test

mailpref/pgsql-performance > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.co

Re: [PERFORM] "Slow" query or just "Bad hardware"?

838,1377100,969316,1796618,1121046,4662009,963535,5302610,1121105,688700,688743,688836,688763,688788,1056859,2386006,2386015,2386023,4265832,4231262,4265743,5302612,1121056,11 21 > 090,1121074,688659,688650}'::integer[])) > -> Inde

Re: [PERFORM] postgresql is slow with larger table even it is in RAM

ems with the results you're getting, they're about what I would expect. Are you trying to compare PostgreSQL to MySQL/MyISAM? More directly, what is your purpose in starting this email conversation? What are you hoping to accomplish? -- Bill Moran Collaborative Fusion Inc. http://p

Re: [PERFORM] what is the maximum number of rows in a table in postgresql 8.1

; Total runtime: 6259.543 ms 6 seconds doesn't sound like an unreasonable amount of time to count 3 million rows. I don't see any performance issue here. What were your expectations? -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECT

Re: [PERFORM] postgresql is slow with larger table even it is in RAM

s and blocks, etc) so you have all that processing overhead as well. What filesystem did you format the RAM disk with? Why are you doing this? If you have enough RAM to store the table, why not just allocate it to shared buffers? -- Bill Moran Collaborative Fusion Inc. http://people.collabo

Re: [PERFORM] PG writes a lot to the disk

UNT. The real question (to verify Tom's point) is does a _second_ SELECT count() modify the table again? If so, then something else is going on than what Tom suggested. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-34

Re: [PERFORM] PG writes a lot to the disk

magine of a select that modifies database data, but it's hardly the only one. I suspect that the OP has procedures in his SELECTs that are modifying table data, or triggers that do it ON SELECT or something similar. Of course, without any details, this is purely speculation. -- Bill Moran

Re: [PERFORM] question on TRUNCATE vs VACUUM FULL

In response to "Mark Steben" <[EMAIL PROTECTED]>: > Bill, > Thanks for your quick response. > We are at version 8.2.5 - just recently upgraded from 7.4.5. > This strategy using truncate was just implemented yesterday. > Now I will revisit the vacuum full strate

Re: [PERFORM] PG writes a lot to the disk

of RAM, you might want to try bumping this and see if it helps without pushing the system into swap. If the problem is sort file usage, this is the option to tune it. > maintenance_work_mem = 4GB I doubt it's hurting anything, but I don't think a value this high will actually be used

Re: [PERFORM] question on TRUNCATE vs VACUUM FULL

action updating or inserting to Table A via the after > trigger (insert, update) > > - Function D (insert, update, delete) > > Table C is processed ONLY by function D (insert, update, delete). Nothing > else touches it; > > PG_LOCKS table verifies that that this table is t

Re: [PERFORM] count * performance issue

)). > > Sorry, if I am missing something here, but shouldn't something like this > allow us to get a (fast) accurate count ? > > SELECT COUNT(*) from table WHERE indexed_field IS NULL > + > SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL For certain, qualified d

Re: [PERFORM] count * performance issue

uickly available. Another is to use EXPLAIN to get an estimate of the # of rows from the planner. This works well if an estimate is acceptable, but can't be trusted for precise counts. Some searches through the archives should turn up details on these methods. -- Bill Moran Collaborative Fus

Re: [PERFORM] Toast space grows

autovacuum running, reduce those values. Personally, I'd recommend running a MRTG graph that graphs the size of this table so you can easily watch to see if your config tweaks are getting the job done or not. And remember that _some_ bloat is expected and normal for operation. -- Bill Mor

Re: [PERFORM] Toast space grows

s table - just inserts, lots of updates? > > > > 2. What does SELECT sum(length(log)) FROM dataaction; show? > > > > > > > Vacuum on the table doesn't finish. > > > > > > A plain vacuum doesn't finish, or vacuum full doesn't finis

Re: [PERFORM] count * performance issue

nd I don't hear anyone denying that. As I said, google around a bit WRT to PG storing visibility information in indexes, as I think that's the way this will be improved. > My rant for today... Feel better now? -- Bill Moran -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance

Re: [PERFORM] postgresql performance

a count column, or using explain to get a quick estimate of the number of rows (if that's acceptable). -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 *

Re: [PERFORM] PostgreSQL performance on a virtual host

; Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > > To make changes to your subscription: > > > > http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance > > > > -- > Sent via pgsql-performance mailing li

Re: [PERFORM] Performance tuning on FreeBSD

u need to be running the 7.X branch. Based on your pgbench results, I'm guessing you didn't get battery-backed cache on your systems? That makes a big difference no matter what OS you're using. Besides that, I can't think of any FreeBSD-specific things to do. Basically, genera

Re: [PERFORM] questions about CLUSTER

kes enough of a difference to be doing all of this work. > again, tia. i feel like such a noob around here :) Bah ... we all start out as noobs. Just don't go googling for my posts from years back, it's embarrassing ... -- Bill Moran Collaborative Fusion Inc. http://people.collabor

Re: [PERFORM] questions about CLUSTER

) > or > on the underlying index (index on lastname)? If cluster helps you at all, it's going to help if you have an index that's frequently used to fetch ranges of data. Whether that index is compound or not isn't likely to factor in. -- Bill Moran Collaborative Fusion

Re: [PERFORM] Example web access to Postgres DB

me a link where I can find a WAR file. I think you're going to have to be more specific. I don't know what technology uses WAR files, and based on the tepid response, it doesn't seem like anyone else on the list does either. What program are you using that uses the WAR file

Re: [PERFORM] Making the most of memory?

ke it's worth the effort, but if it's run 1x10^25 times a day it is. If the IO load of logging all queries presents too much of a slowdown, I recommend selecting data collection periods and do it for perhaps an hour, then turn it back off. Maybe once a week or so. Hope th

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

In response to Mark Mielke <[EMAIL PROTECTED]>: > Bill Moran wrote: > > In response to Mark Mielke <[EMAIL PROTECTED]>: > > > > > >> Bill Moran wrote: > >> > >>> I'm fairly sure that FreeBSD's GEOM does.

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

In response to Mark Mielke <[EMAIL PROTECTED]>: > Bill Moran wrote: > > > >> What do you mean "heard of"? Which raid system do you know of that reads > >> all drives for RAID 1? > >> > > > > I'm fairly sure that FreeBSD&#

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

In response to Mark Mielke <[EMAIL PROTECTED]>: > Bill Moran wrote: > > In order to recalculate the parity, it has to have data from all disks. > > Thus, > > if you have 4 disks, it has to read 2 (the unknown data blocks included in > > the parity calculation)

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

said, I have heard of raid1 setups where it only reads off of one > > of the drives, but I have not heard of higher raid levels doing so. > What do you mean "heard of"? Which raid system do you know of that reads > all drives for RAID 1? I'm fairly sure that FreeBSD&#x

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

es which don't have noticable seek > > times, things are completely different ;-) > > Ha, sadly budget is very tight. :) Budget is always tight. That's why you don't want a RAID 5. Do a RAID 5 now thinking you'll save a few bucks, and you'll be spending twice tha

Re: [PERFORM] With 4 disks should I go for RAID 5 or RAID 10

RAID 10. I snipped the rest of your message because none of it matters. Never use RAID 5 on a database system. Ever. There is absolutely NO reason to every put yourself through that much suffering. If you hate yourself that much just commit suicide, it's less drastic. -- Bill

Re: [PERFORM] viewing source code

In response to "Joshua D. Drake" <[EMAIL PROTECTED]>: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Fri, 14 Dec 2007 11:18:49 -0500 > Bill Moran <[EMAIL PROTECTED]> wrote: > > > > That is like saying anyone that has rights to call a w

Re: [PERFORM] viewing source code

t have rights to execute the functions > but they can see the source code for them. Shouldn't I be able to revoke > both the ability to execute and the ability to see functions? Um ... why did you snip my second paragraph where I said exactly this? -- Bill Moran Collaborative F

Re: [PERFORM] viewing source code

oach. As a result, what _really_ needs to be done is an extra permission bit added to functions so administrators can control who can view the function body. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023

Re: [PERFORM] Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005)

tten (or appropriate indexes added, or whatever) EXPLAIN can be your friend once you've found problematic queries. Another piece of broadly useful advice is to install the pgbuffercache addon and monitor shared_buffer usage to see if you've got enough. Also useful is monitoring the

Re: [PERFORM] Appending "LIMIT" to query drastically decreases performance

YSE" recently? I don't know about the "FULL" part ... but certainly an ANALYZE. Please post EXPLAIN ANALYZE output for the two queries. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 **

Re: [PERFORM] TB-sized databases

In response to Gregory Stark <[EMAIL PROTECTED]>: > "Bill Moran" <[EMAIL PROTECTED]> writes: > > > In response to Matthew <[EMAIL PROTECTED]>: > > > >> On Tue, 27 Nov 2007, Pablo Alcaraz wrote: > >> > it would be nice to do some

Re: [PERFORM] TB-sized databases

In response to Csaba Nagy <[EMAIL PROTECTED]>: > On Wed, 2007-11-28 at 08:27 -0500, Bill Moran wrote: > > Is there something wrong with: > > set enable_seqscan = off > > ? > > Nothing wrong with enable_seqscan = off except it is all or nothing type > of thi

Re: [PERFORM] TB-sized databases

; database. Is there something wrong with: set enable_seqscan = off ? -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] Query only slow on first run

r faster disks will improve this situation are probably 90% or better. Other things that could cause this problem are poor schema design, and unreasonable expectations. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTE

Re: [PERFORM] tuning for TPC-C benchmark

g = sql_ascii# actually, defaults to > database > # encoding > > # These settings are initialized by initdb -- they might be changed > lc_messages = 'it_IT.UTF-8' # locale for system > error message >

Re: [PERFORM] autovacuum: recommended?

it hasn't moved from 8.1 -> 8.2. The comment is that it's bad because it hasn't updated a major branch with the latest bug fixes. i.e. it hasn't moved from 8.1.4 to 8.1.5. If this is indeed the case, I agree that such a distro isn't worth using. -- Bill Moran Collab

Re: [PERFORM] Clustered/covering indexes (or lack thereof :-)

ates materialized_topics any time the first table is altered. Thus you always have fast lookups. Of course, this may be non-optimal if that table sees a lot of updates. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] Need to run CLUSTER to keep performance

ram the process holding the transaction open to do a vacuum full of that table when it's done with it's work. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 *

Re: [PERFORM] work_mem and shared_buffers

re to track when sorts don't fit in work_mem. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD

> involved and there are no joins. It's testing very low-level aspects of > performance. Actually, what it's really showing is parallelism, and I've always expected PostgreSQL to come out on top in that arena. -- Bill Moran Potential Technologies http://www.potentialtech

Re: [PERFORM] Need to run CLUSTER to keep performance

aybe a cron that vacuums that table every 5 minutes. You could also do a combination, i.e. enable autovacuum with conservative settings and set a cron to vacuum the table every 10 minutes. Vacuuming once a day is usually only enough if you have very minimal updates. -- Bill Moran Collabora

Re: [PERFORM] 12 hour table vacuums

In response to Ron St-Pierre <[EMAIL PROTECTED]>: > Bill Moran wrote: > > In response to Ron St-Pierre <[EMAIL PROTECTED]>: > > > > > >> We vacuum only a few of our tables nightly, this one is the last one > >> because it takes longer to run

Re: [PERFORM] 12 hour table vacuums

2000 > deadlock_timeout = 1 > max_locks_per_transaction = 640 > add_missing_from = on > > As I mentioned, any insights into changing the configuration to optimize > performance are most welcome. > > Thanks > > Ron > > ---(end of broadc

Re: [PERFORM] Postgres running Very slowly

78 kB shared > memory. > VACUUM This doesn't look problematic, so I doubt your vacuum policy is to blame. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)---

Re: [PERFORM] quickly getting the top N rows

us the explain. However, 2 guesses: 1) You never analyzed the table, thus PG has awful statistics and doesn't know how to pick a good plan. 2) You have so few rows in the table that a seq scan is actually faster than an index scan, which is why PG uses it

Re: [PERFORM] select count(*) performance (vacuum did not help)

ep up the vacuum schedule you've already established. You may want to (as has already been suggested) explicitly vacuum this table after large delete operations as well. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023

Re: [PERFORM] Low CPU Usage

in the same server there > aren't networks delays! Not network, no. But the results of your explains seem to show that the query is executing much faster on the new system than the old, so the problem still becomes, "what is happening after the query completes that is so slow?"

Re: [PERFORM] Low CPU Usage

ery, in the old one, in the new one), without difference and I only > retrieve the first 100 records (I didn't count the network time in any case). > But the weird thing is running the query in the new server the are many disk > access and cpu usage. And wi

Re: [PERFORM] Upgraded from 7.4 to 8.1.4 QUERIES NOW SLOW!!!

> > Index Cond: (qa.answer_id = "outer".id) > > > -> BitmapOr (cost=55.08..55.08 rows=6596 width=0) (never e

Re: R: [PERFORM] R: DELETE queries slow down

anner faster. > > Do you think it could be reasonable? Based on the information you've given and the responses you've made, I think you're as likely to roll a 1d6 and get the right solution as anything else. Good luck. > -Messaggio originale- > Da: Bil

Re: [PERFORM] R: DELETE queries slow down

gt; Generally it's faster to run more commands in a single transaction but what > I'm worried about is that you may have a transaction open which you aren't > committing for a long time. This can stop vacuum from being able to clean up > dead space and if it's in t

Re: [PERFORM] About autovacuum

x27;ll see detailed log messages about its activities. There were discussions on other lists about improving autovacuum's log messages, I'm pretty sure it will log more helpful information in 8.3. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PR

Re: [PERFORM] Vacum Analyze problem

mance related stuff. > > Thank you all in advance To add to Mikko's comments: Periodic vacuuming and analyzing is a mandatory part of running a PostgreSQL database server. You'll probably be best served to configure the autovacuum daemon to handle this for you. See the postgre

Re: [PERFORM] Transaction Log

ay with a lot less space than many other usages, so they might be very practical. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Performance issue

re not obvious to me. The explain analyze output is going to be key to working this out -- unless it's something like your postgresql.conf isn't properly tuned. > I vacuum and > reindex the database daily. > > I'd prefer not to have to rewrite th

Re: [PERFORM] Postgres performance problem

ou autovacuum or vacuum manually? > Tell us more... > > > Bye, > Chris. > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql

Re: [PERFORM] significant vacuum issues - looking for suggestions

> You can't use these techniques for a major version upgrade. > Use pg_dump piped to psql. That will also eliminate all bloat. If you can't afford any downtime, you may be able to use Slony to do your upgrade. However, slony adds overhead, and if this system is tapped out already, i

Re: [PERFORM] significant vacuum issues - looking for suggestions

ons on a server if needed, the > ability to quickly revert to a previous version, etc however this is a > discussion for another day - I only mention it in case the question "why not > just use RPM's?" arises... > > > So here's my questions: > > 1

Re: [PERFORM] deadlock_timeout parameter in Postgresql.cof

e any difference ? Does the documentation leave anything unanswered? http://www.postgresql.org/docs/8.2/static/runtime-config-locks.html -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end o

  1   2   3   >