On 4-Apr-07, at 8:46 AM, Andreas Kostyrka wrote:
* Peter Kovacs <[EMAIL PROTECTED]> [070404 14:40]:
This may be a silly question but: will not 3 times as many disk
drives
mean 3 times higher probability for disk failure? Also rumor has it
that SATA drives are more prone to fail than SCSI dri
Depends on what the query is. If the queries take 3 to 5 days to
execute, then 1 query per day on a 4 CPU machine would be at capacity.
On 23-Dec-06, at 3:12 AM, [EMAIL PROTECTED] wrote:
Hey Everyone,
I am having a bit of trouble with a web host, and was wondering as
what
you would class
Rebuilding the indexes or integrity confirmations are probably taking
most of the time.
What is your work_mem setting?
On 22-Dec-06, at 9:32 AM, Sebastián Baioni wrote:
Hi,
We have a database with one table of 10,000,000 tuples and 4 tables
with 5,000,000 tuples.
While in SQL Server it tak
On 26-Nov-06, at 8:11 AM, Steinar H. Gunderson wrote:
On Sun, Nov 26, 2006 at 09:43:11AM -0300, Marcelo Costa wrote:
All its tables constantly manipulated (INSERT, UPDATE, DELETE)
they need a
VACUUM
Just a minor clarification here: INSERT does not create dead rows,
only
UPDATE and DELETE
On Wed, 2006-09-27 at 18:08 +0200, Edoardo Ceccarelli wrote:
>
> I have read that autovacuum cannot check to see pg load before
> launching
> vacuum but is there any patch about it? that would sort out the
> problem
> in a good and simple way.
In some cases the solution to high load is to vacu
On Tue, 2006-09-19 at 15:22 +0200, Ivan Voras wrote:
> I've just fired off a "DELETE FROM table" command (i.e. unfiltered
> DELETE) on a trivially small table but with many foreign key references
> (on similar-sized tables), and I'm waiting for it to finish. It's been
> 10 minutes now, which see
> capsa=# explain analyze select name from capsa.flatomfilesysentry
> where objectid in ( select dstobj from capsa.flatommemberrelation
> where srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409');
>
>
>QUERY PLAN
> -
On Thu, 2005-07-14 at 15:08 +1000, Alison Winters wrote:
> Hi,
>
> Our application requires a number of processes to select and update rows
> from a very small (<10 rows) Postgres table on a regular and frequent
> basis. These processes often run for weeks at a time, but over the
Are these long
> If I could get and deploy some SSD (Solid State Disk) devices that
> would make this sort of thing *actually safe,* I'd expect that to be a
> pretty fabulous improvement, at least for write-heavy database
> activity.
Not nearly as much as you would expect. For the price of the SSD and a
SCSI con
> There are some immediate questions from our engineers about performance
>
> "- Oracle has one particular performance enhancement that Postgres is
> missing. If you do a select that returns 100,000 rows in a given order,
> and all you want are rows 99101 to 99200, then Oracle can do that very
>
> 1.) shared_buffers I see lot of reference to making
> this the size of available ram (for the DB). However,
> I also read to make it the size of pgdata directory.
> 2.) effective_cache_size - from what I read this is
> the 'total' allowed memory for postgresql to use
> correct? So, if I am w
> Now of course this isn't Nirvana, you must pay somewhere ;-) and our
> weak spot is the need for VACUUM. But you have no need to fear large
> individual transactions.
No need to fear long running transactions other than their ability to
stop VACUUM from doing what it's supposed to be doing, thu
> Ok, if all 21 are affected, I can understand the problem.
> But allow me to say that this is a "functional error"
It's a choice between total throughput on a high load, high connection
basis (MVCC dramatically wins here), versus a single user, low load
scenario (MS Access is designed for this).
On Mon, 2005-06-06 at 12:00 -0400, Amit V Shah wrote:
> Hi all,
>
> Thanks for your replies.
>
> I ran a very prelimnary test, and found following results. I feel they are
> wierd and I dont know what I am doing wrong !!!
>
> I made a schema with 5 tables. I have a master data table with foreig
On Wed, 2005-05-11 at 12:53 +0800, Christopher Kings-Lynne wrote:
> > Another trick you can use with large data sets like this when you want
> > results
> > back in seconds is to have regularly updated tables that aggregate the data
> > along each column normally aggregated against the main data s
> I've done other things that make sense, like using indexes, playing with the
> planner constants and turning up the postgres cache buffers.
After you load the new days data try running CLUSTER on the structure
using a key of (stockID, date) -- probably your primary key.
This should significantl
>
> I did have a question if any folks are using two servers one for
> reporting and one for data entry what system should be the beefier?
Yeah. We started putting up slaves for reporting purposes and
application specific areas using Slony replicating partial data sets to
various locations -- so
> I'm having a pretty serious problem with postgresql's performance.
> Currently, I have a cron task that is set to restart and vacuumdb -faz
> every six hours. If that doesn't happen, the disk goes from 10% full
> to 95% full within 2 days (and it's a 90GB disk...with the database
> being a
> The HPs are at root pretty good machines -- and take 6 drives, so I expect
> you're mixed up there. However, they use HP's proprietary RAID controller
> which is seriously defective. So you need to factor replacing the RAID
> controller into the cost.
Do you have any additional materials o
On Fri, 2005-04-15 at 15:43 -0500, Matthew Nuzum wrote:
> I think there are many people who feel that $7,000 is a good budget for a
> database server, me being one.
The budget for a database server is usually some %age of the value of
the data within the database or the value of it's availability.
On Wed, 2005-04-06 at 14:40 -0400, Alex Turner wrote:
> I think his point was that 9 * 4 != 2400
Oh.. heh.. I didn't even notice that.
Can I pretend I did it in my head using HEX math and that it wasn't a
mistake?
> On Apr 6, 2005 2:23 PM, Rod Taylor <[EMAIL PROTECTED]> w
On Wed, 2005-04-06 at 19:42 +0200, Steinar H. Gunderson wrote:
> On Wed, Apr 06, 2005 at 01:18:29PM -0400, Rod Taylor wrote:
> > Yeah, I think that can be done provided there is more than one worker.
> > My limit seems to be about 1000 transactions per second each with a
> >
On Wed, 2005-04-06 at 19:08 +0200, [EMAIL PROTECTED] wrote:
>
> On our production server, I can insert 5000 tuples in 2100 ms.
>
> Single Xeon 2.6 Ghz
> 2 Gigs ram
> 3ware RAID 5 SATA drives array, 3 drives only :-((
> PG 8.0 - fsync off
>
> I do think inserting 5000 tuples in a second (i
On Wed, 2005-04-06 at 16:12 +, Mohan, Ross wrote:
> I wish I had a Dell system and run case to show you Alex, but I don't...
> however...using Oracle's "direct path" feature, it's pretty straightforward.
>
> We've done 110,000 rows per second into index-less tables on a big system
> (IBM Powe
On Sun, 2005-03-20 at 00:29 -0400, Alvaro Herrera wrote:
> On Sat, Mar 19, 2005 at 07:05:53PM -0500, Tom Lane wrote:
> > Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > > We probably also need multi-table indexes.
> >
> > As Josh says, that seems antithetical to the main point of partitioning,
> >
On Wed, 2005-03-16 at 19:31 +0100, Laurent Martelli wrote:
> >>>>> "Rod" == Rod Taylor <[EMAIL PROTECTED]> writes:
>
> Rod> On Wed, 2005-03-16 at 18:58 +0100, Laurent Martelli wrote:
> >> Consider this query:
> >>
> >>
On Wed, 2005-03-16 at 18:58 +0100, Laurent Martelli wrote:
> Consider this query:
>
> SELECT distinct owner from pictures;
The performance has nothing to do with the number of rows returned, but
rather the complexity of calculations and amount of data to sift through
in order to find it.
> Any
> I get this problem on my dev (Windows/7.4/Cygwin) environment. But now
> I see that it's also have this problem on my production env. Yes I
> tought I was maybe just a cygwin/Windows problem .. apparently not :-
Care to try again with logging enabled on the PostgreSQL side within the
de
On Wed, 2005-02-23 at 15:26 -0300, Bruno Almeida do Lago wrote:
> Is there a real limit for max_connections? Here we've an Oracle server with
> up to 1200 simultaneous conections over it!
If you can reduce them by using something like pgpool between PostgreSQL
and the client, you'll save some head
t the
buffers. I would suppose that toast doesn't bypass them.
--
Rod Taylor <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
On Tue, 2005-02-15 at 09:34 +0800, Michael Ryan S. Puncia wrote:
> Hi,
>
>
>
> I have 3 tables in the database with 80G of data, one of them is
> almost 40G and the remaining 2 tables has 20G each.
>
> We use this database mainly for query and updating is done only
> quarterly and the database
aster? I've done
If it's for a single request, you cannot get single processors which are
much faster than what you describe as having.
Want to send us a few EXPLAIN ANALYZE's of your longer running queries?
--
Rod Taylor <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Sat, 2005-01-22 at 12:41 -0600, Bruno Wolff III wrote:
> On Sat, Jan 22, 2005 at 12:13:00 +0900,
> Tatsuo Ishii <[EMAIL PROTECTED]> wrote:
> >
> > Probably VACUUM works well for small to medium size tables, but not
> > for huge ones. I'm considering about to implement "on the spot
> > salvagi
On Thu, 2005-01-20 at 15:36 +0100, Hervé Piedvache wrote:
> Le Jeudi 20 Janvier 2005 15:24, Christopher Kings-Lynne a écrit :
> > > Is there any solution with PostgreSQL matching these needs ... ?
> >
> > You want: http://www.slony.info/
> >
> > > Do we have to backport our development to MySQL for
On Thu, 2005-01-06 at 12:35 -0500, Dave Cramer wrote:
> Reading can be worse for a normalized db, which is likely what the
> developers were concerned about.
To a point. Once you have enough data that you start running out of
space in memory then normalization starts to rapidly gain ground again
> Each COPY only hits ones CPU for like 2.1% but anything over about 5
> kicks the load avg up.
Sounds like disk IO is slowing down the copy then.
> Ill get some explain analysis and table structures out there pronto.
>
> -Original Message-
> From: Rod Taylor [mailto:[EMA
> 1)the 250 million records are currently whipped and reinserted as a
> "daily snapshot" and the fastest way I have found "COPY" to do this from
> a file is no where near fast enough to do this. SQL*Loader from Oracle
> does some things that I need, ie Direct Path to the db files access
> (skipp
Are there any tricks to speeding up pg_dump aside from doing them from a
replicated machine?
I'm using -Fc with no compression.
--
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomai
On Thu, 2004-12-02 at 17:07 +0100, Dmitry Karasik wrote:
> Hi Thomas!
>
> Thomas> Look at the ACTUAL TIME. It dropped from 0.029ms (using the index
> Thomas> scan) to 0.009ms (using a sequential scan.)
>
> Thomas> Index scans are not always faster, and the planner/optimizer knows
> Tho
as there isn't any data
in those tables and the queries took less than 1ms.
--
Rod Taylor <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
tion pooling via pgpool or similar has been installed.
Please report back with your findings.
--
Rod Taylor <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
On Mon, 2004-11-22 at 16:54 -0800, Josh Berkus wrote:
> Alexandre,
>
> > What is the common approach? Should I use directly the product_code as
> > my ID, or use a sequantial number for speed? (I did the same for the
> > company_id, this is a 'serial' and not the shor name of the customer.
> > I j
150 tuples.
Thanks for any tips -- particularly Solaris kernel tuning.
--
Rod Taylor <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
I'm looking for suggestions on tuning Solaris 9 for a SunFire 890 (Ultra
IV chips) connected to an Hitachi 9500V running PostgreSQL 7.4.
Database is approx 160GB in size with a churn of around 4GB per day (2
GB updated, 2GB inserted, very little removed). It's a mixture of OLTP
and reporting.
5%
> It seems to me that a query saying "SELECT column FROM table WHERE
> column LIKE 'AA%';" should be just as fast or very close to the first
> case up above. However, explain tells me that this query is not using
> the index above, which is what's not making sense to me.
It looks for an exact exp
On Thu, 2004-11-04 at 17:42, [EMAIL PROTECTED] wrote:
> Citando Rod Taylor <[EMAIL PROTECTED]>:
> > Please send an explain analyze from both.
> I'm sendin three explains. In the first the Dell machine didn't use existing
> indexes, so I turn enable_seqscan off (t
On Thu, 2004-11-04 at 16:06, Alvaro Nunes Melo wrote:
> Hi,
>
> I have a very tricky situation here. A client bought a Dell dual-machine
> to be used as Database Server, and we have a cheaper machine used in
> development. With identical databases, configuration parameters and
> running the same q
On Thu, 2004-10-28 at 12:31, Tom Lane wrote:
> Josh Berkus <[EMAIL PROTECTED]> writes:
> >> One drawback to this is that it would require an additional lseek per table
> >> while planning, but that doesn't seem like a huge penalty.
>
> > Hmmm ... would the additional lseek take longer for larger t
On Tue, 2004-10-26 at 13:42, Anjan Dave wrote:
> It probably is locking issue. I got a long list of locks held when we ran select *
> from pg_locks during a peak time.
>
> relation | database | transaction | pid | mode | granted
> --+--+-+---+---
On Mon, 2004-10-25 at 16:53, Anjan Dave wrote:
> Hi,
>
>
>
> I am dealing with an app here that uses pg to handle a few thousand
> concurrent web users. It seems that under heavy load, the INSERT and
> UPDATE statements to one or two specific tables keep queuing up, to
> the count of 150+ (one
On Wed, 2004-10-20 at 12:45, Robert Creager wrote:
> When grilled further on (Tue, 19 Oct 2004 22:12:28 -0400),
> Rod Taylor <[EMAIL PROTECTED]> confessed:
>
> > > I've done some manual benchmarking running my script 'time script.pl'
> > > I realis
> I've done some manual benchmarking running my script 'time script.pl'
> I realise my script uses some of the time, bench marking shows that
> %50 of the time is spent in dbd:execute.
The perl drivers don't currently use database level prepared statements
which would give a small boost.
But your
> Whatever the case, the database still slows down to a halt after a month or
> so, and I have to go in and shut everything down and do a VACUUM FULL by
> hand. One index (of many many) takes 2000 seconds to vacuum. The whole
> process takes a few hours.
Do a REINDEX on that table instead, and r
On Mon, 2004-10-11 at 13:38, Andrew Sullivan wrote:
> On Tue, Oct 05, 2004 at 09:47:36AM -0700, Josh Berkus wrote:
> > As long as you're on x86, scaling outward is the way to go. If you want to
> > continue to scale upwards, ask Andrew Sullivan about his experiences running
> > PostgreSQL on bi
> More to the point though, I think this is a feature that really really
> should be in the DB, because then it's trivial for people to use.
How does putting it into PGPool make it any less trivial for people to
use?
---(end of broadcast)---
TIP
I set nested_loop = off, which is why I have the high cost.
@ is a postgis operator between 2 geomotries (both polygons). It's the @
operator which is expensive. Is there a way to force a cheaper way of
doing that join?
-> Nested Loop (cost=11905.94..11906.08 rows=1
width=68) (
> Rod Taylor wrote:
> | I've used both a NetApp and Hitachi based SANs with PostgreSQL. Both
> | work as well as expected, but do require some tweeking as they normally
> | are not optimized for the datablock size that PostgreSQL likes to deal
> | with (8k by default) -- this c
MARY KEY, btree (date, reference_number,
> message_type, "time", match_number)
> "island_history_date_stock_time" btree (date, stock, "time")
> "island_history_oid" btree (oid)
>
> ---(end of broadcast)-
> People expect count(*) _without a where clause_ to be cached in a single
> global variable. Postgres can't do this, but the reason has everything to do
Someone should write an approx_count('table') function that reads
reltuples from pg_class and tell them to use it in combination with
autovac.
On Fri, 2004-08-20 at 05:37, Gaetano Mendola wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Christopher Kings-Lynne wrote:
>
> |>>> Without index: 1.140 ms
> |>>> With index: 1.400 ms
> |>>> With default_statistic_targer = 200: 1.800 ms
> |>>
> |>>
> |>>
> |>>
> |>> Can I just che
On Wed, 2004-08-18 at 11:18, Raoul Buzziol wrote:
> > You're not going to be able to get a Dual Athlon MP for the same price
> > as a single Xeon. A few years back, this was the case because Xeon CPUs
> > & MBs had a huge premium over Athlon. This is no longer true mainly
> > because the number of
On Fri, 2004-08-13 at 08:10, Ulrich Wisser wrote:
> Hi,
>
> my inserts are done in one transaction, but due to some foreign key
> constraints and five indexes sometimes the 100 inserts will take more
> than 5 minutes.
It is likely that you are missing an index on one of those foreign key'd
item
On Wed, 2004-08-11 at 18:03, Jason Coene wrote:
> > -Original Message-
> > From: Rod Taylor [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, August 11, 2004 5:46 PM
> > To: Jason Coene
> > Cc: 'Merlin Moncure'; Postgresql Performance
> > Subj
On Wed, 2004-08-11 at 17:31, Brian Hirt wrote:
> On Aug 11, 2004, at 3:18 PM, Jason Coene wrote:
> >
> > I'm wondering why our PG server is using so little memory... The
> > system has
> > 2GB of memory, though only around 200MB of it are used. Is there a PG
> > setting to force more memory usag
> I'm wondering why our PG server is using so little memory... The system has
> 2GB of memory, though only around 200MB of it are used. Is there a PG
This is the second time you've said this. Surely you're not implying
there is 1.8GB Free Memory -- rather than 1.8GB in Buffers or Cache.
Send ou
> Our database is about 20GB on disk, we have some quite large tables - 2M
> rows with TEXT fields in a sample table, accessed constantly. We average
> about 4,000 - 5,000 queries per second - all from web traffic. As you can
99% is reads? and probably the same data over and over again? You migh
query, ...) and are using indexes (I "explained" each one to
> be sure) :
Care to send us the EXPLAIN ANALYZE output for each of the 4 queries
after you've improved the datatype selection?
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://
> | ARC still helps, since it makes sure the shared_buffers don't all get
> | flushed from the useful small datasets when a seq scan gets executed.
>
> I'm still not convinced. Why the last backend alive, have to throw away
> bunch of memory copied in the SHM? And again, the ARC is a replacement
>
> SELECT count(*) FROM contacts LEFT JOIN companies ON contacts.sid =
> companies.intfield01
>
> contacts.sid (type text, b-tree index on it)
> companies.intfield01 (type bigint, b-tree index on it)
> How can I force the usage of the indexes when using "left join". Or
> any other SQL construct th
You only want one record to be returned? Tack a LIMIT 1 onto the end of
the query.
> My problem is deceptively simple: how you read the next record from a
> table based on a given set of values? In practice, this is difficult to
> implement. If anybody can suggest a alternative/better way to th
On Fri, 2004-07-23 at 01:50, William Carney wrote:
> Hello,
>
> Using a test client application that performs 10 insert operations on a
> table, with the client application running on the same machine as the
> Postgres server, I get the following results for the time taken to run the
> test:
>
On Wed, 2004-07-21 at 06:04, Steinar H. Gunderson wrote:
> On Tue, Jul 20, 2004 at 10:18:19PM -0400, Rod Taylor wrote:
> > I've taken a look and managed to cut out quite a bit of used time.
> > You'll need to confirm it's the same results though (I didn't -- i
> I could of course post the updated query plan if anybody is interested; let
> me know. (The data is still available if anybody needs it as well, of
> course.)
I've taken a look and managed to cut out quite a bit of used time.
You'll need to confirm it's the same results though (I didn't -- it is
> Would NAS or SAN be good solutions ? (I've read that NAS uses NFS
> which could slow down the transfer rate ??)
> Has anyone ever tried one of these with postgresql ?
I've used both a NetApp and Hitachi based SANs with PostgreSQL. Both
work as well as expected, but do require some tweeking as t
> The database grows very slowly. The main load comes from SELECT's and
> not from INSERT's or UPDATE's, but the performance gets slower day by day...
>
> I have no idea where to search for the speed break!
Lets start with an example. Please send us an EXPLAIN ANALYZE of a
couple of the poorly p
> Oddly enough, I put the same database on a different machine, and the
> query now behaves as I hoped all along. Notice that I'm using the
> "real" query, with the aspid in asc and the other fields in desc order,
> yet the query does use the call_idx13 index:
Notice that while it only takes 19 s
> What would be most interesting to see is whether this makes it wise to
> increase shared buffer size. It may be more effective to bump down
> the cache a little, and bump up sort memory; hard to tell.
How do we go about scheduling tests with the OSDL folks? If they could
do 10 runs with buffers
> OK, that makes sense; however, this doesn't:
>
> elon2=# explain analyse select * from call where aspid='123C' and
> OpenedDateTime between '2000-01-01 00:00:00.0' and '2004-06-24
> 23:59:59.999' order by aspid asc, openeddatetime asc, callstatus asc,
> calltype asc, callkey asc;
> I've modifie
> It seems vacuum_mem does not have performance
> effect at all.
Wrong conclusion. It implies that your test case takes less than 64M of
memory to track your removed tuples. I think it takes 8 bytes to track a
tuple for vacuuming an index, which means it should be able to track
80 deletions.
> However, this query performs a sequence scan on the table, ignoring the
> call_idx13 index (the only difference is the addition of the aspid field
> in the order by clause):
You do not have an index which matches the ORDER BY, so PostgreSQL
cannot simply scan the index for the data you want. Th
> Can I get any better performance?
You can try bumping your sort memory way up (for this query only).
Another method would be to cluster the table by the symbol column
(eliminates the expensive sort).
If you could run a very simple calculation against open & close numbers
to eliminate a majorit
> 2) You can hire a PG database expert.This will be much faster, but cost
> you a lot of money.
I wouldn't exactly say "a lot of money". Lots of consulters out there
are willing to put in a weeks worth of effort, on site, for
significantly less than a support contract with most commercial DB
> Does php need to read database everytime when pg_fetch_array is executed in
> the while loop or all the rows have been in the memory after pg_query?
You may need to ask the php people about this one. The PostgreSQL
protocol would allow data to continue streaming in at the same time as
you are pr
> As I understand it, sync() is never called anymore. mdsync() hits the
> all the files 1 by 1 with an fsync. My understanding of the commit
> process is that 30 tps is quite reasonable for my hardware.
Sorry. I didn't see the version in the subject and assumed 7.4 on a
Linux machine with exce
On Fri, 2004-06-11 at 14:40, Merlin Moncure wrote:
> I am batch inserting insert statements into a database with fsync = on.
> My single disk system is on a 10k drive...even though I am inside a
> transaction there is at least 1 file sync per row insert.
Which filesystem?
PostgreSQL isn't issui
On Wed, 2004-06-09 at 21:45, Christopher Kings-Lynne wrote:
> > If I take away the diagonalSize condition in my query I find that there
> > are 225 rows that satisfy the other conditions. 155 of these have a
> Maybe you should drop your random_page_cost to something less than 4,
> eg. 3 or even
> ... and here is the plan with statistics set to 1000 ...
>
> Seq Scan on nrgfeature f (cost=0.00..31675.57 rows=18608 width=218)
> (actual time=63.544..1002.701 rows=225 loops=1)
>Filter: ((upperrightx > 321264.236977215::double precision) AND
> (lowerleftx < 324046.799812083::double preci
On Wed, 2004-06-09 at 16:50, ken wrote:
> Thanks Rod,
>
> This setting has no effect however. If I set statistics to 1000, or
Okay.. but you never did send EXPLAIN ANALYZE output. I want to know
what it is really finding.
> On Wed, 2004-06-09 at 13:12, Rod Taylor wrote:
> > I
It seems to believe that the number of rows returned for the >49.999
case will be 4 times the number for the >50 case. If that was true, then
the sequential scan would be correct.
ALTER TABLE ALTER COLUMN diagonalsize SET STATISTICS 1000;
ANALZYE ;
Send back EXPLAIN ANALYZE output for the >49.99
On Mon, 2004-06-07 at 16:12, Dan Langille wrote:
> On 7 Jun 2004 at 16:00, Rod Taylor wrote:
>
> > On Mon, 2004-06-07 at 15:45, Dan Langille wrote:
> > > A production system has had a query recently degrade in performance.
> > > What once took < 1s now takes
send explain analyse again with SEQ_SCAN enabled but with
nested loops disabled?
Off the cuff? I might hazard a guess that effective_cache is too low or
random_page_cost is a touch too high. Probably the former.
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http:
> random_page_cost = 0.5
Not likely. The lowest this value should ever be is 1, and thats if
you're using something like a ram drive.
If you're drives are doing a ton of extra random IO due to the above
(rather than sequential reads) it would lower the throughput quite a
bit.
Try a value of 2 fo
up_id, member_id)
Remove (member_id, group_id)
Add (member_id)
An index on just member_id is actually going to perform better than
member_id, group_id since it has a smaller footprint on the disk.
Anytime where both group_id and member_id are in the query, the
(group_id, member_id) index will likely b
On Fri, 2004-06-04 at 18:07, Michael Nonemacher wrote:
> Slight update:
>
> Thanks for the replies; this is starting to make a little more sense...
>
> I've managed to track down the root of the problem to a single query on
> a single table. I have a query that looks like this:
>select c
On Tue, 2004-06-01 at 23:16, V i s h a l Kashyap @ [Sai Hertz And
Control Systems] wrote:
> Dear all,
>
> Have anyone compiled PostgreSQL with kernel 2.6.x
> if YES
> 1. Was their any performance gains
OSDL reports approx 20% improvement. I've seen similar with some data
access patterns.
> 2. W
> What is essentially required is the "prescient cacheing algorithm,"
> where the postmaster must consult /dev/esp in order to get a
> prediction of what blocks it may need to refer to in the next sixty
> seconds.
Easy enough. Television does it all the time with live shows. The guy
with the buzze
On Fri, 2004-05-14 at 14:00, Eduardo Almeida wrote:
> Hi folks,
>
> I need some help in a TPCH 100GB benchmark.
Performance with 7.5 is much improved over 7.4 for TPCH due to efforts
of Tom Lane and OSDL. Give it a try with a recent snapshot of
PostgreSQL.
Otherwise, disable nested loops for tha
> but if I use:
> select url from urlinfo where url like 'http://%.lycos.de';
> it won't use index at all, NOT good!
> is there any way I can force secon query use index???
create index nowww on urlinfo(replace(replace(url, 'http://', ''),
'www.', '')));
SELECT url
FROM urlinfo
WHERE replace(re
> Or even better an offset into the datatable for the earliest deleted
> row, so if you have a table where you update the row shortly after
> insert and then never touch it vacuum can skip most of the table
> (inserts are done at the end of the table, right?)
Inserts are done at the end of the
> I would be nice to get a feel for how much performance loss would be incurred in
> maintaining the index flags against possible performance gains for getting the data
> back
> out again.
I guess the real question is, why maintain index flags and not simply
drop the index entry altogether?
A
1 - 100 of 207 matches
Mail list logo
