Re: [PERFORM] Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
Dear All Thanks for your suggestions replies. The below are the sample query which i put for particular one bill_id EXPLAIN ANALYZE SELECT abd.bill_no as bill_no,to_char(abd.bill_date,'dd/mm/') AS date,mp.product_desc as product_desc,std.quantity,std.area,rip.price AS rate FROM acc_bill_items_106 abi JOIN acc_bill_details_106 abd ON abd.bill_id=abi.bill_id JOIN stk_source ss ON ss.source_detail[1]=1 and ss.source_detail[2]=abi.item_id JOIN stock_transaction_detail_106 std ON std.stock_id=ss.stock_id JOIN stock_details_106 sd106 ON sd106.stock_id=std.stock_id JOIN master_product_106 mp ON mp.product_id= sd106.product_id JOIN receipt_item_price_106 rip ON rip.receipt_item_id=abi.item_id WHERE abi.bill_id=12680; QUERY PLAN --- Nested Loop (cost=0.00..7230339.59 rows=54369 width=39) (actual time=158156.895..158157.206 rows=1 loops=1) - Nested Loop (cost=0.00..7149579.10 rows=8192 width=32) (actual time=158156.863..158157.172 rows=1 loops=1) - Nested Loop (cost=0.00..7119922.60 rows=8192 width=27) (actual time=158156.855..158157.164 rows=1 loops=1) - Nested Loop (cost=0.00..7086865.70 rows=8192 width=19) (actual time=158156.835..158157.143 rows=1 loops=1) Join Filter: (abi.item_id = ss.source_detail[2]) - Nested Loop (cost=0.00..604.54 rows=2 width=23) (actual time=2.782..2.786 rows=1 loops=1) - Index Scan using acc_bill_details_106_pkey on acc_bill_details_106 abd (cost=0.00..6.29 rows=1 width=12) (actual time=0.010..0.012 rows=1 loops=1) Index Cond: (bill_id = 12680) - Nested Loop (cost=0.00..598.19 rows=2 width=19) (actual time=2.770..2.772 rows=1 loops=1) Join Filter: (abi.item_id = rip.receipt_item_id) - Seq Scan on receipt_item_price_106 rip (cost=0.00..162.48 rows=4216 width=11) (actual time=0.005..0.562 rows=4218 loops=1) - Materialize (cost=0.00..140.59 rows=2 width=8) (actual time=0.000..0.000 rows=1 loops=4218) - Seq Scan on acc_bill_items_106 abi (cost=0.00..140.58 rows=2 width=8) (actual time=0.412..0.412 rows=1 loops=1) Filter: (bill_id = 12680) - Materialize (cost=0.00..7024562.68 rows=819222 width=33) (actual time=0.035..153869.575 rows=19010943 loops=1) - Append (cost=0.00..7014065.57 rows=819222 width=33) (actual time=0.034..145403.828 rows=19010943 loops=1) - Seq Scan on stk_source ss (cost=0.00..45.10 rows=5 width=36) (actual time=0.001..0.001 rows=0 loops=1) Filter: (source_detail[1] = 1) - Seq Scan on stk_source ss (cost=0.00..6.32 rows=2596 width=33) (actual time=0.033..118.019 rows=66356 loops=1) Filter: (source_detail[1] = 1) - Seq Scan on stk_source ss (cost=0.00..90405.31 rows=10559 width=33) (actual time=0.010..490.712 rows=288779 loops=1) Filter: (source_detail[1] = 1) - Seq Scan on stk_source ss (cost=0.00..6901388.84 rows=806062 width=33) (actual time=13.382..142493.302 rows=18655808 loops=1) Filter: (source_detail[1] = 1) - Index Scan using sd106_stock_id_idx on stock_details_106 sd106 (cost=0.00..4.00 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1) Index Cond: (sd106.stock_id = ss.stock_id) - Index Scan using master_product_pkey on master_product_106 mp (cost=0.00..3.59 rows=1 width=13) (actual time=0.006..0.006 rows=1 loops=1) Index Cond: (mp.product_id = sd106.product_id) - Index Scan using std106_stock_id_idx on stock_transaction_detail_106 std (cost=0.00..9.70 rows=4 width=19) (actual time=0.007..0.009 rows=1 loops=1) Index Cond: (std.stock_id = ss.stock_id) Total runtime: 158240.795 ms http://goog_1591150719*http://explain.depesz.com/s/Tyc *Similarly i have used the queries on various details pages and views that too if i go for one month transactions its taking so much times. I will try to upgrade to latest version and will try to tune more my queries so changing the conf settings wouldn't help for better performance?? Thanks Regards Hashim On Tue, Nov 1, 2011 at 7:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marcus Engene meng...@engene.se writes: After I upgraded from Postgres 8.3/8.4 to 9.0 I had all sorts of problems with queries with many joins. Queries that
Re: [PERFORM] procedure takes much more time than its query statement
Excelent ! You are right Thanks a lot Sabin Craig Ringer ring...@ringerc.id.au wrote in message news:4eb0a920.1010...@ringerc.id.au... On 11/01/2011 10:01 PM, Sabin Coanda wrote: Hi there, I have the function: CREATE OR REPLACE FUNCTION Test( ... ) RETURNS SETOF record AS $BODY$ BEGIN RETURN QUERY SELECT ...; END; $BODY$ LANGUAGE 'plpgsql' STABLE The function call takes about 5 minute to proceed, but using directly its query statement, after replacing the arguments with the same values, it takes just 5 seconds ! I repeat the test several times and the duration is the same. What is wrong ? Is it also slow if, outside PL/PgSQL in a regular psql session, you PREPARE the query, then EXECUTE it? If so, you're being bitten by a generic query plan. The server does a better job when it knows what parameter is used when it's planning the statement. To work around it, you can use the PL/PgSQL 'EXECUTE ... USING ...' statement to force a re-plan of the statement for every time it's run. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Intel 710 pgbench write latencies
Hello list, A OCZ Vertex 2 PRO and Intel 710 SSD, both 100GB, in a software raid 1 setup. I was pretty convinced this was the perfect solution to run PostgreSQL on SSDs without a IO controller with BBU. No worries for strange firmware bugs because of two different drives, good write endurance of the 710. Access to the smart attributes. Complete control over the disks: nothing hidden by a hardware raid IO layer. Then I did a pgbench test: - bigger than RAM test (~30GB database with 24GB ram) - and during that test I removed the Intel 710. - during the test I removed the 710 and 10 minutes later inserted it again and added it to the array. The pgbench transaction latency graph is here: http://imgur.com/JSdQd With only the OCZ, latencies are acceptable but with two drives, there are latencies up to 3 seconds! (and 11 seconds at disk remove time) Is this due to software raid, or is it the Intel 710? To figure that out I repeated the test, but now removing the OCZ, latency graph at: http://imgur.com/DQa59 (The 12 seconds maximum was at disk remove time.) So the Intel 710 kind of sucks latency wise. Is it because it is also heavily reading, and maybe WAL should not be put on it? I did another test, same as before but * with 5GB database completely fitting in RAM (24GB) * put WAL on a ramdisk * started on the mirror * during the test mdadm --fail on the Intel SSD Latency graph is at: http://imgur.com/dY0Rk So still: with Intel 710 participating in writes (beginning of graph), some latencies are over 2 seconds, with only the OCZ, max write latencies are near 300ms. I'm now contemplating not using the 710 at all. Why should I not buy two 6Gbps SSDs without supercap (e.g. Intel 510 and OCZ Vertex 3 Max IOPS) with a IO controller+BBU? Benefits: should be faster for all kinds of reads and writes. Concerns: TRIM becomes impossible (which was already impossible with md raid1, lvm / dm based mirroring could work) but is TRIM important for a PostgreSQL io load, without e.g. routine TRUNCATES? Also the write endurance of these drives is probably a lot less than previous setup. Thoughts, ideas are highly appreciated! -- Yeb PS: I checked for proper alignment of partitions as well as md's data offsett, all was well. Ext4 filesystem mounted with barrier=0 /proc/sys/vm/dirty_background_bytes set to 17850 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
Yeb Havinga yebhavi...@gmail.com wrote: I'm now contemplating not using the 710 at all. Why should I not buy two 6Gbps SSDs without supercap (e.g. Intel 510 and OCZ Vertex 3 Max IOPS) with a IO controller+BBU? Wouldn't the data be subject to loss between the time the IO controller writes to the SSD and the time it makes it from buffers to flash RAM? -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
As I've come up to speed on SQL and PostgreSQL with some medium-complexity queries, I've asked a few questions about what the optimizer will do in various situations. I'm not talking about the seq-scan-vs-index type of optimizing; I mean transforming within the relational calculus (algebra?) to an equivalent but more performant query. The same topics come up: - Flattening. I think that means Merge the intent of the subquery into the various clauses of the parent query. - Inlining. That's Don't run this function/subquery/view as an atomic unit; instead, push it up into the parent query so the optimizer can see it all at once. Maybe that's the same as flattening. - Predicate pushdown. That's This subquery produces a lot of rows, but the parent query has a WHERE clause that will eliminate half of them, so don't produce the unnecessary rows. Am I right so far? Now, the big question, which I haven't seen documented anywhere: Under what circumstances can the optimizer do each of these things? For instance, I have a complex query that calculates the similarity of one user to every other user. The output is two columns, one row per user: select * from similarity(my_user_id); other_user | similarity% ---|- 123 | 99 Being a novice at SQL, I first wrote it in PL/pgSQL, so I could stay in my imperative, iterative head. The query performed decently well when scanning the whole table, but when I only wanted to compare myself to a single user, I said: select * from similarity(my_user_id) as s where s.other_user = 321; And, of course, similarity() produced the whole table anyway, because predicates don't get pushed down into PL/pgSQL functions. So I went and rewrote similarity as a SQL function, but I still didn't want one big hairy SQL query. Ah ha! CTEs let you write modular subqueries, and you also avoid problems with lack of LATERAL. I'll use those. .. But of course predicates don't get pushed into CTEs, either. (Or maybe it was that they would, but only if they were inline with the predicate.. I forget now.) So you can see where I'm going. I know if I break everything into elegant, composable functions, it'll continue to perform poorly. If I write one big hairy, it'll perform great but it will be difficult to maintain, and it will be inelegant and a kitten will die. My tools are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and views (and other tools?) What optimizations do each of those prevent? We're on 9.0 now but will happily upgrade to 9.1 if that matters. Jay Levitt -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
On Wed, Nov 2, 2011 at 8:05 AM, Yeb Havinga yebhavi...@gmail.com wrote: Hello list, A OCZ Vertex 2 PRO and Intel 710 SSD, both 100GB, in a software raid 1 setup. I was pretty convinced this was the perfect solution to run PostgreSQL on SSDs without a IO controller with BBU. No worries for strange firmware bugs because of two different drives, good write endurance of the 710. Access to the smart attributes. Complete control over the disks: nothing hidden by a hardware raid IO layer. Then I did a pgbench test: - bigger than RAM test (~30GB database with 24GB ram) - and during that test I removed the Intel 710. - during the test I removed the 710 and 10 minutes later inserted it again and added it to the array. The pgbench transaction latency graph is here: http://imgur.com/JSdQd With only the OCZ, latencies are acceptable but with two drives, there are latencies up to 3 seconds! (and 11 seconds at disk remove time) Is this due to software raid, or is it the Intel 710? To figure that out I repeated the test, but now removing the OCZ, latency graph at: http://imgur.com/DQa59 (The 12 seconds maximum was at disk remove time.) So the Intel 710 kind of sucks latency wise. Is it because it is also heavily reading, and maybe WAL should not be put on it? I did another test, same as before but * with 5GB database completely fitting in RAM (24GB) * put WAL on a ramdisk * started on the mirror * during the test mdadm --fail on the Intel SSD Latency graph is at: http://imgur.com/dY0Rk So still: with Intel 710 participating in writes (beginning of graph), some latencies are over 2 seconds, with only the OCZ, max write latencies are near 300ms. I'm now contemplating not using the 710 at all. Why should I not buy two 6Gbps SSDs without supercap (e.g. Intel 510 and OCZ Vertex 3 Max IOPS) with a IO controller+BBU? Benefits: should be faster for all kinds of reads and writes. Concerns: TRIM becomes impossible (which was already impossible with md raid1, lvm / dm based mirroring could work) but is TRIM important for a PostgreSQL io load, without e.g. routine TRUNCATES? Also the write endurance of these drives is probably a lot less than previous setup. software RAID (mdadm) is currently blocking TRIM. the only way to to get TRIM in a raid-ish environment is through LVM mirroring/striping or w/brtfs raid (which is not production ready afaik). Given that, if you do use software raid, it's not a good idea to partition the entire drive because the very first thing the raid driver does is write to the entire device. I would keep at least 20-30% of both drives unpartitioned to leave the controller room to wear level and as well as other stuff. I'd try wiping the drives, reparititoing, and repeating your test. I would also compare times through mdadm and directly to the device. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
Mohamed Hashim nmdhas...@gmail.com writes: The below are the sample query which i put for particular one bill_id EXPLAIN ANALYZE SELECT abd.bill_no as bill_no,to_char(abd.bill_date,'dd/mm/') AS date,mp.product_desc as product_desc,std.quantity,std.area,rip.price AS rate FROM acc_bill_items_106 abi JOIN acc_bill_details_106 abd ON abd.bill_id=abi.bill_id JOIN stk_source ss ON ss.source_detail[1]=1 and ss.source_detail[2]=abi.item_id JOIN stock_transaction_detail_106 std ON std.stock_id=ss.stock_id JOIN stock_details_106 sd106 ON sd106.stock_id=std.stock_id JOIN master_product_106 mp ON mp.product_id= sd106.product_id JOIN receipt_item_price_106 rip ON rip.receipt_item_id=abi.item_id WHERE abi.bill_id=12680; All the time seems to be going into the seqscan on stk_source and its child tables. It looks like it would help if ss.source_detail[1]=1 and ss.source_detail[2]=abi.item_id were indexable (particularly the latter). Which probably means you need to rethink your data representation. Putting things that you need to index on into an array is not a very good design. I suppose you can do it if you're absolutely set on it (functional indexes on (source_detail[1]) and (source_detail[2])) but it appears to suck from a notational point of view too. Six months from now, when you look at this code, are you going to remember what's the difference between source_detail[1] and source_detail[2]? Not without consulting your notes, I bet. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
Jay Levitt jay.lev...@gmail.com writes: So you can see where I'm going. I know if I break everything into elegant, composable functions, it'll continue to perform poorly. If I write one big hairy, it'll perform great but it will be difficult to maintain, and it will be inelegant and a kitten will die. My tools are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and views (and other tools?) What optimizations do each of those prevent? plpgsql functions are black boxes to the optimizer. If you can express your functions as single SQL commands, using SQL-language functions is usually a better bet than plpgsql. CTEs are also treated as optimization fences; this is not so much an optimizer limitation as to keep the semantics sane when the CTE contains a writable query. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
On 2011-11-02 15:06, Kevin Grittner wrote: Yeb Havingayebhavi...@gmail.com wrote: I'm now contemplating not using the 710 at all. Why should I not buy two 6Gbps SSDs without supercap (e.g. Intel 510 and OCZ Vertex 3 Max IOPS) with a IO controller+BBU? Wouldn't the data be subject to loss between the time the IO controller writes to the SSD and the time it makes it from buffers to flash RAM? Good question. My guess would be no, if the raid controller does 'write-throughs' on the attached disks, and the SSD's don't lie about when they've written to RAM. I'll put this on my to test list for the new setup. -- Yeb -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
On Wed, Nov 2, 2011 at 16:04, Yeb Havinga yebhavi...@gmail.com wrote: On 2011-11-02 15:06, Kevin Grittner wrote: Yeb Havingayebhavi...@gmail.com wrote: I'm now contemplating not using the 710 at all. Why should I not buy two 6Gbps SSDs without supercap (e.g. Intel 510 and OCZ Vertex 3 Max IOPS) with a IO controller+BBU? Wouldn't the data be subject to loss between the time the IO controller writes to the SSD and the time it makes it from buffers to flash RAM? Good question. My guess would be no, if the raid controller does 'write-throughs' on the attached disks, and the SSD's don't lie about when they've written to RAM. Doesn't most SSDs without supercaps lie about the writes, though? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
On Wed, Nov 2, 2011 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jay Levitt jay.lev...@gmail.com writes: So you can see where I'm going. I know if I break everything into elegant, composable functions, it'll continue to perform poorly. If I write one big hairy, it'll perform great but it will be difficult to maintain, and it will be inelegant and a kitten will die. My tools are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and views (and other tools?) What optimizations do each of those prevent? plpgsql functions are black boxes to the optimizer. If you can express your functions as single SQL commands, using SQL-language functions is usually a better bet than plpgsql. CTEs are also treated as optimization fences; this is not so much an optimizer limitation as to keep the semantics sane when the CTE contains a writable query. I wonder if we need to rethink, though. We've gotten a number of reports of problems that were caused by single-use CTEs not being equivalent - in terms of performance - to a non-CTE formulation of the same idea. It seems necessary for CTEs to behave this way when the subquery modifies data, and there are certainly situations where it could be desirable otherwise, but I'm starting to think that we shouldn't do it that way by default. Perhaps we could let people say something like WITH x AS FENCE (...) when they want the fencing behavior, and otherwise assume they don't (but give it to them anyway if there's a data-modifying operation in there). Whenever I give a talk on the query optimizer, I'm constantly telling people to take logic out of functions and inline it, avoid CTEs, and generally merge everything into one big query. But as the OP says, that is decidedly less than ideal from a code-beauty-and-maintenance point of view: people WANT to be able to use syntactic sugar and still get good performance. Allowing for the insertion of optimization fences is good and important but it needs to be user-controllable behavior. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
On 2011-11-02 15:26, Merlin Moncure wrote: On Wed, Nov 2, 2011 at 8:05 AM, Yeb Havingayebhavi...@gmail.com wrote: Hello list, A OCZ Vertex 2 PRO and Intel 710 SSD, both 100GB, in a software raid 1 setup. I was pretty convinced this was the perfect solution to run PostgreSQL on SSDs without a IO controller with BBU. No worries for strange firmware bugs because of two different drives, good write endurance of the 710. Access to the smart attributes. Complete control over the disks: nothing hidden by a hardware raid IO layer. Then I did a pgbench test: - bigger than RAM test (~30GB database with 24GB ram) - and during that test I removed the Intel 710. - during the test I removed the 710 and 10 minutes later inserted it again and added it to the array. The pgbench transaction latency graph is here: http://imgur.com/JSdQd With only the OCZ, latencies are acceptable but with two drives, there are latencies up to 3 seconds! (and 11 seconds at disk remove time) Is this due to software raid, or is it the Intel 710? To figure that out I repeated the test, but now removing the OCZ, latency graph at: http://imgur.com/DQa59 (The 12 seconds maximum was at disk remove time.) So the Intel 710 kind of sucks latency wise. Is it because it is also heavily reading, and maybe WAL should not be put on it? I did another test, same as before but * with 5GB database completely fitting in RAM (24GB) * put WAL on a ramdisk * started on the mirror * during the test mdadm --fail on the Intel SSD Latency graph is at: http://imgur.com/dY0Rk So still: with Intel 710 participating in writes (beginning of graph), some latencies are over 2 seconds, with only the OCZ, max write latencies are near 300ms. I'm now contemplating not using the 710 at all. Why should I not buy two 6Gbps SSDs without supercap (e.g. Intel 510 and OCZ Vertex 3 Max IOPS) with a IO controller+BBU? Benefits: should be faster for all kinds of reads and writes. Concerns: TRIM becomes impossible (which was already impossible with md raid1, lvm / dm based mirroring could work) but is TRIM important for a PostgreSQL io load, without e.g. routine TRUNCATES? Also the write endurance of these drives is probably a lot less than previous setup. software RAID (mdadm) is currently blocking TRIM. the only way to to get TRIM in a raid-ish environment is through LVM mirroring/striping or w/brtfs raid (which is not production ready afaik). Given that, if you do use software raid, it's not a good idea to partition the entire drive because the very first thing the raid driver does is write to the entire device. If that is bad because of a decreased lifetime, I don't think these number of writes are significant - in a few hours of pgbenching I the GBs written are more than 10 times the GB sizes of the drives. Or do you suggest this because then the disk firmware can operate assuming a smaller idema capacity, thereby proloning the drive life? (i.e. the Intel 710 200GB has 200GB idema capacity but 320GB raw flash). I would keep at least 20-30% of both drives unpartitioned to leave the controller room to wear level and as well as other stuff. I'd try wiping the drives, reparititoing, and repeating your test. I would also compare times through mdadm and directly to the device. Good idea. -- Yeb -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
So the Intel 710 kind of sucks latency wise. Is it because it is also heavily reading, and maybe WAL should not be put on it? A couple quick thoughts: 1. There are a lot of moving parts in the system besides the SSDs. It will take some detailed analysis to determine the cause for the outlying high latency transactions. The cause may not be as simple as one SSD processes I/O operations less quickly than another. For example the system may be subject to some sort of starvation issue in PG or the OS that is affected by quite small differences in underlying storage performance. 2. What are your expectations for maximum transaction latency ? In my experience it is not possible to guarantee sub-second (or even sub-multi-second) latencies overall in a system built with general purpose OS and database software. (put another way : a few outlying 1 second and even several-second transactions would be pretty much what I'd expect to see on a database under sustained saturation load as experienced under a pgbench test). -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
On Wednesday 02 Nov 2011 16:13:09 Robert Haas wrote: On Wed, Nov 2, 2011 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jay Levitt jay.lev...@gmail.com writes: So you can see where I'm going. I know if I break everything into elegant, composable functions, it'll continue to perform poorly. If I write one big hairy, it'll perform great but it will be difficult to maintain, and it will be inelegant and a kitten will die. My tools are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and views (and other tools?) What optimizations do each of those prevent? plpgsql functions are black boxes to the optimizer. If you can express your functions as single SQL commands, using SQL-language functions is usually a better bet than plpgsql. CTEs are also treated as optimization fences; this is not so much an optimizer limitation as to keep the semantics sane when the CTE contains a writable query. I wonder if we need to rethink, though. We've gotten a number of reports of problems that were caused by single-use CTEs not being equivalent - in terms of performance - to a non-CTE formulation of the same idea. It seems necessary for CTEs to behave this way when the subquery modifies data, and there are certainly situations where it could be desirable otherwise, but I'm starting to think that we shouldn't do it that way by default. Perhaps we could let people say something like WITH x AS FENCE (...) when they want the fencing behavior, and otherwise assume they don't (but give it to them anyway if there's a data-modifying operation in there). +1. I avoid writing CTEs in many cases where they would be very useful just for that reasons. I don't even think some future inlining necessarily has to be restricted to one-use cases only... +1 for making fencing behaviour as well. Currently there is no real explicit method to specify this which is necessarily future proof (WITH, OFFSET 0)... Andres -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
On Wed, Nov 2, 2011 at 12:13 PM, Robert Haas robertmh...@gmail.com wrote: I wonder if we need to rethink, though. We've gotten a number of reports of problems that were caused by single-use CTEs not being equivalent - in terms of performance - to a non-CTE formulation of the same idea. It seems necessary for CTEs to behave this way when the subquery modifies data, and there are certainly situations where it could be desirable otherwise, but I'm starting to think that we shouldn't do it that way by default. Perhaps we could let people say something like WITH x AS FENCE (...) when they want the fencing behavior, and otherwise assume they don't (but give it to them anyway if there's a data-modifying operation in there). Well, in my case, I got performance thanks to CTEs *being* optimization fences, letting me fiddle with query execution. And I mean, going from half-hour queries to 1-minute queries. It is certainly desirable to maintain the possibility to use fences when needed. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
On Wed, Nov 2, 2011 at 10:16 AM, Yeb Havinga yebhavi...@gmail.com wrote: On 2011-11-02 15:26, Merlin Moncure wrote: On Wed, Nov 2, 2011 at 8:05 AM, Yeb Havingayebhavi...@gmail.com wrote: Hello list, A OCZ Vertex 2 PRO and Intel 710 SSD, both 100GB, in a software raid 1 setup. I was pretty convinced this was the perfect solution to run PostgreSQL on SSDs without a IO controller with BBU. No worries for strange firmware bugs because of two different drives, good write endurance of the 710. Access to the smart attributes. Complete control over the disks: nothing hidden by a hardware raid IO layer. Then I did a pgbench test: - bigger than RAM test (~30GB database with 24GB ram) - and during that test I removed the Intel 710. - during the test I removed the 710 and 10 minutes later inserted it again and added it to the array. The pgbench transaction latency graph is here: http://imgur.com/JSdQd With only the OCZ, latencies are acceptable but with two drives, there are latencies up to 3 seconds! (and 11 seconds at disk remove time) Is this due to software raid, or is it the Intel 710? To figure that out I repeated the test, but now removing the OCZ, latency graph at: http://imgur.com/DQa59 (The 12 seconds maximum was at disk remove time.) So the Intel 710 kind of sucks latency wise. Is it because it is also heavily reading, and maybe WAL should not be put on it? I did another test, same as before but * with 5GB database completely fitting in RAM (24GB) * put WAL on a ramdisk * started on the mirror * during the test mdadm --fail on the Intel SSD Latency graph is at: http://imgur.com/dY0Rk So still: with Intel 710 participating in writes (beginning of graph), some latencies are over 2 seconds, with only the OCZ, max write latencies are near 300ms. I'm now contemplating not using the 710 at all. Why should I not buy two 6Gbps SSDs without supercap (e.g. Intel 510 and OCZ Vertex 3 Max IOPS) with a IO controller+BBU? Benefits: should be faster for all kinds of reads and writes. Concerns: TRIM becomes impossible (which was already impossible with md raid1, lvm / dm based mirroring could work) but is TRIM important for a PostgreSQL io load, without e.g. routine TRUNCATES? Also the write endurance of these drives is probably a lot less than previous setup. software RAID (mdadm) is currently blocking TRIM. the only way to to get TRIM in a raid-ish environment is through LVM mirroring/striping or w/brtfs raid (which is not production ready afaik). Given that, if you do use software raid, it's not a good idea to partition the entire drive because the very first thing the raid driver does is write to the entire device. If that is bad because of a decreased lifetime, I don't think these number of writes are significant - in a few hours of pgbenching I the GBs written are more than 10 times the GB sizes of the drives. Or do you suggest this because then the disk firmware can operate assuming a smaller idema capacity, thereby proloning the drive life? (i.e. the Intel 710 200GB has 200GB idema capacity but 320GB raw flash). It's bad because the controller thinks all the data is 'live' -- that is, important. When all the data on the drive is live the fancy tricks the controller pulls to do intelligent wear leveling and to get fast write times becomes much more difficult which in turn leads to more write amplification and early burnout. Supposedly, the 710 has extra space anyways which is probably there specifically to ameliorate the raid issue as well as extend lifespan but I'm still curious how this works out. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
On 11/2/11 10:22 AM, Claudio Freire wrote: On Wed, Nov 2, 2011 at 12:13 PM, Robert Haasrobertmh...@gmail.com wrote: I wonder if we need to rethink, though. We've gotten a number of reports of problems that were caused by single-use CTEs not being equivalent - in terms of performance - to a non-CTE formulation of the same idea. It seems necessary for CTEs to behave this way when the subquery modifies data, and there are certainly situations where it could be desirable otherwise, but I'm starting to think that we shouldn't do it that way by default. Perhaps we could let people say something like WITH x AS FENCE (...) when they want the fencing behavior, and otherwise assume they don't (but give it to them anyway if there's a data-modifying operation in there). Well, in my case, I got performance thanks to CTEs *being* optimization fences, letting me fiddle with query execution. And I mean, going from half-hour queries to 1-minute queries. Same here. It was a case where I asked this group and was told that putting an offset 0 fence in was probably the only way to solve it (once again reminding us that Postgres actually does have hints ... they're just called other things). It is certainly desirable to maintain the possibility to use fences when needed. Indeed. Optimizer problems are usually fixed in due course, but these fences are invaluable when you have a dead web site that has to be fixed right now. Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] two table join just not fast enough.
Hi all,
I've got a query that I need to squeeze as much speed out of as I can.
When I execute this query, the average time it takes is about 190 ms. I
increased my work_mem from 1 MB to 50MB and it decreased the timing down
to an average of 170 ms, but that's still not fast enough. This query is
executed extremely frequently, so much of it should be easily cached.
Some settings
work_mem = 50MB
shared_buffers = 5GB
I've made sure that the two tables are recently analyzed (with
default_stats to 100, 400, and 1500 even), and table bloat is low (150
meg table has 7 megs bloat).
Here's the query:
SELECT yankee.my_id
FROM yankee
INNER JOIN hotel_zulu
ON hotel_zulu.my_id = yankee.zulu_id
AND hotel_zulu.type IN ('string1', 'string2', 'string3', 'string4')
WHERE yankee.your_id=402513;
And here is a query plan.
Hash Join (cost=17516.470..26386.660 rows=27624 width=4) (actual
time=309.194..395.135 rows=12384 loops=1)
Hash Cond: (yankee.alpha = hotel_zulu.quebec)
- Bitmap Heap Scan on yankee (cost=1066.470..8605.770 rows=27624
width=20) (actual time=5.178..34.693 rows=26963 loops=1)
Recheck Cond: (mike = 402513)
- Bitmap Index Scan on hotel_alpha (cost=0.000..1059.570 rows=27624
width=0) (actual time=4.770..4.770 rows=26967 loops=1)
Index Cond: (mike = 402513)
- Hash (cost=14465.080..14465.080 rows=114154 width=16) (actual
time=303.717..303.717 rows=129908 loops=1)
Buckets: 4096 Batches: 8 Memory Usage: 784kB
- Bitmap Heap Scan on hotel_zulu (cost=2461.770..14465.080
rows=114154 width=16) (actual time=25.642..185.253 rows=129908 loops=1)
Recheck Cond: ((two)::golf = ANY ('xray'::golf[]))
- Bitmap Index Scan on kilo (cost=0.000..2433.230 rows=114154
width=0) (actual time=23.887..23.887 rows=130292 loops=1)
Index Cond: ((two)::golf = ANY ('xray'::golf[]))
One thing I notice is the rows estimated is 27624 and the actual rows
returned is 12384. Quite a bit different.
Table 2 (known here as hotel_zulu) is being joined on zulu_id to the
first table, and then a where clause on the column 'type'. There are
single column indexes on each of these columns, and any multi column
index I put on these are just ignored by the planner.
Any thoughts on ways to tweak this?
- Brian F
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Poor performance on a simple join
Hi All;
The below contab2 table conmtains ~400,000 rows. This query should not
take this long. We've tweaked work_mem up to 50MB, ensured that the
appropriate indexes are in place, etc...
Thoughts?
Thanks in advance
Explain analyze:
SELECT contab2.contacts_tab
FROM contab2
INNER JOIN sctab
ON sctab.id = contab2.to_service_id
AND sctab.type IN ('FService', 'FqService', 'LService',
'TService')
WHERE contab2.from_contact_id=402513;
QUERY PLAN
---
Hash Join (cost=16904.28..25004.54 rows=26852 width=4) (actual
time=302.621..371.599 rows=12384 loops=1)
Hash Cond: (contab2.to_service_id = sctab.id)
- Bitmap Heap Scan on contab2 (cost=1036.49..8566.14 rows=26852
width=20) (actual time=5.191..32.701 rows=26963 loops=1)
Recheck Cond: (from_contact_id = 402513)
- Bitmap Index Scan on index_contab2_on_from_user_id
(cost=0.00..1029.78 rows=26852 width=0) (actual time=4.779..4.779
rows=26963 loops=1)
Index Cond: (from_contact_id = 402513)
- Hash (cost=14445.19..14445.19 rows=113808 width=16) (actual
time=297.332..297.332 rows=129945 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 6092kB
- Bitmap Heap Scan on sctab (cost=2447.07..14445.19
rows=113808 width=16) (actual time=29.480..187.166 rows=129945 loops=1)
Recheck Cond: ((type)::text = ANY
('{FService,FqService,LService,TService}'::text[]))
- Bitmap Index Scan on index_sctab_on_type
(cost=0.00..2418.62 rows=113808 width=0) (actual time=27.713..27.713
rows=130376 loops=1)
Index Cond: ((type)::text = ANY
('{FService,FqService,LService,TService}'::text[]))
Total runtime: 382.514 ms
(13 rows)
--
-
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
-
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
-Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Wednesday, November 02, 2011 11:13 AM To: Tom Lane Cc: Jay Levitt; pgsql-performance@postgresql.org Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc? ... ... Perhaps we could let people say something like WITH x AS FENCE (...) when they want the fencing behavior, and otherwise assume they don't (but give it to them anyway if there's a data-modifying operation in there). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company Hints here we come :) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
On 2011-11-02 16:16, Yeb Havinga wrote: On 2011-11-02 15:26, Merlin Moncure wrote: I would keep at least 20-30% of both drives unpartitioned to leave the controller room to wear level and as well as other stuff. I'd try wiping the drives, reparititoing, and repeating your test. I would also compare times through mdadm and directly to the device. Good idea. Reinstalled system - 50% drives unpartitioned. /dev/sdb3 19G 5.0G 13G 29% /ocz /dev/sda3 19G 4.8G 13G 28% /intel /dev/sdb3 on /ocz type ext4 (rw,noatime,nobarrier,discard) /dev/sda3 on /intel type ext4 (rw,noatime,nobarrier,discard) Again WAL was put in a ramdisk. pgbench -i -s 300 t # fits in ram pgbench -c 20 -M prepared -T 300 -l t Intel latency graph at http://imgur.com/Hh3xI Ocz latency graph at http://imgur.com/T09LG -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Poor performance on a simple join
On Wed, Nov 2, 2011 at 2:21 PM, CS DBA cs_...@consistentstate.com wrote:
Hi All;
The below contab2 table conmtains ~400,000 rows. This query should not take
this long. We've tweaked work_mem up to 50MB, ensured that the appropriate
indexes are in place, etc...
Thoughts?
Thanks in advance
How long should it take? 300 milliseconds is fairly fast for mushing
129k rows up against 26k rows and getting 12k rows back. That's 40
rows / millisecond, which isn't too bad really.
What pg version are you running? What evidence do you have that this
is slow? i.e. other machines you've run it on where it's faster? What
hardware (CPU, RAM, IO subsystem, OS) Are you running on?
Explain analyze:
SELECT contab2.contacts_tab
FROM contab2
INNER JOIN sctab
ON sctab.id = contab2.to_service_id
AND sctab.type IN ('FService', 'FqService', 'LService', 'TService')
WHERE contab2.from_contact_id=402513;
QUERY
PLAN
---
Hash Join (cost=16904.28..25004.54 rows=26852 width=4) (actual
time=302.621..371.599 rows=12384 loops=1)
Hash Cond: (contab2.to_service_id = sctab.id)
- Bitmap Heap Scan on contab2 (cost=1036.49..8566.14 rows=26852
width=20) (actual time=5.191..32.701 rows=26963 loops=1)
Recheck Cond: (from_contact_id = 402513)
- Bitmap Index Scan on index_contab2_on_from_user_id
(cost=0.00..1029.78 rows=26852 width=0) (actual time=4.779..4.779
rows=26963 loops=1)
Index Cond: (from_contact_id = 402513)
- Hash (cost=14445.19..14445.19 rows=113808 width=16) (actual
time=297.332..297.332 rows=129945 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 6092kB
- Bitmap Heap Scan on sctab (cost=2447.07..14445.19 rows=113808
width=16) (actual time=29.480..187.166 rows=129945 loops=1)
Recheck Cond: ((type)::text = ANY
('{FService,FqService,LService,TService}'::text[]))
- Bitmap Index Scan on index_sctab_on_type
(cost=0.00..2418.62 rows=113808 width=0) (actual time=27.713..27.713
rows=130376 loops=1)
Index Cond: ((type)::text = ANY
('{FService,FqService,LService,TService}'::text[]))
Total runtime: 382.514 ms
(13 rows)
--
-
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
-
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
To understand recursion, one must first understand recursion.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
On Wed, Nov 2, 2011 at 3:45 PM, Yeb Havinga yebhavi...@gmail.com wrote: On 2011-11-02 16:16, Yeb Havinga wrote: On 2011-11-02 15:26, Merlin Moncure wrote: I would keep at least 20-30% of both drives unpartitioned to leave the controller room to wear level and as well as other stuff. I'd try wiping the drives, reparititoing, and repeating your test. I would also compare times through mdadm and directly to the device. Good idea. Reinstalled system - 50% drives unpartitioned. /dev/sdb3 19G 5.0G 13G 29% /ocz /dev/sda3 19G 4.8G 13G 28% /intel /dev/sdb3 on /ocz type ext4 (rw,noatime,nobarrier,discard) /dev/sda3 on /intel type ext4 (rw,noatime,nobarrier,discard) Again WAL was put in a ramdisk. pgbench -i -s 300 t # fits in ram pgbench -c 20 -M prepared -T 300 -l t Intel latency graph at http://imgur.com/Hh3xI Ocz latency graph at http://imgur.com/T09LG curious: what were the pgbench results in terms of tps? merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Poor performance on a simple join
On 11/02/2011 02:45 PM, Scott Marlowe wrote:
On Wed, Nov 2, 2011 at 2:21 PM, CS DBAcs_...@consistentstate.com wrote:
Hi All;
The below contab2 table conmtains ~400,000 rows. This query should not take
this long. We've tweaked work_mem up to 50MB, ensured that the appropriate
indexes are in place, etc...
Thoughts?
Thanks in advance
How long should it take? 300 milliseconds is fairly fast for mushing
129k rows up against 26k rows and getting 12k rows back. That's 40
rows / millisecond, which isn't too bad really.
What pg version are you running? What evidence do you have that this
is slow? i.e. other machines you've run it on where it's faster? What
hardware (CPU, RAM, IO subsystem, OS) Are you running on?
Explain analyze:
SELECT contab2.contacts_tab
FROM contab2
INNER JOIN sctab
ON sctab.id = contab2.to_service_id
AND sctab.type IN ('FService', 'FqService', 'LService', 'TService')
WHERE contab2.from_contact_id=402513;
QUERY
PLAN
---
Hash Join (cost=16904.28..25004.54 rows=26852 width=4) (actual
time=302.621..371.599 rows=12384 loops=1)
Hash Cond: (contab2.to_service_id = sctab.id)
-Bitmap Heap Scan on contab2 (cost=1036.49..8566.14 rows=26852
width=20) (actual time=5.191..32.701 rows=26963 loops=1)
Recheck Cond: (from_contact_id = 402513)
-Bitmap Index Scan on index_contab2_on_from_user_id
(cost=0.00..1029.78 rows=26852 width=0) (actual time=4.779..4.779
rows=26963 loops=1)
Index Cond: (from_contact_id = 402513)
-Hash (cost=14445.19..14445.19 rows=113808 width=16) (actual
time=297.332..297.332 rows=129945 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 6092kB
-Bitmap Heap Scan on sctab (cost=2447.07..14445.19 rows=113808
width=16) (actual time=29.480..187.166 rows=129945 loops=1)
Recheck Cond: ((type)::text = ANY
('{FService,FqService,LService,TService}'::text[]))
-Bitmap Index Scan on index_sctab_on_type
(cost=0.00..2418.62 rows=113808 width=0) (actual time=27.713..27.713
rows=130376 loops=1)
Index Cond: ((type)::text = ANY
('{FService,FqService,LService,TService}'::text[]))
Total runtime: 382.514 ms
(13 rows)
--
-
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
-
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Agreed. but it's not fast enough for the client. I think we're going to
look at creating an aggregate table or maybe partitioning
--
-
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
-
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] two table join just not fast enough.
Brian Fehrle bri...@consistentstate.com writes: I've got a query that I need to squeeze as much speed out of as I can. Hmm ... are you really sure this is being run with work_mem = 50MB? The hash join is getting batched, which means the executor thinks it's working under a memory constraint significantly less than the size of the filtered inner relation, which should be no more than a couple megabytes according to this. I'm not sure how much that will save, since the hashjoin seems to be reasonably speedy anyway, but there's not much other fat to trim here. One minor suggestion is to think whether you really need string comparisons here or could convert that to use of an enum type. String compares ain't cheap, especially not in non-C locales. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] two table join just not fast enough.
Thanks Tom, And looks like I pasted an older explain plan, which is almost exactly the same as the one with 50MB work_mem, except for the hash join 'buckets' part which used more memory and only one 'bucket' so to speak. When running with the 50MB work_mem over 1MB work_mem, the query went from an average of 190 ms to 169 ms, so it did help some but it wasn't a game changer (I even found for this specific query, 6MB of work_mem was the most that would actually help me). I have other plans to try to get this thing running faster, I'll be exploring them tomorrow, as well as looking at using an enum type. - Brian F On 11/02/2011 05:53 PM, Tom Lane wrote: Brian Fehrlebri...@consistentstate.com writes: I've got a query that I need to squeeze as much speed out of as I can. Hmm ... are you really sure this is being run with work_mem = 50MB? The hash join is getting batched, which means the executor thinks it's working under a memory constraint significantly less than the size of the filtered inner relation, which should be no more than a couple megabytes according to this. I'm not sure how much that will save, since the hashjoin seems to be reasonably speedy anyway, but there's not much other fat to trim here. One minor suggestion is to think whether you really need string comparisons here or could convert that to use of an enum type. String compares ain't cheap, especially not in non-C locales. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] two table join just not fast enough.
On 03/11/11 09:12, Brian Fehrle wrote:
And here is a query plan.
Hash Join (cost=17516.470..26386.660 rows=27624 width=4) (actual
time=309.194..395.135 rows=12384 loops=1)
Hash Cond: (yankee.alpha = hotel_zulu.quebec)
- Bitmap Heap Scan on yankee (cost=1066.470..8605.770 rows=27624
width=20) (actual time=5.178..34.693 rows=26963 loops=1)
Recheck Cond: (mike = 402513)
- Bitmap Index Scan on hotel_alpha (cost=0.000..1059.570
rows=27624 width=0) (actual time=4.770..4.770 rows=26967 loops=1)
Index Cond: (mike = 402513)
- Hash (cost=14465.080..14465.080 rows=114154 width=16) (actual
time=303.717..303.717 rows=129908 loops=1)
Buckets: 4096 Batches: 8 Memory Usage: 784kB
- Bitmap Heap Scan on hotel_zulu (cost=2461.770..14465.080
rows=114154 width=16) (actual time=25.642..185.253 rows=129908 loops=1)
Recheck Cond: ((two)::golf = ANY ('xray'::golf[]))
- Bitmap Index Scan on kilo (cost=0.000..2433.230
rows=114154 width=0) (actual time=23.887..23.887 rows=130292 loops=1)
Index Cond: ((two)::golf = ANY ('xray'::golf[]))
Might be worth posting table definitions, as this plan does not
immediately look like it came from the query you posted. Also unless I
am misreading the output looks like you have some custom datatypes (e.g
'golf'), so more info there could be useful too.
When we have that, there may be something to be learned from examining
the pg_stats data for the join and predicate columns used in these queries.
regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Poor performance on a simple join
On Wed, Nov 2, 2011 at 3:53 PM, CS DBA cs_...@consistentstate.com wrote:
On 11/02/2011 02:45 PM, Scott Marlowe wrote:
On Wed, Nov 2, 2011 at 2:21 PM, CS DBAcs_...@consistentstate.com wrote:
Hi All;
The below contab2 table conmtains ~400,000 rows. This query should not
take
this long. We've tweaked work_mem up to 50MB, ensured that the
appropriate
indexes are in place, etc...
Thoughts?
Thanks in advance
How long should it take? 300 milliseconds is fairly fast for mushing
129k rows up against 26k rows and getting 12k rows back. That's 40
rows / millisecond, which isn't too bad really.
What pg version are you running? What evidence do you have that this
is slow? i.e. other machines you've run it on where it's faster? What
hardware (CPU, RAM, IO subsystem, OS) Are you running on?
Explain analyze:
SELECT contab2.contacts_tab
FROM contab2
INNER JOIN sctab
ON sctab.id = contab2.to_service_id
AND sctab.type IN ('FService', 'FqService', 'LService',
'TService')
WHERE contab2.from_contact_id=402513;
QUERY
PLAN
---
Hash Join (cost=16904.28..25004.54 rows=26852 width=4) (actual
time=302.621..371.599 rows=12384 loops=1)
Hash Cond: (contab2.to_service_id = sctab.id)
- Bitmap Heap Scan on contab2 (cost=1036.49..8566.14 rows=26852
width=20) (actual time=5.191..32.701 rows=26963 loops=1)
Recheck Cond: (from_contact_id = 402513)
- Bitmap Index Scan on index_contab2_on_from_user_id
(cost=0.00..1029.78 rows=26852 width=0) (actual time=4.779..4.779
rows=26963 loops=1)
Index Cond: (from_contact_id = 402513)
- Hash (cost=14445.19..14445.19 rows=113808 width=16) (actual
time=297.332..297.332 rows=129945 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 6092kB
- Bitmap Heap Scan on sctab (cost=2447.07..14445.19
rows=113808
width=16) (actual time=29.480..187.166 rows=129945 loops=1)
Recheck Cond: ((type)::text = ANY
('{FService,FqService,LService,TService}'::text[]))
- Bitmap Index Scan on index_sctab_on_type
(cost=0.00..2418.62 rows=113808 width=0) (actual time=27.713..27.713
rows=130376 loops=1)
Index Cond: ((type)::text = ANY
('{FService,FqService,LService,TService}'::text[]))
Total runtime: 382.514 ms
(13 rows)
--
-
Kevin Kempter - Constent State
A PostgreSQL Professional Services Company
www.consistentstate.com
-
--
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Agreed. but it's not fast enough for the client. I think we're going to
look at creating an aggregate table or maybe partitioning
Take a look here:
http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Intel 710 pgbench write latencies
Your results are consistent with the benchmarks I've seen. Intel SSD have much worse write performance compared to SSD that uses Sandforce controllers, which Vertex 2 Pro does. According to this benchmark, at high queue depth the random write performance of Sandforce is more than 5 times that of Intel 710: http://www.anandtech.com/show/4902/intel-ssd-710-200gb-review/4 Why don't you just use two Vertex 2 Pro in sw RAID1? It should give you good write performance. Why should I not buy two 6Gbps SSDs without supercap (e.g. Intel 510 and OCZ Vertex 3 Max IOPS) with a IO controller+BBU? Because it that case you'll lose data whenever you have a power loss. Without capacitors data written to the SSD is not durable. From: Yeb Havinga yebhavi...@gmail.com To: pgsql-performance@postgresql.org pgsql-performance@postgresql.org Sent: Wednesday, November 2, 2011 9:05 AM Subject: [PERFORM] Intel 710 pgbench write latencies Hello list, A OCZ Vertex 2 PRO and Intel 710 SSD, both 100GB, in a software raid 1 setup. I was pretty convinced this was the perfect solution to run PostgreSQL on SSDs without a IO controller with BBU. No worries for strange firmware bugs because of two different drives, good write endurance of the 710. Access to the smart attributes. Complete control over the disks: nothing hidden by a hardware raid IO layer. Then I did a pgbench test: - bigger than RAM test (~30GB database with 24GB ram) - and during that test I removed the Intel 710. - during the test I removed the 710 and 10 minutes later inserted it again and added it to the array. The pgbench transaction latency graph is here: http://imgur.com/JSdQd With only the OCZ, latencies are acceptable but with two drives, there are latencies up to 3 seconds! (and 11 seconds at disk remove time) Is this due to software raid, or is it the Intel 710? To figure that out I repeated the test, but now removing the OCZ, latency graph at: http://imgur.com/DQa59 (The 12 seconds maximum was at disk remove time.) So the Intel 710 kind of sucks latency wise. Is it because it is also heavily reading, and maybe WAL should not be put on it? I did another test, same as before but * with 5GB database completely fitting in RAM (24GB) * put WAL on a ramdisk * started on the mirror * during the test mdadm --fail on the Intel SSD Latency graph is at: http://imgur.com/dY0Rk So still: with Intel 710 participating in writes (beginning of graph), some latencies are over 2 seconds, with only the OCZ, max write latencies are near 300ms. I'm now contemplating not using the 710 at all. Why should I not buy two 6Gbps SSDs without supercap (e.g. Intel 510 and OCZ Vertex 3 Max IOPS) with a IO controller+BBU? Benefits: should be faster for all kinds of reads and writes. Concerns: TRIM becomes impossible (which was already impossible with md raid1, lvm / dm based mirroring could work) but is TRIM important for a PostgreSQL io load, without e.g. routine TRUNCATES? Also the write endurance of these drives is probably a lot less than previous setup. Thoughts, ideas are highly appreciated! -- Yeb PS: I checked for proper alignment of partitions as well as md's data offsett, all was well. Ext4 filesystem mounted with barrier=0 /proc/sys/vm/dirty_background_bytes set to 17850 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
