[PERFORM] Speed Up Offset and Limit Clause
Hi! How can I speed up my server's performance when I use offset and limit clause. For example I have a query: SELECT * FROM table ORDER BY id, name OFFSET 10 LIMIT 1 This query takes a long time about more than 2 minutes. If my query is: SELECT * FROM table ORDER BY id, name OFFSET 5 LIMIT 1 It takes about 2 seconds. Thanks ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Speed Up Offset and Limit Clause
Christian Paul Cosinas wrote: Hi! How can I speed up my server's performance when I use offset and limit clause. For example I have a query: SELECT * FROM table ORDER BY id, name OFFSET 10 LIMIT 1 This query takes a long time about more than 2 minutes. If my query is: SELECT * FROM table ORDER BY id, name OFFSET 5 LIMIT 1 It takes about 2 seconds. Please create a new thread rather than replying to someone elses post and changing the subject. These threads can sometimes get missed. You do have an index on id and name don't you? -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Speed Up Offset and Limit Clause
Why do you want to use it this way ? Explain what you want to do, there probably is another faster solution... On Thu, 11 May 2006 16:45:33 +0200, Christian Paul Cosinas [EMAIL PROTECTED] wrote: Hi! How can I speed up my server's performance when I use offset and limit clause. For example I have a query: SELECT * FROM table ORDER BY id, name OFFSET 10 LIMIT 1 This query takes a long time about more than 2 minutes. If my query is: SELECT * FROM table ORDER BY id, name OFFSET 5 LIMIT 1 It takes about 2 seconds. Thanks ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: 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] Speed Up Offset and Limit Clause
Christian Paul Cosinas cpc 'at' cybees.com writes: Hi! How can I speed up my server's performance when I use offset and limit clause. For example I have a query: SELECT * FROM table ORDER BY id, name OFFSET 10 LIMIT 1 This query takes a long time about more than 2 minutes. If my query is: SELECT * FROM table ORDER BY id, name OFFSET 5 LIMIT 1 It takes about 2 seconds. First you should read the appropriate documentation. http://www.postgresql.org/docs/8.1/interactive/performance-tips.html -- Guillaume Cottenceau ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal
On Thu, May 11, 2006 at 09:55:15AM +0200, Zeugswetter Andreas DCP SD wrote: 0.101 ms BEGIN 1.451 ms CREATE TEMPORARY TABLE tmp ( a INTEGER NOT NULL, b INTEGER NOT NULL, c TIMESTAMP NOT NULL, d INTEGER NOT NULL ) ON COMMIT DROP 1.4 seconds is not great for create table, is that what we expect ? Hmm, I'm hoping ms means milliseconds... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[PERFORM] Nested Loops vs. Hash Joins or Merge Joins
Title: Nested Loops vs. Hash Joins or Merge Joins I am attempting to learn more about the way Pg decides what operators to use in its query planning and executions. I have moderately complicated table layout, but it is mostly normalized I recently created a query: select account.acct_name as Customer Name, NULL as Facility, account.acct_number as LDC Acct#, account.svc_address as Service Address, account.svc_address as Service Address, account.svc_city as Service City, account.svc_state as Service State, account.svc_city as Service City, account.svc_zip as Service Zip, product.ldc_name as LDC, NULL as ESI Rate, NULL as LDC Rate, account.billing_address as Mailing Address1, account.billing_address_2 as Mailing Address2, account.billing_city || ', ' || account.billing_state as City, State, account.billing_zip as Zip, customer.first_name || ' ' || customer.last_name as Contact, customer.phone as Phone, customer.class as Customer Class, NULL as Tax Exempt, NULL as Exempt%, marketer_divisions.channel_partner_code as Channel Partner, NULL as AE, NULL as Annual Use MCF, account.rate as Trigger Price, marketer_divisions.channel_partner_fee as Channel Partner Fee from naes.reconciliation inner join naes.application inner join naes.account inner join naes.marketer_product inner join naes.marketer_divisions inner join naes.cities on marketer_divisions.city_id = cities.city_id on marketer_product.division_id = marketer_divisions.division_id inner join naes.product on marketer_product.ldc_id = product.ldc_id on account.marketer_product_id = marketer_product.marketer_product_id inner join naes.customer on account.customer_id = customer.customer_id on account.app_id = application.app_id and account.acct_id = application.acct_id on reconciliation.app_id = application.app_id and reconciliation.transferred_date is NULL; The query runs fine I have no performance issues with it, but here are two query plans for the above query, one with nested loops on, the other with them off: Nested Loops on: Nested Loop (cost=3.33..11.37 rows=1 width=268) (actual time=2.166..2.982 rows=3 loops=1) Join Filter: (outer.city_id = inner.city_id) - Nested Loop (cost=3.33..10.32 rows=1 width=272) (actual time=2.136..2.863 rows=3 loops=1) Join Filter: (outer.division_id = inner.division_id)plication.app_id and reco= - Nested Loop (cost=3.33..9.27 rows=1 width=231) (actual time=2.119..2.763 rows=3 loops=1) Join Filter: (outer.ldc_id = inner.ldc_id) - Nested Loop (cost=3.33..8.23 rows=1 width=218) (actual time=2.101..2.659 rows=3 loops=1) - Nested Loop (cost=3.33..5.15 rows=1 width=151) (actual time=2.068..2.559 rows=3 loops=1) Join Filter: (inner.app_id = outer.app_id) - Merge Join (cost=3.33..4.11 rows=1 width=159) (actual time=1.096..1.477 rows=31 loops=1) Merge Cond: (outer.marketer_product_id = inner.marketer_product_id) - Index Scan using PK_marketer_product_id on marketer_product (cost=0.00..3.04 rows=4 width=12) (actual time=0.017..0.033 rows=4 loops=1) - Sort (cost=3.33..3.33 rows=1 width=155) (actual time=1.065..1.180 rows=31 loops=1) Sort Key: account.marketer_product_id - Hash Join (cost=1.75..3.32 rows=1 width=155) (actual time=0.457..0.848 rows=31 loops=1) Hash Cond: ((outer.app_id = inner.app_id) AND (outer.acct_id = inner.acct_id)) - Seq Scan on account (cost=0.00..1.28 rows=28 width=155) (actual time=0.007..0.160 rows=34 loops=1) - Hash (cost=1.50..1.50 rows=50 width=8) (actual time=0.413..0.413 rows=50 loops=1) - Seq Scan on application (cost=0.00..1.50 rows=50 width=8) (actual time=0.006..0.209 rows=50 loops=1) - Seq Scan on reconciliation (cost=0.00..1.03 rows=1 width=4) (actual time=0.005..0.016 rows=3 loops=31) Filter: (transferred_date IS NULL) - Index Scan using customer_pkey on customer (cost=0.00..3.06 rows=1 width=75) (actual time=0.011..0.015 rows=1 loops=3) Index Cond: (outer.customer_id = customer.customer_id) - Seq Scan on product (cost=0.00..1.02 rows=2 width=21) (actual time=0.005..0.013 rows=2 loops=3) - Seq Scan on marketer_divisions (cost=0.00..1.02 rows=2 width=49) (actual time=0.005..0.013 rows=2 loops=3) - Seq Scan on cities (cost=0.00..1.02 rows=2 width=4) (actual time=0.005..0.013 rows=2 loops=3) Total runtime: 3.288 ms Nested Loops off: Hash Join (cost=8.27..11.78 rows=1 width=268) (actual time=1.701..1.765 rows=3 loops=1) Hash Cond: (outer.city_id = inner.city_id) - Hash Join (cost=7.24..10.73 rows=1 width=272) (actual time=1.629..1.667 rows=3 loops=1) Hash Cond: (outer.customer_id = inner.customer_id) - Seq Scan on customer (cost=0.00..3.32 rows=32 width=75) (actual time=0.006..0.136 rows=33 loops=1) - Hash (cost=7.24..7.24 rows=1 width=205) (actual time=1.366..1.366 rows=3 loops=1) - Hash Join (cost=6.43..7.24 rows=1 width=205) (actual time=1.243..1.333 rows=3 loops=1) Hash Cond: (outer.division_id = inner.division_id) - Hash Join (cost=5.40..6.20 rows=1 width=164) (actual time=1.184..1.252 rows=3 loops=1) Hash Cond: (outer.ldc_id = inner.ldc_id) - Merge Join (cost=4.38..5.16 rows=1 width=151)
Re: [PERFORM] Postgres gets stuck
Craig A. James [EMAIL PROTECTED] wrote I'm having a rare but deadly problem. On our web servers, a process occasionally gets stuck, and can't be unstuck. Once it's stuck, all Postgres activities cease. kill -9 is required to kill it -- signals 2 and 15 don't work, and /etc/init.d/postgresql stop fails. Details: Postgres 8.0.3 [Scanning 8.0.4 ~ 8.0.7 ...] Didn't find related bug fix in the upgrade release. Can you attach to the problematic process and bt it (so we could see where it stucks)? Regards, Qingqing ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Postgres gets stuck
Chris wrote: This is a deadly bug, because our web site goes dead when this happens, ... Sounds like a deadlock issue. ... stats_command_string = true and restart postgresql. then you'll be able to: select * from pg_stat_activity; to see what queries postgres is running and that might give you some clues. Thanks, good advice. You're absolutely right, it's stuck on a mutex. After doing what you suggest, I discovered that the query in progress is a user-written function (mine). When I log in as root, and use gdb -p pid to attach to the process, here's what I find. Notice the second function in the stack, a mutex lock: (gdb) bt #0 0x0087f7a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2 #1 0x0096cbfe in __lll_mutex_lock_wait () from /lib/tls/libc.so.6 #2 0x008ff67b in _L_mutex_lock_3220 () from /lib/tls/libc.so.6 #3 0x4f5fc1b4 in ?? () #4 0x00dc5e64 in std::string::_Rep::_S_empty_rep_storage () from /usr/local/pgsql/lib/libchmoogle.so #5 0x009ffcf0 in ?? () from /usr/lib/libz.so.1 #6 0xbfe71c04 in ?? () #7 0xbfe71e50 in ?? () #8 0xbfe71b78 in ?? () #9 0x009f7019 in zcfree () from /usr/lib/libz.so.1 #10 0x009f7019 in zcfree () from /usr/lib/libz.so.1 #11 0x009f8b7c in inflateEnd () from /usr/lib/libz.so.1 #12 0x00c670a2 in ~basic_unzip_streambuf (this=0xbfe71be0) at zipstreamimpl.h:332 #13 0x00c60b61 in OpenBabel::OBConversion::Read (this=0x1, pOb=0xbfd923b8, pin=0xffea) at istream:115 #14 0x00c60fd8 in OpenBabel::OBConversion::ReadString (this=0x8672b50, pOb=0xbfd923b8) at obconversion.cpp:780 #15 0x00c19d69 in chmoogle_ichem_mol_alloc () at stl_construct.h:120 #16 0x00c1a203 in chmoogle_ichem_normalize_parent () at stl_construct.h:120 #17 0x00c1b172 in chmoogle_normalize_parent_sdf () at vector.tcc:243 #18 0x0810ae4d in ExecMakeFunctionResult () #19 0x0810de2e in ExecProject () #20 0x08115972 in ExecResult () #21 0x08109e01 in ExecProcNode () #22 0x0020 in ?? () #23 0xbed4b340 in ?? () #24 0xbf92d9a0 in ?? () #25 0xbed4b0c0 in ?? () #26 0x in ?? () It looks to me like my code is trying to read the input parameter (a fairly long string, maybe 2K) from a buffer that was gzip'ed by Postgres for the trip between the client and server. My suspicion is that it's an incompatibility between malloc() libraries. libz (gzip compression) is calling something called zcfree, which then appears to be intercepted by something that's (probably statically) linked into my library. And somewhere along the way, a mutex gets set, and then ... it's stuck forever. ps(1) shows that this thread had been running for about 7 hours, and the job status showed that this function had been successfully called about 1 million times, before this mutex lock occurred. Any ideas? Thanks, Craig ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal
Jim C. Nasby [EMAIL PROTECTED] writes: Perhaps it would be worth creating a class of temporary tables that used a tuplestore, although that would greatly limit what could be done with that temp table. I can say that I've seen plenty of instances where the ability to create temporary tables very quickly with no overhead over the original query would be useful. For instance, in one site I had to do exactly what I always advise others against: use offset/limit to implement paging. So first I have to execute the query with a count(*) aggregate to get the total, then execute the same query a second time to fetch the actual page of interest. This would be (or could be arranged to be) within the same transaction and doesn't require the ability to execute any dml against the tuple store which I imagine would be the main issues? For bonus points what would be real neat would be if the database could notice shared plan segments, keep around the materialized tuple store, and substitute it instead of reexecuting that segment of the plan. Of course this requires keeping track of transaction snapshot states and making sure it's still correct. Something else worth considering is not using the normal catalog methods for storing information about temp tables, but hacking that together would probably be a rather large task. It would be nice if using this feature didn't interact poorly with preplanning all your queries and using the cached plans. Perhaps if you had some way to create a single catalog entry that defined all the column names and types and then simply pointed it at a new tuplestore each time without otherwise altering the catalog entry? -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal
On Wed, May 10, 2006 at 08:31:54PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, May 09, 2006 at 03:13:01PM -0400, Tom Lane wrote: PFC [EMAIL PROTECTED] writes: Fun thing is, the rowcount from a temp table (which is the problem here) should be available without ANALYZE ; as the temp table is not concurrent, it would be simple to inc/decrement a counter on INSERT/DELETE... No, because MVCC rules still apply. But can anything ever see more than one version of what's in the table? Yes, because there can be more than one active snapshot within a single transaction (think about volatile functions in particular). Any documentation on how snapshot's work? They're a big mystery to me. :( Speaking of which, if a temp table is defined as ON COMMIT DROP or DELETE ROWS, there shouldn't be any need to store xmin/xmax, only cmin/cmax, correct? No; you forgot about subtransactions. Oh, I thought those were done with cmin and cmax... if that's not what cmin/cmax are for, then what is? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal
On Thu, May 11, 2006 at 12:18:06PM -0500, Jim C. Nasby wrote: Yes, because there can be more than one active snapshot within a single transaction (think about volatile functions in particular). Any documentation on how snapshot's work? They're a big mystery to me. :( A snapshot is a particular view on a database. In particular, you have to be able to view a version of the database that doesn't have you own changes, otherwise an UPDATE would keep updating the same tuple. Also, for example, a cursor might see an older version of the database than queries being run. I don't know of any particular information about it though. Google wasn't that helpful. No; you forgot about subtransactions. Oh, I thought those were done with cmin and cmax... if that's not what cmin/cmax are for, then what is? cmin/cmax are command counters. So in the sequence: BEGIN; SELECT 1; SELECT 2; The second query runs as the same transaction ID but a higher command ID so it can see the result of the previous query. Subtransactions are (AIUI anyway) done by having transactions depend on other transactions. When you start a savepoint you start a new transaction ID whose status is tied to its top-level transaction ID but can also be individually rolledback. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal
On Thu, May 11, 2006 at 11:35:34AM -0400, Greg Stark wrote: I can say that I've seen plenty of instances where the ability to create temporary tables very quickly with no overhead over the original query would be useful. I wonder if this requires what the standard refers to as a global temporary table. As I read it (which may be wrong, I find the language obtuse), a global temporary table is a temporary table whose structure is predefined. So, you'd define it once, updating the catalog only once but still get a table that is emptied each startup. Ofcourse, it may not be what the standard means, but it still seems like a useful idea, to cut down on schema bloat. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal
On Thu, May 11, 2006 at 08:03:19PM +0200, Martijn van Oosterhout wrote: On Thu, May 11, 2006 at 12:18:06PM -0500, Jim C. Nasby wrote: Yes, because there can be more than one active snapshot within a single transaction (think about volatile functions in particular). Any documentation on how snapshot's work? They're a big mystery to me. :( A snapshot is a particular view on a database. In particular, you have to be able to view a version of the database that doesn't have you own changes, otherwise an UPDATE would keep updating the same tuple. Also, for example, a cursor might see an older version of the database than queries being run. I don't know of any particular information about it though. Google wasn't that helpful. Ahh, I'd forgotten that commands sometimes needed to see prior data. But that's done with cmin/max, right? In any case, going back to the original thought/question... my point was that in a single-session table, it should be possible to maintain a row counter. Worst case, you might have to keep a seperate count for each CID or XID, but that doesn't seem that unreasonable for a single backend to do, unless you end up running a heck of a lot of commands. More importantnly, it seems a lot more feasable to at least know how many rows there are every time you COMMIT, which means you can potentially avoid having to ANALYZE. No; you forgot about subtransactions. Oh, I thought those were done with cmin and cmax... if that's not what cmin/cmax are for, then what is? cmin/cmax are command counters. So in the sequence: BEGIN; SELECT 1; SELECT 2; The second query runs as the same transaction ID but a higher command ID so it can see the result of the previous query. Subtransactions are (AIUI anyway) done by having transactions depend on other transactions. When you start a savepoint you start a new transaction ID whose status is tied to its top-level transaction ID but can also be individually rolledback. Hmmm, interesting. I would have thought it was tied to CID, but I guess XID has more of that machinery around to support rollback. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal
On Thu, May 11, 2006 at 08:43:46PM +0200, Martijn van Oosterhout wrote: On Thu, May 11, 2006 at 11:35:34AM -0400, Greg Stark wrote: I can say that I've seen plenty of instances where the ability to create temporary tables very quickly with no overhead over the original query would be useful. I wonder if this requires what the standard refers to as a global temporary table. As I read it (which may be wrong, I find the language obtuse), a global temporary table is a temporary table whose structure is predefined. So, you'd define it once, updating the catalog only once but still get a table that is emptied each startup. Ofcourse, it may not be what the standard means, but it still seems like a useful idea, to cut down on schema bloat. IIRC that's the exact syntax Oracle uses: CREATE GLOBAL TEMPORARY TABLE ... I always found it a bit odd, since it always seemed to me like a global temporary table would be one that every backend could read... something akin to a real table that doesn't worry about fsync or any of that (and is potentially not backed on disk at all). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal
On Thu, May 11, 2006 at 09:55:15AM +0200, Zeugswetter Andreas DCP SD wrote: Something else worth considering is not using the normal catalog methods for storing information about temp tables, but hacking that together would probably be a rather large task. But the timings suggest, that it cannot be the catalogs in the worst case he showed. 0.101 ms BEGIN 1.451 ms CREATE TEMPORARY TABLE tmp ( a INTEGER NOT NULL, b INTEGER NOT NULL, c TIMESTAMP NOT NULL, d INTEGER NOT NULL ) ON COMMIT DROP 1.4 seconds is not great for create table, is that what we expect ? milliseconds... :) Given the amount of code and locking that it looks like is involved in creating a table, that might not be unreasonable... 0.450 ms INSERT INTO tmp SELECT * FROM bookmarks ORDER BY annonce_id DESC LIMIT 20 0.443 ms ANALYZE tmp 0.365 ms SELECT * FROM tmp 0.310 ms DROP TABLE tmp 32.918 ms COMMIT CREATING the table is OK, but what happens on COMMIT ? I hear the disk seeking frantically. The 32 seconds for commit can hardly be catalog related. It seems the file is fsynced before it is dropped. I'd hope that wasn't what's happening... is the backend smart enough to know not to fsync anything involved with the temp table? ISTM that that transaction shouldn't actually be creating any WAL traffic at all. Though on the other hand there's no reason that DROP should be in the transaction at all; maybe that's gumming things up during the commit. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] slow variable against int??
If you're trying to come up with ranking then you'll be much happier using a sequence and pulling from it using an ordered select. See lines 19-27 in http://lnk.nu/cvs.distributed.net/9bu.sql for an example. Depending on what you're doing you might not need the temp table. On Fri, May 05, 2006 at 04:46:43PM +0200, Witold Strzelczyk wrote: I have a question about my function. I must get user rating by game result. This isn't probably a perfect solution but I have one question about select into inGameRating count(game_result)+1 from users where game_result inRow.game_result; This query in function results in about 1100 ms. inRow.game_result is a integer 2984 And now if I replace inRow.game_result with integer select into inGameRating count(game_result)+1 from users where game_result 2984; query results in about 100 ms There is probably a reason for this but can you tell me about it because I can't fine one My function: create or replace function ttt_result(int,int) returns setof tparent_result language plpgsql volatile as $$ declare inOffset alias for $1; inLimit alias for $2; inRow tparent_result%rowtype; inGameResult int := -1; inGameRating int := -1; begin for inRow in select email,wynik_gra from konkurs_uzytkownik order by wynik_gra desc limit inLimit offset inOffset loop if inGameResult 0 then -- only for first iteration /* this is fast ~100 ms select into inGameRating count(game_result)+1 from users where game_result 2984; */ /* even if inRow.game_result = 2984 this is very slow ~ 1100 ms! select into inGameRating count(game_result)+1 from users where game_result inRow.game_result; */ inGameResult := inRow.game_result; end if; if inGameResult inRow.game_result then inGameRating := inGameRating + 1; end if; inRow.game_rating := inGameRating; inGameResult := inRow.game_result; return next inRow; end loop; return; end; $$; -- Witold Strzelczyk [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Dynamically loaded C function performance
On Fri, May 05, 2006 at 03:47:53PM -0700, Adam Palmblad wrote: Hi, We've got a C function that we use here and we find that for every connection, the first run of the function is much slower than any subsequent runs. ( 50ms compared to 8ms) Besides using connection pooling, are there any options to improve performance? In my experience, connection startup takes a heck of a lot longer than 50ms, so why are you worrying about 50ms for the first run of a function? BTW, sorry, but I don't know a way to speed this up, either. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Assistance with optimizing query - same SQL, different category_id = Seq Scan
On Mon, May 08, 2006 at 07:29:32PM -0600, Brendan Duddridge wrote: Do you have any suggestions on how I can optimize the query so both versions of the query come back fast without doing a sequential scan on the price table? Well, before you do anything you should verify that an index scan in the second case would actually be faster. Set enable_seqscan=off and check that. After that, you can favor an index scan by (in order of effectiveness) increasing the correlation on the appropriate index (by clustering on it), lowering random_page_cost, or increasing effective_cache_size. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal
Jim C. Nasby [EMAIL PROTECTED] writes: I'd hope that wasn't what's happening... is the backend smart enough to know not to fsync anything involved with the temp table? The catalog entries required for it have to be fsync'd, unless you enjoy putting your entire database at risk (a bad block in pg_class, say, would probably take out more than one table). It's interesting to speculate about keeping such catalog entries in child tables of pg_class etc that are themselves temp tables. Resolving the apparent circularity of this is left as an exercise for the reader. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [PERFORM] Arguments Pro/Contra Software Raid
On Tue, May 09, 2006 at 08:59:55PM -0400, Bruce Momjian wrote: Joshua D. Drake wrote: Vivek Khera wrote: On May 9, 2006, at 11:51 AM, Joshua D. Drake wrote: Sorry that is an extremely misleading statement. SATA RAID is perfectly acceptable if you have a hardware raid controller with a battery backup controller. And dollar for dollar, SCSI will NOT be faster nor have the hard drive capacity that you will get with SATA. Does this hold true still under heavy concurrent-write loads? I'm preparing yet another big DB server and if SATA is a better option, I'm all (elephant) ears. I didn't say better :). If you can afford, SCSI is the way to go. However SATA with a good controller (I am fond of the LSI 150 series) can provide some great performance. Basically, you can get away with cheaper hardware, but it usually doesn't have the reliability/performance of more expensive options. You want an in-depth comparison of how a server disk drive is internally better than a desktop drive: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf BTW, someone (Western Digital?) is now offering SATA drives that carry the same MTBF/warranty/what-not as their SCSI drives. I can't remember if they actually claim that it's the same mechanisms just with a different controller on the drive... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid
On Tue, May 09, 2006 at 12:10:32PM +0200, Jean-Yves F. Barbier wrote: I myself can't see much reason to spend $500 on high end controller cards for a simple Raid 1. Naa, you can find ATA | SATA ctrlrs for about EUR30 ! And you're likely getting what you paid for: crap. Such a controller is less likely to do things like turn of write caching so that fsync works properly. + Hardware Raids might be a bit easier to manage, if you never spend a few hours to learn Software Raid Tools. I'd the same (mostly as you still have to punch a command line for most of the controlers) Controllers I've seen have some kind of easy to understand GUI, at least during bootup. When it comes to OS-level tools that's going to vary widely. + There are situations in which Software Raids are faster, as CPU power has advanced dramatically in the last years and even high end controller cards cannot keep up with that. Definitely NOT, however if your server doen't have a heavy load, the software overload can't be noticed (essentially cache managing and syncing) For bi-core CPUs, it might be true Depends. RAID performance depends on a heck of a lot more than just CPU. Software RAID allows you to do things like spread load across multiple controllers, so you can scale a lot higher for less money. Though in this case I doubt that's a consideration, so what's more important is that making sure the controller bus isn't in the way. One thing that means is ensuring that every SATA drive has it's own dedicated controller, since a lot of SATA hardware can't handle multiple commands on the bus at once. + Using SATA drives is always a bit of risk, as some drives are lying about whether they are caching or not. ?? Do you intend to use your server without a UPS ?? Have you never heard of someone tripping over a plug? Or a power supply failing? Or the OS crashing? If fsync is properly obeyed, PostgreSQL will gracefully recover from all of those situations. If it's not, you're at risk of losing the whole database. + Using hardware controllers, the array becomes locked to a particular vendor. You can't switch controller vendors as the array meta information is stored proprietary. In case the Raid is broken to a level the controller can't recover automatically this might complicate manual recovery by specialists. ?? Do you intend not to make backups ?? Even with backups this is still a valid concern, since the backup will be nowhere near as up-to-date as the database was unless you have a pretty low DML rate. BUT a hardware controler is about EUR2000 and a (ATA/SATA) 500GB HD is ~ EUR350. Huh? You can get 3ware controllers for about $500, and they're pretty decent. While I'm sure there are controllers for $2k that doesn't mean there's nothing inbetween that and nothing. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Question about explain-command...
On Wed, May 10, 2006 at 09:47:07AM -0500, Dave Dutcher wrote: The hash lines mean your tables are being joined by hash joins. You should read this page for more info: http://www.postgresql.org/docs/8.1/interactive/performance-tips.html tooting-own-hornYou might also want to read http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10120query=explain -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Dynamically loaded C function performance
Jim C. Nasby wrote: On Fri, May 05, 2006 at 03:47:53PM -0700, Adam Palmblad wrote: Hi, We've got a C function that we use here and we find that for every connection, the first run of the function is much slower than any subsequent runs. ( 50ms compared to 8ms) Besides using connection pooling, are there any options to improve performance? In my experience, connection startup takes a heck of a lot longer than 50ms, so why are you worrying about 50ms for the first run of a function? BTW, sorry, but I don't know a way to speed this up, either. I think Tom nailed the solution already in a nearby reply -- see preload_libraries on this page: http://www.postgresql.org/docs/8.1/interactive/runtime-config-resource.html Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Nested Loops vs. Hash Joins or Merge Joins
On Thu, May 11, 2006 at 08:57:48AM -0400, Ketema Harris wrote: Nested Loops on: Nested Loop (cost=3.33..11.37 rows=1 width=268) (actual time=2.166..2.982 Nested Loops off: Hash Join (cost=8.27..11.78 rows=1 width=268) (actual time=1.701..1.765 With nested loops enabled does it choose to use them because it sees the estimated start up cost with loops as less? Does it not know that the total query would be faster with the Hash Joins? This query is in development Yes it does know; re-read the output. I believe the cases where the planner will look at startup cost over total cost are pretty limited; when LIMIT is used and I think sometimes when a CURSOR is used. Statistics collecting and auto vacuum is enabled btw. I have an erd diagram showing the table structures if anyone is interested in looking at it, just let me know. Note that it's not terribly uncommon for the default stats target to be woefully inadequate for large sets of data, not that 100 rows a day is large. But it probably wouldn't hurt to bump the defaulst stats target up to 30 or 50 anyway. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [HACKERS] Big IN() clauses etc : feature proposal
On Thu, May 11, 2006 at 06:08:36PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: I'd hope that wasn't what's happening... is the backend smart enough to know not to fsync anything involved with the temp table? The catalog entries required for it have to be fsync'd, unless you enjoy putting your entire database at risk (a bad block in pg_class, say, would probably take out more than one table). Yeah, thought about that after sending... :( It's interesting to speculate about keeping such catalog entries in child tables of pg_class etc that are themselves temp tables. Resolving the apparent circularity of this is left as an exercise for the reader. Well, since it'd be a system table with a fixed OID there could presumably be a special case in the recovery code for it, though that's pretty fugly sounding. Another alternative would be to support global temp tables... I think that would handle all the complaints of the OP except for the cost of analyze. I suspect this would be easier to do than creating a special type of temp table that used tuplestore instead of the full table framework, and it'd certainly be more general-purpose. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [PERFORM] Arguments Pro/Contra Software Raid
You want an in-depth comparison of how a server disk drive is internally better than a desktop drive: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf BTW, someone (Western Digital?) is now offering SATA drives that carry the same MTBF/warranty/what-not as their SCSI drives. I can't remember if they actually claim that it's the same mechanisms just with a different controller on the drive... Well western digital and Seagate both carry 5 year warranties. Seagate I believe does on almost all of there products. WD you have to pick the right drive. Joshua D Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [PERFORM] Arguments Pro/Contra Software Raid
On Thu, May 11, 2006 at 03:38:31PM -0700, Joshua D. Drake wrote: You want an in-depth comparison of how a server disk drive is internally better than a desktop drive: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf BTW, someone (Western Digital?) is now offering SATA drives that carry the same MTBF/warranty/what-not as their SCSI drives. I can't remember if they actually claim that it's the same mechanisms just with a different controller on the drive... Well western digital and Seagate both carry 5 year warranties. Seagate I believe does on almost all of there products. WD you have to pick the right drive. I know that someone recently made a big PR push about how you could get 'server reliability' in some of their SATA drives, but maybe now everyone's starting to do it. I suspect the premium you can charge for it offsets the costs, provided that you switch all your production over rather than trying to segregate production lines. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [PERFORM] Arguments Pro/Contra Software Raid
Joshua D. Drake wrote: You want an in-depth comparison of how a server disk drive is internally better than a desktop drive: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf BTW, someone (Western Digital?) is now offering SATA drives that carry the same MTBF/warranty/what-not as their SCSI drives. I can't remember if they actually claim that it's the same mechanisms just with a different controller on the drive... Well western digital and Seagate both carry 5 year warranties. Seagate I believe does on almost all of there products. WD you have to pick the right drive. That's nice, but it seems similar to my Toshiba laptop drive experience --- it breaks, we replace it. I would rather not have to replace it. :-) Let me mention the only drive that has ever failed without warning was a SCSI Deskstar (deathstar) drive, which was a hybrid because it was a SCSI drive, but made for consumer use. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [PERFORM] Arguments Pro/Contra Software Raid
Well western digital and Seagate both carry 5 year warranties. Seagate I believe does on almost all of there products. WD you have to pick the right drive. That's nice, but it seems similar to my Toshiba laptop drive experience --- it breaks, we replace it. I would rather not have to replace it. :-) Laptop drives are known to have short lifespans do to heat. I have IDE drives that have been running for four years without any issues but I have good fans blowing over them. Frankly I think if you are running drivess (in a production environment) for more then 3 years your crazy anyway :) Let me mention the only drive that has ever failed without warning was a SCSI Deskstar (deathstar) drive, which was a hybrid because it was a SCSI drive, but made for consumer use. -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [PERFORM] Arguments Pro/Contra Software Raid
On Thu, May 11, 2006 at 07:20:27PM -0400, Bruce Momjian wrote: Joshua D. Drake wrote: You want an in-depth comparison of how a server disk drive is internally better than a desktop drive: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf BTW, someone (Western Digital?) is now offering SATA drives that carry the same MTBF/warranty/what-not as their SCSI drives. I can't remember if they actually claim that it's the same mechanisms just with a different controller on the drive... Well western digital and Seagate both carry 5 year warranties. Seagate I believe does on almost all of there products. WD you have to pick the right drive. That's nice, but it seems similar to my Toshiba laptop drive experience --- it breaks, we replace it. I would rather not have to replace it. :-) Let me mention the only drive that has ever failed without warning was a SCSI Deskstar (deathstar) drive, which was a hybrid because it was a SCSI drive, but made for consumer use. My damn powerbook drive recently failed with very little warning, other than I did notice that disk activity seemed to be getting a bit slower. IIRC it didn't log any errors or anything. Even if it did, if the OS was catching them I'd hope it would pop up a warning or something. But from what I've heard, some drives now-a-days will silently remap dead sectors without telling the OS anything, which is great until you've used up all of the spare sectors and there's nowhere to remap to. :( Hmm... I should figure out how to have OS X email me daily log updates like FreeBSD does... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [PERFORM] Arguments Pro/Contra Software Raid
Jim C. Nasby wrote: On Thu, May 11, 2006 at 07:20:27PM -0400, Bruce Momjian wrote: Joshua D. Drake wrote: You want an in-depth comparison of how a server disk drive is internally better than a desktop drive: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf BTW, someone (Western Digital?) is now offering SATA drives that carry the same MTBF/warranty/what-not as their SCSI drives. I can't remember if they actually claim that it's the same mechanisms just with a different controller on the drive... Well western digital and Seagate both carry 5 year warranties. Seagate I believe does on almost all of there products. WD you have to pick the right drive. That's nice, but it seems similar to my Toshiba laptop drive experience --- it breaks, we replace it. I would rather not have to replace it. :-) Let me mention the only drive that has ever failed without warning was a SCSI Deskstar (deathstar) drive, which was a hybrid because it was a SCSI drive, but made for consumer use. My damn powerbook drive recently failed with very little warning, other than I did notice that disk activity seemed to be getting a bit slower. IIRC it didn't log any errors or anything. Even if it did, if the OS was catching them I'd hope it would pop up a warning or something. But from what I've heard, some drives now-a-days will silently remap dead sectors without telling the OS anything, which is great until you've used up all of the spare sectors and there's nowhere to remap to. :( Yes, I think most IDE drives do silently remap, and most SCSI drives don't. Not sure how much _most_ is. I know my SCSI controller beeps at me when I try to access a bad block. Now, that gets my attention. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [PERFORM] Arguments Pro/Contra Software Raid
Hmm... I should figure out how to have OS X email me daily log updates like FreeBSD does... Logwatch. -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Postgres gets stuck
Craig A. James [EMAIL PROTECTED] writes: My suspicion is that it's an incompatibility between malloc() libraries. On Linux there's only supposed to be one malloc, ie, glibc's version. On other platforms I'd be worried about threaded vs non-threaded libc (because the backend is not threaded), but not Linux. There may be a more basic threading problem here, though, rooted in the precise fact that the backend isn't threaded. If you're trying to use any libraries that assume they can have multiple threads, I wouldn't be at all surprised to see things go boom. C++ exception handling could be problematic too. Or it could be a garden variety glibc bug. How up-to-date is your platform? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Postgres gets stuck
Tom Lane wrote: My suspicion is that it's an incompatibility between malloc() libraries. On Linux there's only supposed to be one malloc, ie, glibc's version. On other platforms I'd be worried about threaded vs non-threaded libc (because the backend is not threaded), but not Linux. I guess I misinterpreted the Postgress manual, which says (in 31.9, C Language Functions), When allocating memory, use the PostgreSQL functions palloc and pfree instead of the corresponding C library functions malloc and free. I imagined that perhaps palloc/pfree used mutexes for something. But if I understand you, palloc() and pfree() are just wrappers around malloc() and free(), and don't (for example) make their own separate calls to brk(2), sbrk(2), or their kin. If that's the case, then you answered my question - it's all ordinary malloc/free calls in the end, and that's not the source of the problem. There may be a more basic threading problem here, though, rooted in the precise fact that the backend isn't threaded. If you're trying to use any libraries that assume they can have multiple threads, I wouldn't be at all surprised to see things go boom. No threading anywhere. None of the libraries use threads or mutexes. It's just plain old vanilla C/C++ scientific algorithms. C++ exception handling could be problematic too. No C++ exceptions are thrown anywhere in the code, 'tho I suppose one of the I/O libraries could throw an exception, e.g. when reading from a file. But there's no evidence of this after millions of identical operations succeeded. In addition, the stack trace shows it to be stuck in a memory operation, not an I/O operation. Or it could be a garden variety glibc bug. How up-to-date is your platform? I guess this is the next place to look. From the few answers I've gotten, it sounds like this isn't a known Postgres issue, and my stack trace doesn't seem to be familiar to anyone on this forum. Oh well... thanks for your help. Craig ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Postgres gets stuck
Craig A. James [EMAIL PROTECTED] writes: I guess I misinterpreted the Postgress manual, which says (in 31.9, C Language Functions), When allocating memory, use the PostgreSQL functions palloc and pfree instead of the corresponding C library functions malloc and free. I imagined that perhaps palloc/pfree used mutexes for something. But if I understand you, palloc() and pfree() are just wrappers around malloc() and free(), and don't (for example) make their own separate calls to brk(2), sbrk(2), or their kin. Correct. palloc/pfree are all about managing the lifetime of memory allocations, so that (for example) a function can return a palloc'd data structure without worrying about whether that creates a long-term memory leak. But ultimately they just use malloc/free, and there's certainly not any threading or mutex considerations in there. No threading anywhere. None of the libraries use threads or mutexes. It's just plain old vanilla C/C++ scientific algorithms. Darn, my best theory down the drain. Or it could be a garden variety glibc bug. How up-to-date is your platform? I guess this is the next place to look. Let us know how it goes... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
