[PERFORM] problem with select *
Hello, I have a table with 12 columns and 20 Million rows. While writing the table I do not find any problem but when reading that I have some issues faced. When I perform a 'select * from table limit 1400;' (selecting 14million rows), it is working fine. If the limit value is 1500, it is throwing the error as 'out of memory'. If the query is 'select * from table' , The process is getting killed by displaying the message 'killed'. Kindly tell me where it is going wrong. I have 6MB cache, 1.6GHz CPU, linux 14.04 OS, 8GB RAM. Thanks.
Re: [PERFORM] problem with select *
Hi, Please share with us on the configuration in postgresql.conf Thanks! On 24 August 2015 at 15:04, bhuvan Mitra bhuvan...@gmail.com wrote: Hello, I have a table with 12 columns and 20 Million rows. While writing the table I do not find any problem but when reading that I have some issues faced. When I perform a 'select * from table limit 1400;' (selecting 14million rows), it is working fine. If the limit value is 1500, it is throwing the error as 'out of memory'. If the query is 'select * from table' , The process is getting killed by displaying the message 'killed'. Kindly tell me where it is going wrong. I have 6MB cache, 1.6GHz CPU, linux 14.04 OS, 8GB RAM. Thanks. -- Regards, Ang Wei Shan
Re: [PERFORM] problem with select *
På mandag 24. august 2015 kl. 09:04:07, skrev bhuvan Mitra bhuvan...@gmail.com mailto:bhuvan...@gmail.com: Hello, I have a table with 12 columns and 20 Million rows. While writing the table I do not find any problem but when reading that I have some issues faced. When I perform a 'select * from table limit 1400;' (selecting 14million rows), it is working fine. If the limit value is 1500, it is throwing the error as 'out of memory'. If the query is 'select * from table' , The process is getting killed by displaying the message 'killed'. Kindly tell me where it is going wrong. I have 6MB cache, 1.6GHz CPU, linux 14.04 OS, 8GB RAM. In what application are you performing these queries? -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com mailto:andr...@visena.com www.visena.com https://www.visena.com https://www.visena.com
Re: [PERFORM] problem with select *
On 08/24/2015 03:04 AM, bhuvan Mitra wrote: Hello, I have a table with 12 columns and 20 Million rows. While writing the table I do not find any problem but when reading that I have some issues faced. When I perform a 'select * from table limit 1400;' (selecting 14million rows), it is working fine. If the limit value is 1500, it is throwing the error as 'out of memory'. If the query is 'select * from table' , The process is getting killed by displaying the message 'killed'. Kindly tell me where it is going wrong. I have 6MB cache, 1.6GHz CPU, linux 14.04 OS, 8GB RAM. You should be using a cursor. 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] problem with select count(*) ..
On Thu, Nov 20, 2003 at 07:07:30 +0530, Rajesh Kumar Mallah [EMAIL PROTECTED] wrote: If i dump and reload the performance improves and it takes 1 sec. This is what i have been doing since the upgrade. But its not a solution. The Vacuum full is at the end of a loading batch SQL file which makes lot of insert , deletes and updates. If a dump and reload fixes your problem, most likely you have a lot of dead tuples in the table. You might need to run vacuum more often. You might have an open transaction that is preventing vacuum full from cleaning up the table. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] problem with select count(*) ..
Ever Since i upgraded to 7.4RC2 i am facing problem with select count(*) . In 7.3 the problem was not there select count(*) from data_bank.profiles used to return almost instantly , but in 7.4 explain analyze SELECT count(*) from data_bank.profiles; QUERY PLAN --- Aggregate (cost=48361.30..48361.30 rows=1 width=0) (actual time=23456.870..23456.871 rows=1 loops=1) - Seq Scan on profiles (cost=0.00..47431.84 rows=371784 width=0) (actual time=12174.999..23262.823 rows=123928 loops=1) Total runtime: 23458.460 ms (3 rows) tradein_clients=# If i dump and reload the performance improves and it takes 1 sec. This is what i have been doing since the upgrade. But its not a solution. The Vacuum full is at the end of a loading batch SQL file which makes lot of insert , deletes and updates. Regds Mallah. VACUUM FULL VERBOSE ANALYZE data_bank.profiles; INFO: vacuuming data_bank.profiles INFO: profiles: found 430524 removable, 371784 nonremovable row versions in 43714 pages INFO: index profiles_pincode now contains 371784 row versions in 3419 pages INFO: index profiles_city now contains 371784 row versions in 3471 pages INFO: index profiles_branch now contains 371784 row versions in 2237 pages INFO: index profiles_area_code now contains 371784 row versions in 2611 pages INFO: index profiles_source now contains 371784 row versions in 3165 pages INFO: index co_name_index_idx now contains 371325 row versions in 3933 pages INFO: index address_index_idx now contains 371490 row versions in 4883 pages INFO: index profiles_exp_cat now contains 154836 row versions in 2181 pages INFO: index profiles_imp_cat now contains 73678 row versions in 1043 pages INFO: index profiles_manu_cat now contains 87124 row versions in 1201 pages INFO: index profiles_serv_cat now contains 19340 row versions in 269 pages INFO: index profiles_pid now contains 371784 row versions in 817 pages INFO: index profiles_pending_branch_id now contains 0 row versions in 1 pages INFO: profiles: moved 0 row versions, truncated 43714 to 43714 pages INFO: vacuuming pg_toast.pg_toast_67748379 INFO: pg_toast_67748379: found 0 removable, 74 nonremovable row versions in 17 pages INFO: index pg_toast_67748379_index now contains 74 row versions in 2 pages INFO: pg_toast_67748379: moved 1 row versions, truncated 17 to 17 pages INFO: index pg_toast_67748379_index now contains 74 row versions in 2 pages INFO: analyzing data_bank.profiles INFO: profiles: 43714 pages, 3000 rows sampled, 3634 estimated total rows VACUUM Time: 1001525.19 ms ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
