Re: [PERFORM] IS NOT NULL and LEFT JOIN
On Tue, Oct 21, 2014 at 2:58 AM, Tom Lane t...@sss.pgh.pa.us wrote: Laurent Martelli laurent.marte...@enercoop.org writes: Do we agree that both queries are identical ? No, they *aren't* identical. Go consult any SQL reference. Left join conditions don't work the way you seem to be thinking: after the join, the RHS column might be null, rather than equal to the LHS column. For what it's worth I'd say they are identical, at least, if you discount deferring foreign key constraints or also executing the query from within a volatile function which was called by a query which just updated the user_info table to break referential integrity. The presence of the foreign key on contract_contract.user_info which references user_user_info.id means that any non-null contract_contract.user_info record must reference a valid user_user_info record, therefore the join is not required to prove that a non nulled user_info contract records match a user info record, therefore the join to check it exists is pretty much pointless in just about all cases that you're likely to care about. Although, saying that I'm still a bit confused about the question. Are you asking if there's some way to get PostgreSQL to run the 1st query faster? Or are you asking if both queries are equivalent? Regards David Rowley
Re: [PERFORM] Query with large number of joins
2014-10-20 21:59 GMT-02:00 Tom Lane t...@sss.pgh.pa.us: Marco Di Cesare marco.dices...@pointclickcare.com writes: We are using a BI tool that generates a query with an unusually large number of joins. My understanding is that with this many joins Postgres query planner can't possibly use an exhaustive search so it drops into a heuristics algorithm. Unfortunately, the query runs quite slow (~35 seconds) and seems to ignore using primary keys and indexes where available. Query plan here (sorry had to anonymize): http://explain.depesz.com/s/Uml It's difficult to make any detailed comments when you've shown us only an allegedly-bad query plan, and not either the query itself or the table definitions. However, it appears to me that the query plan is aggregating over a rather large number of join rows, and there are very few constraints that would allow eliminating rows. So I'm not at all sure there is a significantly better plan available. Are you claiming this query was instantaneous on SQL Server? The only thing that jumps out at me as possibly improvable is that with a further increase in work_mem, you could probably get it to change the last aggregation step from Sort+GroupAggregate into HashAggregate, which'd likely run faster ... assuming you can spare some more memory. 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 Hi, As Tom said, WORK_MEM seems a nice place to start. Here are other considerations you might take in account: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server There's also the opportunity to tune the query itself (if it's not automatically generated by your BI tool). You can always speed up a query response by using filtered sub-selects instead of calling the the entire tables themselves on the joins. BR Felipe
[PERFORM] Query Performance Problem
Hi all,I'm experimenting with table partitioning though inheritance. I'm testing a query as follows:explain (analyze, buffers)selectresponse.idfrom claim.responsewhere response.account_id = 4766and response.expire_timestamp is nulland response.create_timestamp = DATE '2014-08-01'order by create_timestamp;The response table looks like this:"account_id";"integer""file_type_id";"integer""receiver_inbound_detail_id";"integer""processing_status_id";"integer""processing";"boolean""expire_timestamp";"timestamp without time zone""last_mod_timestamp";"timestamp without time zone""create_timestamp";"timestamp without time zone""response_trace_nbr";"character varying""posted_timestamp";"timestamp without time zone""need_to_post";"boolean""response_message";"text""worked";"boolean""response_status_id";"integer""response_type_id";"integer""outbound_claim_detail_id";"bigint""id";"bigint"Here are some rowcounts:SELECT count(*) from claim_response.response_201408; count-4585746(1 row)Time: 7271.054 msSELECT count(*) from claim_response.response_201409; count-3523370(1 row)Time: 4341.116 msSELECT count(*) from claim_response.response_201410;count--- 154(1 row)Time: 0.258 msThe entire table has225,665,512 rows. I read that a partitioning rule of thumb is that benefits of partitioning occur starting around 100 million rows.SELECT count(*) from claim.response; count---225665512(1 row)Time: 685064.637 msThe partitioning is on the create_timestamp field.The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a VM machine - 8 GB RAM with 2 CPUs:Architecture: x86_64CPU op-mode(s):32-bit, 64-bitByte Order: Little EndianCPU(s):2On-line CPU(s) list: 0,1Thread(s) per core: 1Core(s) per socket: 2CPU socket(s): 1NUMA node(s): 1Vendor ID: GenuineIntelCPU family: 6Model: 44Stepping: 2CPU MHz:2660.000BogoMIPS: 5320.00L1d cache: 32KL1i cache: 32KL2 cache: 256KL3 cache: 12288KNUMA node0 CPU(s): 0,12 users, load average: 0.00, 0.12, 0.37Please see the following for the explain analysis :http://explain.depesz.com/s/I3SLI'm trying to understand why I'm getting the yellow, orange, and red on the inclusive, and the yellow on the exclusive. (referring to theexplain.depesz.com/s/I3SLpage.)I'm relatively new to PostgreSQL, but I've been an Oracle DBA for some time. I suspect the I/O may be dragging but I don't know how to dig that information out from here. Please point out anything else you can decipher from this.Thanks,John
Re: [PERFORM] Query Performance Problem
2014-10-21 10:57 GMT-02:00 j...@jpm-cola.com: Hi all, I'm experimenting with table partitioning though inheritance. I'm testing a query as follows: explain (analyze, buffers) select response.id from claim.response where response.account_id = 4766 and response.expire_timestamp is null and response.create_timestamp = DATE '2014-08-01' order by create_timestamp; The response table looks like this: account_id;integer file_type_id;integer receiver_inbound_detail_id;integer processing_status_id;integer processing;boolean expire_timestamp;timestamp without time zone last_mod_timestamp;timestamp without time zone create_timestamp;timestamp without time zone response_trace_nbr;character varying posted_timestamp;timestamp without time zone need_to_post;boolean response_message;text worked;boolean response_status_id;integer response_type_id;integer outbound_claim_detail_id;bigint id;bigint Here are some rowcounts: SELECT count(*) from claim_response.response_201408; count - 4585746 (1 row) Time: 7271.054 ms SELECT count(*) from claim_response.response_201409; count - 3523370 (1 row) Time: 4341.116 ms SELECT count(*) from claim_response.response_201410; count --- 154 (1 row) Time: 0.258 ms The entire table has 225,665,512 rows. I read that a partitioning rule of thumb is that benefits of partitioning occur starting around 100 million rows. SELECT count(*) from claim.response; count --- 225665512 (1 row) Time: 685064.637 ms The partitioning is on the create_timestamp field. The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a VM machine - 8 GB RAM with 2 CPUs: Architecture: x86_64 CPU op-mode(s):32-bit, 64-bit Byte Order:Little Endian CPU(s):2 On-line CPU(s) list: 0,1 Thread(s) per core:1 Core(s) per socket:2 CPU socket(s): 1 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family:6 Model: 44 Stepping: 2 CPU MHz: 2660.000 BogoMIPS: 5320.00 L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 12288K NUMA node0 CPU(s): 0,1 2 users, load average: 0.00, 0.12, 0.37 Please see the following for the explain analysis : http://explain.depesz.com/s/I3SL I'm trying to understand why I'm getting the yellow, orange, and red on the inclusive, and the yellow on the exclusive. (referring to the explain.depesz.com/s/I3SL page.) I'm relatively new to PostgreSQL, but I've been an Oracle DBA for some time. I suspect the I/O may be dragging but I don't know how to dig that information out from here. Please point out anything else you can decipher from this. Thanks, John Hi John, Dont know about the colors, but the Stats tab looks fine. You've got yourself 5 Index Scans, which are a very fast way to dig data. I noticed you've also cast your filter field (create_timestamp = '2014-08-01'::date). As far as I know, Postgresql doesn't need this kind of explicit conversion. You would be fine with just (create_timestamp = '2014-08-01'). Regards, Felipe
Re: [PERFORM] Query with large number of joins
On Mon, Oct 20, 2014 at 3:32 PM, Marco Di Cesare marco.dices...@pointclickcare.com wrote: We are using Postgres for the first time after being SQLServer users for a long time so forgive for being noobs. We are using a BI tool that generates a query with an unusually large number of joins. My understanding is that with this many joins Postgres query planner can't possibly use an exhaustive search so it drops into a heuristics algorithm. Unfortunately, the query runs quite slow (~35 seconds) and seems to ignore using primary keys and indexes where available. Query plan here (sorry had to anonymize): http://explain.depesz.com/s/Uml Line 30 is one of the pain points where a full table scan is running on 4.2 million rows even though there are indexes on oscar_bravo.foxtrot_four and oscar_charlie.foxtrot_four We've tried to play around with the join_collapse_limit value by upping it from the default of 8 to 10 or 12 but it doesn't seem to help much. Cranking the value up to an unreasonable value of 20 does shave some seconds off the query time but not substantially (explain plan with the value set to 20: http://explain.depesz.com/s/sW6). You always have the option of disabling geqo completely. However, in this case, can you fetch out the relevant fields for oscar_bravo that are participating in the join? I'd like to see the field name/type in the source table and the destination table. Also. I'd like to see the index definition and the snippit of the query that presents the join condition. You can encourage the server to favor index scans vs seq scans by lowering 'random_page_cost'. The nuclear option is to disable sequential scans completely (which is generally a bad idea but can be useful to try and fetch out queries that are inadvertently forced into a seqscan for some reason). 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] Query with large number of joins
I did not mean to imply this works any better on SQL Server. We never tried. I just meant to say this is the first time we are using Postgres so we don't have much experience with it. We tried with work_mem set to 1GB (even as high as 3GB) but it didn't change the GroupAggregate and Sort or query run time. Sorry, I had to sanitize the query and a few of the relevant tables so hopefully I got it all right. SELECT foxtrot_india.juliet_alpha, foxtrot_india.foxtrot_yankee, foxtrot_india.hotel_sierra, foxtrot_india.juliet_alpha, foxtrot_india.bravo_romeo, oscar_bravo.golf_foxtrot, seven_kilo.november_lima, foxtrot_india.echo_six, uniform_six.seven_six, oscar_charlie.foxtrot_charlie, COUNT(DISTINCT foxtrot_india.bravo_romeo) FROM public.seven_kilo seven_kilo INNER JOIN public.papa_sierra papa_sierra ON (seven_kilo.golf_bravo = papa_sierra.golf_bravo) LEFT JOIN public.golf_two golf_two ON (seven_kilo.lima = golf_two.lima) LEFT JOIN public.bravo_xray bravo_xray ON (seven_kilo.lima = bravo_xray.lima) LEFT JOIN public.foo1 foo1 ON ((seven_kilo.bar1 = foo1.bar1) AND (seven_kilo.golf_bravo = foo1.golf_bravo)) INNER JOIN public.oscar_charlie oscar_charlie ON (seven_kilo.lima = oscar_charlie.lima) INNER JOIN public.oscar_bravo oscar_bravo ON (oscar_charlie.foxtrot_four = oscar_bravo.foxtrot_four) INNER JOIN public.foxtrot_india foxtrot_india ON (oscar_bravo.sierra = foxtrot_india.sierra) INNER JOIN public.hotel_romeo hotel_romeo ON (oscar_charlie.foxtrot_charlie = hotel_romeo.foxtrot_charlie) INNER JOIN public.uniform_six uniform_six ON (hotel_romeo.hotel_lima = uniform_six.hotel_lima) LEFT JOIN public.lookup foo2 ON (foxtrot_india.bar2 = foo2.lookup_id) LEFT JOIN public.uniform_two uniform_two ON (foxtrot_india.sierra = uniform_two.sierra) INNER JOIN public.lookup four_xray ON (uniform_two.quebec = four_xray.quebec) LEFT JOIN public.papa_four papa_four ON (foxtrot_india.sierra = papa_four.sierra) INNER JOIN public.lookup romeo_bravo ON (papa_four.quebec = romeo_bravo.quebec) LEFT JOIN public.juliet_two juliet_two ON (foxtrot_india.sierra = juliet_two.sierra) INNER JOIN public.lookup four_delta ON (juliet_two.quebec = four_delta.quebec) LEFT JOIN public.foo3 foo3 ON (foxtrot_india.bar3 = foo3.bar3) INNER JOIN public.xray xray ON (seven_kilo.lima = xray.lima) INNER JOIN public.romeo_echo romeo_echo ON (xray.echo_sierra = romeo_echo.echo_sierra) WHERE (((xray.echo_sierra = 'november_foxtrot') AND (romeo_echo.hotel_oscar = 'zulu') AND (oscar_charlie.five = 6) AND (oscar_charlie.whiskey = 'four_romeo') AND (oscar_charlie.charlie_romeo = 2014))) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 Table public.oscar_bravo Column | Type | Modifiers | Storage | Stats target | Description -+---+---+--+--+ - foxtrot_four| character varying(60) | not null | extended | | sierra | character varying(40) | not null | extended | | foo | boolean | not null | plain| | bar | numeric(3,2) | | main | | baz | integer | not null | plain| | Indexes: foxtrot_four_sierra_PK_IX PRIMARY KEY, btree (foxtrot_four, sierra) foxtrot_four_idx btree (foxtrot_four) sierra_idx btree (sierra) CLUSTER Foreign-key constraints: sierra_FK FOREIGN KEY (sierra) REFERENCES foxtrot_india(sierra) foxtrot_four_FK FOREIGN KEY (foxtrot_four) REFERENCES oscar_charlie(foxtrot_four ) Has OIDs: no Table public.oscar_charlie Column | Type | Modifiers | Storage | Stats target | Description ---+---+---+--+--+- foxtrot_four | character varying(60) | not null | extended | | foxtrot_charlie | character varying(10) | not null | extended | | lima | character varying(30) | not null | extended | | whiskey | character varying(3) | not null | extended | | charlie_romeo |
Re: [PERFORM] Query with large number of joins
On 10/21/2014 12:09 PM, Marco Di Cesare wrote: I did not mean to imply this works any better on SQL Server. We never tried. I just meant to say this is the first time we are using Postgres so we don't have much experience with it. We tried with work_mem set to 1GB (even as high as 3GB) but it didn't change the GroupAggregate and Sort or query run time. Sorry, I had to sanitize the query and a few of the relevant tables so hopefully I got it all right. SELECT foxtrot_india.juliet_alpha, foxtrot_india.foxtrot_yankee, foxtrot_india.hotel_sierra, foxtrot_india.juliet_alpha, foxtrot_india.bravo_romeo, oscar_bravo.golf_foxtrot, seven_kilo.november_lima, foxtrot_india.echo_six, uniform_six.seven_six, oscar_charlie.foxtrot_charlie, COUNT(DISTINCT foxtrot_india.bravo_romeo) FROM public.seven_kilo seven_kilo INNER JOIN public.papa_sierra papa_sierra ON (seven_kilo.golf_bravo = papa_sierra.golf_bravo) LEFT JOIN public.golf_two golf_two ON (seven_kilo.lima = golf_two.lima) LEFT JOIN public.bravo_xray bravo_xray ON (seven_kilo.lima = bravo_xray.lima) LEFT JOIN public.foo1 foo1 ON ((seven_kilo.bar1 = foo1.bar1) AND (seven_kilo.golf_bravo = foo1.golf_bravo)) INNER JOIN public.oscar_charlie oscar_charlie ON (seven_kilo.lima = oscar_charlie.lima) INNER JOIN public.oscar_bravo oscar_bravo ON (oscar_charlie.foxtrot_four = oscar_bravo.foxtrot_four) INNER JOIN public.foxtrot_india foxtrot_india ON (oscar_bravo.sierra = foxtrot_india.sierra) INNER JOIN public.hotel_romeo hotel_romeo ON (oscar_charlie.foxtrot_charlie = hotel_romeo.foxtrot_charlie) INNER JOIN public.uniform_six uniform_six ON (hotel_romeo.hotel_lima = uniform_six.hotel_lima) LEFT JOIN public.lookup foo2 ON (foxtrot_india.bar2 = foo2.lookup_id) LEFT JOIN public.uniform_two uniform_two ON (foxtrot_india.sierra = uniform_two.sierra) INNER JOIN public.lookup four_xray ON (uniform_two.quebec = four_xray.quebec) LEFT JOIN public.papa_four papa_four ON (foxtrot_india.sierra = papa_four.sierra) INNER JOIN public.lookup romeo_bravo ON (papa_four.quebec = romeo_bravo.quebec) LEFT JOIN public.juliet_two juliet_two ON (foxtrot_india.sierra = juliet_two.sierra) INNER JOIN public.lookup four_delta ON (juliet_two.quebec = four_delta.quebec) LEFT JOIN public.foo3 foo3 ON (foxtrot_india.bar3 = foo3.bar3) INNER JOIN public.xray xray ON (seven_kilo.lima = xray.lima) INNER JOIN public.romeo_echo romeo_echo ON (xray.echo_sierra = romeo_echo.echo_sierra) WHERE (((xray.echo_sierra = 'november_foxtrot') AND (romeo_echo.hotel_oscar = 'zulu') AND (oscar_charlie.five = 6) AND (oscar_charlie.whiskey = 'four_romeo') AND (oscar_charlie.charlie_romeo = 2014))) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 Please don't top-post on the PostgreSQL lists. See http://idallen.com/topposting.html Have you tried a) either turning off geqo or setting geqo_threshold fairly high b) setting join_collapse_limit fairly high (assuming all the above join targets are tables and not views, setting it to something like 25 should do the trick. You also haven't told us what settings you have for things like effective_cache_size, which can dramatically affect query plans. cheers andrew -- 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] Query with large number of joins
Andrew Dunstan and...@dunslane.net writes: Have you tried a) either turning off geqo or setting geqo_threshold fairly high b) setting join_collapse_limit fairly high (assuming all the above join targets are tables and not views, setting it to something like 25 should do the trick. You'd have to do both, I think, to get an exhaustive plan search. In any case, this query is going to result in full table scans of most of the tables, because there just aren't very many WHERE constraints; so expecting it to run instantaneously is a pipe dream. I'm not sure that there's a significantly better plan to be had. 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] Query with large number of joins
Marco Di Cesare marco.dices...@pointclickcare.com writes: COUNT(DISTINCT foxtrot_india.bravo_romeo) Ah. That explains why the planner doesn't want to use a hash aggregation step --- DISTINCT aggregates aren't supported with those. 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
[PERFORM] extremly bad select performance on huge table
Hi newsgroup, I have a very huge table (70 mio rows ) with a key (text length about 30 characters each key). A select on this indexed column myprimkey (index on column mycolumn) took more than 30 mins. Here is the explain (analyze,buffers) select mycolumn from myhugetable Index Only Scan using myprimkey on myhugetable (cost=0.00..822.82 rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 loops=1) Heap Fetches: 356861 Total runtime: 2503009.611 ms Even repeating the query does not show a performance improvement. I assume that the index itself is too large for my db cache. What can I do to gain performance? Which parameters can I adapt? Having a huge Linux machine with 72 GB RAM. Note: This select is just for testing. My final statement will be a join on this table via the mycolumn column. Thanks for your help Björn -- 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] extremly bad select performance on huge table
Sorry forget to copy the buffer information: Heap Fetches: 356861 Buffers: shared hit=71799472 read=613813 Hi newsgroup, I have a very huge table (70 mio rows ) with a key (text length about 30 characters each key). A select on this indexed column myprimkey (index on column mycolumn) took more than 30 mins. Here is the explain (analyze,buffers) select mycolumn from myhugetable Index Only Scan using myprimkey on myhugetable (cost=0.00..822.82 rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 loops=1) Heap Fetches: 356861 Total runtime: 2503009.611 ms Even repeating the query does not show a performance improvement. I assume that the index itself is too large for my db cache. What can I do to gain performance? Which parameters can I adapt? Having a huge Linux machine with 72 GB RAM. Note: This select is just for testing. My final statement will be a join on this table via the mycolumn column. Thanks for your help Björn -- 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] extremly bad select performance on huge table
=?ISO-8859-15?Q?Bj=F6rn_Wittich?= bjoern_witt...@gmx.de writes: Here is the explain (analyze,buffers) select mycolumn from myhugetable Index Only Scan using myprimkey on myhugetable (cost=0.00..822.82 rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 loops=1) Heap Fetches: 356861 Buffers: shared hit=71799472 read=613813 Total runtime: 2503009.611 ms So that works out to about 4 msec per page fetched considering only I/O costs, which is about as good as you're likely to get if the data is sitting on spinning rust. You could potentially make it faster with a VACUUM (to mark all pages all-visible and eliminate the heap fetches costs), or a REINDEX (so that the index scan becomes more nearly sequential instead of random access). However, unless the data is nearly static those will just be temporary fixes: the time will degrade again as you update the table. Note: This select is just for testing. My final statement will be a join on this table via the mycolumn column. In that case it's probably a waste of time to worry about the performance of this query as such. In the first place, a join is not likely to use the index at all unless it's fetching a relatively small number of rows, and in the second place it seems unlikely that the join query can use an IndexOnlyScan on this index --- I imagine that the purpose of the join will require fetching additional columns. 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] extremly bad select performance on huge table
Hi Tom and Igor, thanks for your help. With the reindex the select query running time was reduced from 5200 sec to 130 sec. Impressive! Even a join on this table is now fast. Unfortunately, there is now another problem: The table in my example has 500 columns which I want to retrieve with my join command. Example which is fast select value from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn Example which is slow select value,c1,c2,c3,...,c10 from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn Which is the number of columns to fetch so bad ? Which action is done in the db system when querying this via pgadmin? I think that there is no real retrieval included, why is the number of additional columns so bad for the join performance? =?ISO-8859-15?Q?Bj=F6rn_Wittich?= bjoern_witt...@gmx.de writes: Here is the explain (analyze,buffers) select mycolumn from myhugetable Index Only Scan using myprimkey on myhugetable (cost=0.00..822.82 rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 loops=1) Heap Fetches: 356861 Buffers: shared hit=71799472 read=613813 Total runtime: 2503009.611 ms So that works out to about 4 msec per page fetched considering only I/O costs, which is about as good as you're likely to get if the data is sitting on spinning rust. You could potentially make it faster with a VACUUM (to mark all pages all-visible and eliminate the heap fetches costs), or a REINDEX (so that the index scan becomes more nearly sequential instead of random access). However, unless the data is nearly static those will just be temporary fixes: the time will degrade again as you update the table. Note: This select is just for testing. My final statement will be a join on this table via the mycolumn column. In that case it's probably a waste of time to worry about the performance of this query as such. In the first place, a join is not likely to use the index at all unless it's fetching a relatively small number of rows, and in the second place it seems unlikely that the join query can use an IndexOnlyScan on this index --- I imagine that the purpose of the join will require fetching additional columns. 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] extremly bad select performance on huge table
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Björn Wittich Sent: Tuesday, October 21, 2014 3:32 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] extremly bad select performance on huge table Hi Tom and Igor, thanks for your help. With the reindex the select query running time was reduced from 5200 sec to 130 sec. Impressive! Even a join on this table is now fast. Unfortunately, there is now another problem: The table in my example has 500 columns which I want to retrieve with my join command. Example which is fast select value from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn Example which is slow select value,c1,c2,c3,...,c10 from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn Which is the number of columns to fetch so bad ? Which action is done in the db system when querying this via pgadmin? I think that there is no real retrieval included, why is the number of additional columns so bad for the join performance? =?ISO-8859-15?Q?Bj=F6rn_Wittich?= bjoern_witt...@gmx.de writes: Here is the explain (analyze,buffers) select mycolumn from myhugetable Index Only Scan using myprimkey on myhugetable (cost=0.00..822.82 rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 loops=1) Heap Fetches: 356861 Buffers: shared hit=71799472 read=613813 Total runtime: 2503009.611 ms So that works out to about 4 msec per page fetched considering only I/O costs, which is about as good as you're likely to get if the data is sitting on spinning rust. You could potentially make it faster with a VACUUM (to mark all pages all-visible and eliminate the heap fetches costs), or a REINDEX (so that the index scan becomes more nearly sequential instead of random access). However, unless the data is nearly static those will just be temporary fixes: the time will degrade again as you update the table. Note: This select is just for testing. My final statement will be a join on this table via the mycolumn column. In that case it's probably a waste of time to worry about the performance of this query as such. In the first place, a join is not likely to use the index at all unless it's fetching a relatively small number of rows, and in the second place it seems unlikely that the join query can use an IndexOnlyScan on this index --- I imagine that the purpose of the join will require fetching additional columns. regards, tom lane Björn, I think, the timing difference you see between 2 queries is caused by delivering to the front-end (PgAdmin) and displaying all additional columns that you include in the second query (much bigger amount of data to pass from the db to the client). Pretty sure, if you do explain analyze on both queries, you'll see the same timing, because it'll reflect only db time without what's spent on delivering data to the client. Regards, Igor Neyman -- 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] Query with large number of joins
On 10/21/2014 12:31 PM, Andrew Dunstan wrote: Please don't top-post on the PostgreSQL lists. See http://idallen.com/topposting.html Oops, sorry. Have you tried a) either turning off geqo or setting geqo_threshold fairly high b) setting join_collapse_limit fairly high (assuming all the above join targets are tables and not views, setting it to something like 25 should do the trick. I did try various combinations of these settings but none yielded any significant query run time improvements. You also haven't told us what settings you have for things like effective_cache_size, which can dramatically affect query plans. effective_cache_size = 4096MB I tried bumping this up as well but again no significant query run time improvements. -- 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] Query with large number of joins
Andrew Dunstan and...@dunslane.net writes: Have you tried a) either turning off geqo or setting geqo_threshold fairly high b) setting join_collapse_limit fairly high (assuming all the above join targets are tables and not views, setting it to something like 25 should do the trick. Tom Lane t...@sss.pgh.pa.us writes: You'd have to do both, I think, to get an exhaustive plan search. In any case, this query is going to result in full table scans of most of the tables, because there just aren't very many WHERE constraints; so expecting it to run instantaneously is a pipe dream. I'm not sure that there's a significantly better plan to be had. regards, tom lane I get that same feeling. Just wanted to be sure there was nothing obvious in terms of settings we might have missed. The BI tool we use wants to load as much raw data as needed and then apply filters (where clauses) on top of that. The numerous joins support those filters and a good number of those joins are one-to-many tables causing a Cartesian product. -- 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] Query with large number of joins
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Marco Di Cesare Sent: Tuesday, October 21, 2014 4:03 PM To: Andrew Dunstan; Merlin Moncure Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Query with large number of joins On 10/21/2014 12:31 PM, Andrew Dunstan wrote: Please don't top-post on the PostgreSQL lists. See http://idallen.com/topposting.html Oops, sorry. Have you tried a) either turning off geqo or setting geqo_threshold fairly high b) setting join_collapse_limit fairly high (assuming all the above join targets are tables and not views, setting it to something like 25 should do the trick. I did try various combinations of these settings but none yielded any significant query run time improvements. You also haven't told us what settings you have for things like effective_cache_size, which can dramatically affect query plans. effective_cache_size = 4096MB I tried bumping this up as well but again no significant query run time improvements. Marco, Didn't you mention, that you have something like 48GB RAM? In this case (if that's dedicated db server), you should try and set effective_cache_size around 40GB (not 4GB). Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine
I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel 3.16.3. I receive numerous Error: out of memory messages in the log, which are aborting client requests, even though there appears to be 23GB available in the OS cache. There is no swap on the box. Postgres is behind pgbouncer to protect from the 200 real clients, which limits connections to 32, although there are rarely more than 20 active connections, even though postgres max_connections is set very high for historic reasons. There is also a 4GB java process running on the box. relevant postgresql.conf: max_connections = 1000 # (change requires restart) shared_buffers = 7GB# min 128kB work_mem = 40MB # min 64kB maintenance_work_mem = 1GB # min 1MB effective_cache_size = 20GB sysctl.conf: vm.swappiness = 0 vm.overcommit_memory = 2 kernel.shmmax=34359738368 kernel.shmall=8388608 log example: ERROR: out of memory DETAIL: Failed on request of size 67108864. STATEMENT: SELECT package_texts.* FROM package_texts WHERE package_texts.id = $1 LIMIT 1 example pg_top, showing 23GB available in cache: last pid: 6607; load avg: 3.59, 2.32, 2.61; up 16+09:17:29 20:49:51 18 processes: 1 running, 17 sleeping CPU states: 22.5% user, 0.0% nice, 4.9% system, 63.2% idle, 9.4% iowait Memory: 29G used, 186M free, 7648K buffers, 23G cached DB activity: 2479 tps, 1 rollbs/s, 217 buffer r/s, 99 hit%, 11994 row r/s, 3820 row w/s DB I/O: 0 reads/s, 0 KB/s, 0 writes/s, 0 KB/s DB disk: 149.8 GB total, 46.7 GB free (68% used) Swap: example top showing the only other significant 4GB process on the box: top - 21:05:09 up 16 days, 9:32, 2 users, load average: 2.73, 2.91, 2.88 Tasks: 147 total, 3 running, 244 sleeping, 0 stopped, 0 zombie %Cpu(s): 22.1 us, 4.1 sy, 0.0 ni, 62.9 id, 9.8 wa, 0.0 hi, 0.7 si, 0.3 st KiB Mem: 30827220 total, 30642584 used, 184636 free, 7292 buffers KiB Swap:0 total,0 used,0 free. 23449636 cached Mem PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ COMMAND 7407 postgres 20 0 7604928 10172 7932 S 29.6 0.0 2:51.27 postgres 10469 postgres 20 0 7617716 176032 160328 R 11.6 0.6 0:01.48 postgres 10211 postgres 20 0 7630352 237736 208704 S 10.6 0.8 0:03.64 postgres 18202 elastic+ 20 0 8726984 4.223g 4248 S 9.6 14.4 883:06.79 java 9711 postgres 20 0 7619500 354188 335856 S 7.0 1.1 0:08.03 postgres 3638 postgres 20 0 7634552 1.162g 1.127g S 6.6 4.0 0:50.42 postgres
Re: [PERFORM] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine
Montana Low montana...@gmail.com writes: I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel 3.16.3. I receive numerous Error: out of memory messages in the log, which are aborting client requests, even though there appears to be 23GB available in the OS cache. Perhaps the postmaster is being started with a ulimit setting that restricts process size? 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] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine
Dne 22 Říjen 2014, 0:25, Montana Low napsal(a): I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel 3.16.3. I receive numerous Error: out of memory messages in the log, which are aborting client requests, even though there appears to be 23GB available in the OS cache. There is no swap on the box. Postgres is behind pgbouncer to protect from the 200 real clients, which limits connections to 32, although there are rarely more than 20 active connections, even though postgres max_connections is set very high for historic reasons. There is also a 4GB java process running on the box. relevant postgresql.conf: max_connections = 1000 # (change requires restart) shared_buffers = 7GB# min 128kB work_mem = 40MB # min 64kB maintenance_work_mem = 1GB # min 1MB effective_cache_size = 20GB sysctl.conf: vm.swappiness = 0 vm.overcommit_memory = 2 This means you have 'no overcommit', so the amount of memory is limited by overcommit_ratio + swap. The default value for overcommit_ratio is 50% RAM, and as you have no swap that effectively means only 50% of the RAM is available to the system. If you want to verify this, check /proc/meminfo - see the lines CommitLimit (the current limit) and Commited_AS (committed address space). Once the committed_as reaches the limit, it's game over. There are different ways to fix this, or at least improve that: (1) increasing the overcommit_ratio (clearly, 50% is way too low - something 90% might be more appropriate on 30GB RAM without swap) (2) adding swap (say a small ephemeral drive, with swappiness=10 or something like that) Tomas -- 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] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine
I didn't realize that about overcommit_ratio. It was at 50, I've changed it to 95. I'll see if that clears up the problem moving forward. # cat /proc/meminfo MemTotal: 30827220 kB MemFree: 153524 kB MemAvailable: 17941864 kB Buffers:6188 kB Cached: 24560208 kB SwapCached:0 kB Active: 20971256 kB Inactive:8538660 kB Active(anon): 12460680 kB Inactive(anon):36612 kB Active(file):8510576 kB Inactive(file): 8502048 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 0 kB SwapFree: 0 kB Dirty: 50088 kB Writeback: 160 kB AnonPages: 4943740 kB Mapped: 7571496 kB Shmem: 7553176 kB Slab: 886428 kB SReclaimable: 858936 kB SUnreclaim:27492 kB KernelStack:4208 kB PageTables: 188352 kB NFS_Unstable: 0 kB Bounce:0 kB WritebackTmp: 0 kB CommitLimit:15413608 kB Committed_AS: 14690544 kB VmallocTotal: 34359738367 kB VmallocUsed: 59012 kB VmallocChunk: 34359642367 kB HugePages_Total: 0 HugePages_Free:0 HugePages_Rsvd:0 HugePages_Surp:0 Hugepagesize: 2048 kB DirectMap4k:31465472 kB DirectMap2M: 0 kB # sysctl -a: vm.admin_reserve_kbytes = 8192 vm.block_dump = 0 vm.dirty_background_bytes = 0 vm.dirty_background_ratio = 10 vm.dirty_bytes = 0 vm.dirty_expire_centisecs = 3000 vm.dirty_ratio = 20 vm.dirty_writeback_centisecs = 500 vm.drop_caches = 0 vm.extfrag_threshold = 500 vm.hugepages_treat_as_movable = 0 vm.hugetlb_shm_group = 0 vm.laptop_mode = 0 vm.legacy_va_layout = 0 vm.lowmem_reserve_ratio = 256 256 32 vm.max_map_count = 65530 vm.min_free_kbytes = 22207 vm.min_slab_ratio = 5 vm.min_unmapped_ratio = 1 vm.mmap_min_addr = 4096 vm.nr_hugepages = 0 vm.nr_hugepages_mempolicy = 0 vm.nr_overcommit_hugepages = 0 vm.nr_pdflush_threads = 0 vm.numa_zonelist_order = default vm.oom_dump_tasks = 1 vm.oom_kill_allocating_task = 0 vm.overcommit_kbytes = 0 vm.overcommit_memory = 2 vm.overcommit_ratio = 50 vm.page-cluster = 3 vm.panic_on_oom = 0 vm.percpu_pagelist_fraction = 0 vm.scan_unevictable_pages = 0 vm.stat_interval = 1 vm.swappiness = 0 vm.user_reserve_kbytes = 131072 vm.vfs_cache_pressure = 100 vm.zone_reclaim_mode = 0 On Tue, Oct 21, 2014 at 3:46 PM, Tomas Vondra t...@fuzzy.cz wrote: Dne 22 Říjen 2014, 0:25, Montana Low napsal(a): I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel 3.16.3. I receive numerous Error: out of memory messages in the log, which are aborting client requests, even though there appears to be 23GB available in the OS cache. There is no swap on the box. Postgres is behind pgbouncer to protect from the 200 real clients, which limits connections to 32, although there are rarely more than 20 active connections, even though postgres max_connections is set very high for historic reasons. There is also a 4GB java process running on the box. relevant postgresql.conf: max_connections = 1000 # (change requires restart) shared_buffers = 7GB# min 128kB work_mem = 40MB # min 64kB maintenance_work_mem = 1GB # min 1MB effective_cache_size = 20GB sysctl.conf: vm.swappiness = 0 vm.overcommit_memory = 2 This means you have 'no overcommit', so the amount of memory is limited by overcommit_ratio + swap. The default value for overcommit_ratio is 50% RAM, and as you have no swap that effectively means only 50% of the RAM is available to the system. If you want to verify this, check /proc/meminfo - see the lines CommitLimit (the current limit) and Commited_AS (committed address space). Once the committed_as reaches the limit, it's game over. There are different ways to fix this, or at least improve that: (1) increasing the overcommit_ratio (clearly, 50% is way too low - something 90% might be more appropriate on 30GB RAM without swap) (2) adding swap (say a small ephemeral drive, with swappiness=10 or something like that) Tomas
Re: [PERFORM] extremly bad select performance on huge table
Hi Igor, that was also my assumption, but unfortunately this isn't true. I am using the explain analyze. Example which is fast explain analyze select value from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn 130 - 140 sec Example which is fast explain analyze select value,c1 from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn does not complete after several hours although the c1 coulmn should only be relevant for retrieval. Comparing the explain comparison of both statements gave me a hint: adding the c1 column changes the query planner to make a sequential scan on myhugetable as well as on smallertable. This is much slower. When I set enable_seqscan=false the queryplanner shows the same query plan for both statements but the statement including the c1 column does not complete after several hours. How can this be explained? I do not want the db server to prepare the whole query result at once, my intention is that the asynchronous retrieval starts as fast as possible. Thanks Björn -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Björn Wittich Sent: Tuesday, October 21, 2014 3:32 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] extremly bad select performance on huge table Hi Tom and Igor, thanks for your help. With the reindex the select query running time was reduced from 5200 sec to 130 sec. Impressive! Even a join on this table is now fast. Unfortunately, there is now another problem: The table in my example has 500 columns which I want to retrieve with my join command. Example which is fast select value from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn Example which is slow select value,c1,c2,c3,...,c10 from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn Which is the number of columns to fetch so bad ? Which action is done in the db system when querying this via pgadmin? I think that there is no real retrieval included, why is the number of additional columns so bad for the join performance? =?ISO-8859-15?Q?Bj=F6rn_Wittich?= bjoern_witt...@gmx.de writes: Here is the explain (analyze,buffers) select mycolumn from myhugetable Index Only Scan using myprimkey on myhugetable (cost=0.00..822.82 rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 loops=1) Heap Fetches: 356861 Buffers: shared hit=71799472 read=613813 Total runtime: 2503009.611 ms So that works out to about 4 msec per page fetched considering only I/O costs, which is about as good as you're likely to get if the data is sitting on spinning rust. You could potentially make it faster with a VACUUM (to mark all pages all-visible and eliminate the heap fetches costs), or a REINDEX (so that the index scan becomes more nearly sequential instead of random access). However, unless the data is nearly static those will just be temporary fixes: the time will degrade again as you update the table. Note: This select is just for testing. My final statement will be a join on this table via the mycolumn column. In that case it's probably a waste of time to worry about the performance of this query as such. In the first place, a join is not likely to use the index at all unless it's fetching a relatively small number of rows, and in the second place it seems unlikely that the join query can use an IndexOnlyScan on this index --- I imagine that the purpose of the join will require fetching additional columns. regards, tom lane Björn, I think, the timing difference you see between 2 queries is caused by delivering to the front-end (PgAdmin) and displaying all additional columns that you include in the second query (much bigger amount of data to pass from the db to the client). Pretty sure, if you do explain analyze on both queries, you'll see the same timing, because it'll reflect only db time without what's spent on delivering data to the client. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
