Re: [PERFORM] Help with extracting large volumes of records across related tables
On 13/09/2004 12:38 Damien Dougan wrote: [snip] Are there any tools/tricks/tips with regards to extracting large volumes of data across related tables from Postgres? It doesnt have to export into XML, we can do post-processing on the extracted data as needed - the important thing is to keep the relationship between PvA and PvB on a row-by-row basis. Have you considered using cursors? -- Paul Thomas +--+---+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+---+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] insert
On 13/08/2004 13: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. Two possibilities come to mind: a) you need an index on the referenced FK field b) you have an index but a type mis-match (e.g, an int4 field referencing an int8 field) Either of these will cause a sequential table scan and poor performance. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] The black art of postgresql.conf tweaking
On 04/08/2004 13:45 Paul Serby wrote: Can anyone give a good reference site/book for getting the most out of your postgres server. All I can find is contradicting theories on how to work out your settings. This is what I followed to setup our db server that serves our web applications. http://www.phpbuilder.com/columns/smith20010821.php3?page=2 We have a Dell Poweredge with the following spec. CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache) Physical Memory: 2077264 kB Swap Memory: 2048244 kB Apache on the Web server can take up to 300 connections and PHP is using pg_pconnect Postgres is set with the following. max_connections = 300 shared_buffers = 38400 Might be higher that neccessary. Some people reckon that there's no measurable performance going above ~10,000 buffers sort_mem = 12000 Do you really need 12MB of sort memory? Remember that this is per connection so you could end up with 300x that being allocated in a worst case scenario. But Apache is still maxing out the non-super user connection limit. The machine is under no load and I would like to up the max_connections but I would like to know more about what you need to consider before doing so. I can't think why you should be maxing out when under no load. Maybe you need to investigate this further. The only other source I've found is this: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html But following its method my postgres server locks up straight away as it recommends setting max_connections to 16 for Web sites? I think you've mis-interpreted that. She's talking about using persistent connections - i.e., connection pooling. Is there a scientific method for optimizing postgres or is it all 'finger in the air' and trial and error. Posting more details of the queries which are giving the performance problems will enable people to help you. You're vacuum/analyzing regularly of course ;) People will want to know: - PostgreSQL version - hardware configuration (SCSI or IDE? RAID level?) - table schemas - queries together with EXPLAIN ANALYZE output also output from utils like vmstat, top etc may be of use. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] Traduc Party
On 23/06/2004 19:47 Laurent Martelli wrote: How in hell did could this mail be sent to pgsql-performance ??? I must have inadvertently hit a fatal and obscure keystroke in Emacs/Gnus. That sort of implies that there are Emacs keystrokes which aren't obsure. I've been using it dayly for 2 years now and have yet to discover any key sequence which makes any sense. But then I don't do drugs so my perseption is probably at odds with the origators of Emacs ;) -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Major differences between oracle and postgres performance - what can I do ?
On 18/06/2004 12:31 Gary Cowell wrote: [snip] I'm not as familiar with postgresql as I am with Oracle but I think I've configured comparible buffering and sort area sizes, certainly there isn't much physical IO going on in either case. What can I do to speed up this query? Other queries are slightly slower than under Oracle on the same hardware but nothing like this. Usual questions: have you vacuumed the table recently? what are your postgresql.conf settings? can you show us explain ANALYZE output rather than just explain output? -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Visual Explain
On 17/06/2004 17:54 Vitaly Belman wrote: Is it possible to download the Visual Explain only (link)? I only see that you can donwload the whole ISO (which I hardly need). You can get it from CVS and build it yourself. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Visual Explain
On 17/06/2004 12:10 Adam Witney wrote: Will this run on other platforms? OSX maybe? It's a Java app so it runs on any any platform with a reasonably modern Java VM. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] ORDER BY user defined function performance issues
On 11/06/2004 12:14 Nick Trainor wrote:
[snip]
However, when I seek to ORDER the results, then it takes 'forever':
EXPLAIN ANALYSE SELECT t1.value1,t1.value2,
getday_total('1','23',t1.id::integer,'31','59','2','2004','182','153','6','2004','0')
FROM tblitem t1 WHERE t1.type_id=23::int2 and (t1.id >= 1::int8 and
t1.id<=9223372036854775807::int8)
ORDER BY
getday_total('1','23',t1.id::integer,'31','59','2','2004','182','153','6','2004','0')
DESC
OFFSET 0 LIMIT 20;
I expect that pg is having to evaluate your function every time it does a
compare within its sort. Something like
SELECT t1.value1,t1.value2,
getday_total(..) AS foo
FROM tblitem t1 WHERE t1.type_id=23::int2 and (t1.id >= 1::int8 and
t1.id<=9223372036854775807::int8)
ORDER BY foo
might work. Otherwise try selecting into a temp table then doing the order
by on that table.
HTH
--
Paul Thomas
+--+-+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+--+-+
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] MySQL vs PG TPC-H benchmarks
On 21/04/2004 14:31 Cestmir Hybl wrote: > Looks like he's using the default postgresql.conf settings in which case > I'm not suprised at pg looking so slow. The question also is, IMHO, why the hell, postgreSQL still comes out of the box with so stupid configuration defaults, totally underestimated for todays average hardware configuration (1+GHz, 0.5+GB RAM, fast FSB, fast HDD). It seems to me better strategy to force that 1% of users to "downgrade" cfg. than vice-versa. regards ch This has been discussed many times before. Check the archives. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] MySQL vs PG TPC-H benchmarks
On 21/04/2004 09:31 Nick Barr wrote: Hi, Has anyone had a look at: http://people.ac.upc.es/zgomez/ I realize that MySQL & PG cannot really be compared (especially when you consider the issues that MySQL has with things like data integrity) but still surely PG would perform better than the stats show (i.e. #7 31.28 seconds versus 42 minutes!!!). Looks like he's using the default postgresql.conf settings in which case I'm not suprised at pg looking so slow. His stated use of foreign keys invalidates the tests anyway as MyISAM tables don't support FKs so we're probably seeing FK check overheads in pg that are simply ignore by MySQL. In an honest test, MySQL should be reported as failing those tests. Perhaps one of the advocay team will pick up the batton? On a side note it certainly looks like linux kernel 2.6 is quite a bit faster in comparision to 2.4. Yes, I've seen other benchmarks which also show that. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] statistics
On 07/04/2004 22:05 Jaime Casanova wrote: What the statistics are? Where can i view it? where can i find info about its field and why are they valuable information to performance? thanx in advance, Jaime Casanova OK. An idiot's guide to statistics by a full-time idiot... Let's start with a simple premise. I'm a RDBMS (forget that I'm actually an idiot for a moment...) and I've been asked for select * from foo where bar = 7; How do I go about fulfilling the reequest in the most efficient manner? (i.e., ASAP!) One way might be to read through the whole table and return only those rows which match the where criteron - a sequential scan on the table. But wait a minute, there is an index on column bar. Could I use this instead? Well, of course, I could use it but I have to keep sight of the goal of returning the data ASAP and I know that the act of reading index/reading table/... will have a performance penalty due to a lot more head movement on the disk. So how do I make chose between a sequential scan and an index scan? Let's lokk at a couple of extreme scenarios: 1) let's look at the condition where all or virtually all of the bar columns are populated wityh the value 7. In this case it would be more efficient to read sequentially through the table. 2) the opposite of (1) - very few of the bar columns have the value 7. In this case using the index could be a winner. So generalising, I need to be able to estimate whether doing a sequential scan is more efficient that an index scan and this comes down to 2 factors: a) the cost of moving the disk heads all over the place (random page cost) b) the spread of values in the selecting column(s) (a) is specfified in postgresql.conf (see archives for much discusion about what the value should be..) (b) is determined by the dastardly trick of actually sampling the data in the table!!! That's what analyze does. It samples your table(s) and uses the result to feeede into it's descision about when to flip between sequential and index scans. Hope this makes some kind of sense... -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] PostgreSQL and Linux 2.6 kernel.
On 04/04/2004 09:56 Gary Doades wrote: Unfortunately I don't understand the question! My background is the primarily Win32. The last time I used a *nix OS was about 20 years ago apart from occasional dips into the linux OS over the past few years. If you can tell be how to find out what you want I will gladly give you the information. Googling threw up http://spider.tm/apr2004/cstory2.html Interesting and possibly relevant quote: "Benchmarks have shown that in certain conditions the anticipatory algorithm is almost 10 times faster than what 2.4 kernel supports". HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] select slow?
On 31/03/2004 16:40 Tom Lane wrote: "Jaime Casanova" <[EMAIL PROTECTED]> writes: > There are no indexes yet, and the table is just 6 rows long so even if > indexes exists the planner will do a seq scan. that's my whole point 63m for > seq scan in 6 rows table is too much. That was 63 milliseconds, according to your original post, which seems perfectly reasonable to me seeing that it's not a super-duper server. The problem sounds to be either on the client side or somewhere in your network. I don't know anything about VB, but you might want to look through the client-side operations to see what could be eating up the 13 seconds. Given that the client and server are on different machines, I'm wondering the bulk of the 13 seconds is due a network mis-configuration or a very slow DNS server... -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] select slow?
On 30/03/2004 20:25 Jaime Casanova wrote: hi all, i have an amd athlon with 256 ram (i know, this is not a *real* server but my tables are small) i'm using vb6 (win98) with pgsql-7.3.4 (rh8) trough the psqlodbc. when i do a select in took long to execute, here is an example table icc_m_banco CREATE TABLE ICC_M_BANCO ( CodBanco SMALLINT NOT NULL, Descripcion CHARACTER VARYING(60) NOT NULL, RefContable NUMERIC, Estado CHAR(1) NOT NULL, FecRegistro DATE NOT NULL, CONSTRAINT EstadoBanco CHECK ((Estado = 'A') or (Estado = 'I')), PRIMARY KEY(CodBanco) ); select * from icc_m_banco where codbanco = 1; select * from icc_m_banco where codbanco = 1::int2; -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] Scaling further up
On 03/03/2004 18:23 scott.marlowe wrote: [snip] There are three factors that affect how fast you can get to the next sector: seek time settle time rotational latency Most drives only list the first, and don't bother to mention the other two. Ah yes, one of my (very) few still functioning brain cells was nagging about another bit of time in the equation :) On many modern drives, the seek times are around 5 to 10 milliseconds. [snip] Going back to the OPs posting about random_page_cost, imagine I have 2 servers identical in every way except the disk drive. Server A has a 10K rpm drive and server B has a 15K rpm drive. Seek/settle times aren't spectacularly different between the 2 drives. I'm wondering if drive B might actually merit a _higher_ random_page_cost than drive A as, once it gets settled on a disk track, it can suck the data off a lot faster. opinions/experiences anyone? -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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 to the mailing list cleanly
Re: [PERFORM] Scaling further up
On 02/03/2004 23:25 johnn wrote: [snip] random_page_cost should be set with the following things taken into account: - seek speed Which is not exactly the same thing as spindle speed as it's a combination of spindle speed and track-to-track speed. I think you'll find that a 15K rpm disk, whilst it will probably have a lower seek time than a 10K rpm disk, won't have a proportionately (i.e., 2/3rds) lower seek time. - likelihood of page to be cached in memory by the kernel That's effective cache size. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] Compile Vs RPMs
On 03/02/2004 20:58 Anjan Dave wrote: Hello, I would like to know whether there are any significant performance advantages of compiling (say, 7.4) on your platform (being RH7.3, 8, and 9.0, and Fedora especially) versus getting the relevant binaries (rpm) from the postgresql site? Hardware is Intel XEON (various speeds, upto 2.8GHz, single/dual/quad configuration). Very unlikely I would have thought. Databases tend to speed-limited by I-O performance and the amount of RAM available for caching etc. Having said that, I've only got one machine (the laptop on which I'm writing this email) which has still got its rpm binaries. My other machines have all been upgraded from source. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] go for a script! / ex: PostgreSQL vs. MySQL
On 09/10/2003 09:29 Oliver Scheit wrote: Hi guys, I followed the discussion and here are my 0.2$: I think instead of thinking about where to put the information about tuning, someone should provide a "pgsql-autotune". Maybe even a shell script would do the trick. It's not so hard to find out, how much memory is installed, and IMHO SHARED_BUFFERS, SORT_MEM and EFFECTIVE_CACHE_SIZE depend heavily on this. a "cat /proc/sys/kernel/shmmax" would give some valuable information on linux boxes, there is probably other stuff for different OSes. random_page_cost could be set after probing the harddisks, maybe even do a hdparm -tT if they seem to be ATA, not SCSI. Now, let's pretend the script finds out there is 1 GB RAM, it could ask something like "Do you want to optimize the settings for postgres (other applications may suffer from having not enough RAM) or do you want to use moderate settings?" Something like this, you get the idea. ISR reading that 7.4 will use a default of shared_beffers = 1000 if the machine can support it (most can). This alone should make a big difference in out-of-the-box performance. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Attempt at work around of int4 query won't touch int8 index ...
On 15/09/2003 08:42 Shridhar Daithankar wrote: Is it possible to follow data type upgrade model in planner? Something like in C/C++ where data types are promoted upwards to find out better plan? int2->int4->int8->float4->float8 types. That could be a clean solution.. just a thought.. Interestingly, float8 indexes do work OK (float8col = 99). I spend a large part of yesterday grepping through the sources to try and find out why this should be so. No luck so far but I'm going to keep on trying! -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Tests
On 22/08/2003 22:00 Bill Moran wrote: [snip] Well, as much as I _am_ a die-hard BSD geek, I'm far more interested in knowing what platform is really best when I need a top-notch PostgreSQL server. I'm going to try to force some time this weekend to do some tests ... we'll see if I succeed ... I, for one, would be very interested to see your results (can't you find something better to at the weekend than about with computers ?:) What I'd really be interested in is a comparison of Linux vs BSD using each OS's variations of file system on the same single-processor Intel/AMD based hardware. Selfishness and sillyness aside, I'm sure your tests will of interest to us all. Thanks in advance -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] Perfomance Tuning
On 08/08/2003 11:28 mixo wrote: I have just installed redhat linux 9 which ships with Pg 7.3.2. Pg has to be setup so that data inserts (blobs) should be able to handle at least 8M at a time. The machine has two P III 933MHz CPU's, 1.128G RAM (512M*2 + 128M), and a 36 Gig hd with 1 Gig swap and 3 equal size ext3 partitions. What would be the recomended setup for good performance considering that the db will have about 15 users for 9 hours in a day, and about 10 or so users throughout the day who wont be conistenly using the db. It doesn't sound like a particlarly heavy loading to me. I'd start off with something like shared_buffers = 2000 sort_mem = 1024 max_coonections = 100 and see how it performs under normal business loading. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] How to force PostgreeSQL to work faster?
On 11/08/2003 09:59 Serge Dorofeev wrote: Hi PostrgeSQL team, My PostrgeSQL installed as part of CYGWIN (Windows XP). I have compared performance PostrgeSQL to MS SQL (I used a little Java program with number of inserts in table). MS SQL is faster in 12 times :-( It's very strange results. Guys who developed this server: what you can tell in this - what customizations needs to increase of productivity? How to force PostgreeSQL to work faster? Speed (inserts/sec) Elapsed time (ms) MS SQL (Average): 295 39 869 testInsert 5000 263 18 977 255 19 619 306 16 334 testInsert 1 315 31 716 324 30 905 319 31 325 testInsert 2 241 82 919 313 63 922 317 63 101 PostrgreSQL (Average): 24 520 160 testInsert 5000 26 191 434 26 191 264 26 192 295 testInsert 1 22 463 669 25 393 510 24 409 528 testInsert 2 24 834 911 17 1 184 613 24 820 218 MS SQL is faster (times): 12 13 You don't give any details about your test code or how the databases are configured so I'm guessing that you're inserts use an autocommitting connection. For PostgreSQL, this causes each insert to be run inside a tranaction and the transaction is then immediately written to disk. My guess is that MS SQL behaves differently and doesn't immediately write to disk (faster maybe but could cause data corruption). Try modifying your program to have connection.setAutoCommit(false) and do a connection.commit() after say every 100 inserts. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Clearing rows periodically
On 18/07/2003 07:55 Martin Foster wrote: [snip] A VACUUM FULL will correct the issue, but put the site out of commission for roughly 20 minutes as the drive crunches the information. My question is, should the purging of rows be done more often then once a day for both tables. Is this why performance seems to take a hit specifically? As there were too many rows purged for vacuum to accurately keep track of? ISTR that there are setting in postgresql.conf which affect how many tables/rows vacuum can reclaim. The docs say that the default setting of max_fsm_pages is 1. Maybe this should be increased for your situation? HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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 to the mailing list cleanly
Re: [PERFORM] index / sequential scan problem
On 17/07/2003 13:50 Fabian Kreitner wrote: [snip] Im afraid, no. Database has been stopped / started right before this. [snip] 1) enable_seqscan = true Seq Scan on notiz_objekt a (cost=0.00..56125.80 rows=15561 width=12) (actual time=0.28..2298.71 rows=31122 loops=1) [snip] 2) enable_seqscan = false Seq Scan on notiz_objekt a (cost=1.00..100111719.36 rows=15561 width=12) (actual time=0.25..535.75 rows=31122 loops=1) I've just noticed this. Something is not right here. Look at the crazy cost estimation for the second query. It looks to me like enable_indexscan, enable_tidscan, enable_sort, enable_nestloop, enable_mergejoin or enable_hashjoin have been set to false. Looking at the source, thats the only way I can see that such large numbers can be produced. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] index / sequential scan problem
On 17/07/2003 12:13 Fabian Kreitner wrote: That is what I read too and is why Im confused that the index is indeed executing faster. Can this be a problem with the hardware and/or postgress installation? It's more likely that the OS has most of the data cached after the first query and so doesn't need to re-read that data from disk when you retry the query with seq scan disabled. Try something like this: set enable_seqscan to true; explain analyze .. set enable_seqscan to false; explain analyze .. set enable_seqscan to true; explain analyze .. I expect you will find that the third query is also a lot faster that the first query. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] index / sequential scan problem
On 17/07/2003 10:01 Fabian Kreitner wrote: Hi Fabian, When you are doing these kinds of tests, you need to be aware that the kernel may have most of your data cached after the first query and this may be why the second query appears to run faster. Also don't be worried if the planner chooses a seq scan for small tables as the whole table can often be bought into memory with one IO whereas reading the index then the table would be 2 IOs. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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 to the mailing list cleanly
Re: [PERFORM] Pgsql - Red Hat Linux - VS MySQL VS MSSQL
On 13/07/2003 20:51 Balazs Wellisch wrote: [snip] > > So, does anyone here have any experience using RH AS and DB 2.1? > > Are RH still selling DB 2.1? I can't find it listed on their web site. > -- Yes, it's available for free download. The documentation is here: http://www.redhat.com/docs/manuals/database/. I'd welcome your oppinions on this product. Thank you for your comments. It looks like they just wrote a number of GUI versions of the command line utilities. From what I can tell, its still a standard postgresql database behind the scenes. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] raise or lower transaction timeout?
On 02/07/2003 21:04 Matthew Hixson wrote: We currently have a public website that is serving customers, or at least trying to. This machine is underpowered but we are going to be upgrading soon. In the meantime we need to keep the current site alive. We are running a Java application server. It is receiving 'transaction timed out' SQLExceptions from the JDBC driver. I am wondering if it would be better to raise the transaction timeout or to lower it. On one hand it seems like raising it might improve things. It might let the transactions complete, even though it would make the user experience less enjoyable having to wait longer. On the other hand I could see raising the transaction timeout just cause there to be more transactions in process which would thereby degrade performance since the machine would have even more work to do. Would, in fact, lowering the transaction timeout at least cause the machine to fail fast and return either an error or the page in a more timely manner on a per-user level? I'd like to keep people visiting the site while at the same time relieving some stress from the machine. We have also done little to no performance tuning of Postgres' configuration. We do have indexes on all of the important columns and we have reindexed. Any pointers would be greatly appreciated. As well as the tuning postgresql advice which others have given, there's another thing you could try: Assuming you're using connection pooling, try reducing the maximum number of connections. This will take some of the stress off the database. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance advice
On 25/06/2003 10:47 Michael Mattox wrote: I'm using Java Data Objects (JDO) which is an O/R mapper. It generated the schema from my object model by default it used a table for a sequence. I just got finished configuring it to use a real postgres sequence. With the way they have it designed, it opens and closes a connection each time it retrieves a sequence. Would I get a performance increase if I modify their code to retrieve multiple sequence numbers in one connection? For example I could have it grab 50 at a time, which would replace 50 connections with 1. For best performance, you really should consider using a connection pool as it removes the overhead of creating and closing connections. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
