[GENERAL] memory problem with refresh materialized view
Hi I have a new postgresql 9.5.0 installation on a new virtual server debian 8.3 x64 with 4gb RAM, I have compiled postgresql from source. When I import a dump with materialized views I see that postgres process takes about all 4 Gb and then I have this error fork: Cannot allocate memory Can anyone help me? Thanks in advantage :) Enrico -- PostgreSQL openday Rimini 27-02-2016 <http://www.postgresql.org/about/event/1861/> Enrico Pirozzi Tel. +39 0861 1855771 - Mob.+39 328 4164437 - Fax +39 0861 1850310 http://www.pgtraining.com <http://www.pgtraining.com-> - i...@pgtraining.com www.enricopirozzi.info - i...@enricopirozzi.info Skype sscotty71 - Gtalk sscott...@gmail.com
Re: [GENERAL] Index scan vs indexonly scan method
The background on index-only scans: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2#Index-only_scans In either case(index, index-only) the index has to be scanned. The difference is where the data is pulled from. In the index-only scan the query still needs to consult the visibility map to determine whether the tuple pointed to by the index entry is visible. I would say that in the limit 1 case the planner determines it is just as easy to check and pull the data from the actual tuple as to to check the visibility map. In the limit 100 case it becomes more cost effective to use the visibility map and pull data directly from the index data. Thank you for your answer Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 - Mob.+39 328 4164437 - Fax +39 0861 1850310 http://www.pgtraining.com <http://www.pgtraining.com-> - i...@pgtraining.com www.enricopirozzi.info - i...@enricopirozzi.info PEC: enrico.piro...@pec.it Skype sscotty71 - Gtalk sscott...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Index scan vs indexonly scan method
Hi, I was working on this simple query select field1 from table A where A.field2 <= some_value order by 1 desc limit some_value and I saw that: 1) the planner on this query uses an index only scan method: select field1 from table A where A.field2 <= '2014-08-13 10:20:59.99648+02' order by 1 desc limit 100 2) the planner on this query uses a classic index scan method: select field1 from table A where A.field2 <= '2014-08-13 10:20:59.99648+02' order by 1 desc limit 1 the only difference between the two queries is the limit clause, for the first query the limit is 100 and for the second the limit is 1 it seems a little bit strange...someone can help me to understand why? My develop PostgreSQL version is a 9.4 beta regards, Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 - Mob.+39 328 4164437 - Fax +39 0861 1850310 http://www.pgtraining.com <http://www.pgtraining.com-> - i...@pgtraining.com www.enricopirozzi.info - i...@enricopirozzi.info PEC: enrico.piro...@pec.it Skype sscotty71 - Gtalk sscott...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question on explain
Il 10/06/2013 04:19, Jeff Janes ha scritto: On further thought, that is not strange at all. You have two very selective join conditions, and the planner assumes they are independent, so that it can multiply the selectivities. But in reality they are completely (or almost completely) dependent. If the planner knew about cross column correlations, that might not even help as you can have complete statistical dependence without having correlation. It seems unlikely to me that the timestamp belongs in both tables, since it's value seems to be completely dependent on the value of the UUID. In any event, it is unlikely the planner would pick a different plan were it to correctly understand the selectivities, so no harm is done. Although it is easy to imagine similar queries where that would not be the case. Cheers, Jeff Thank you very much ;) Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 - Mob.+39 328 4164437 - Fax +39 0861 1850310 http://www.pgtraining.com - i...@pgtraining.com www.enricopirozzi.info - i...@enricopirozzi.info Skype sscotty71 - Gtalk sscott...@gmail.com smime.p7s Description: Firma crittografica S/MIME
[GENERAL] Question on explain
Hi all, I have 2 tables: table1 (field1,field2,.) table2 (field1,field2,field3,.) field1 is an uuid type field2 is a timestamp with time zone type. If I execute: explain (analyze on, timing off) select B.field3,A.field1,A.field2 FROM table1 A INNER JOIN table2 B using (field1,field2) the query plan is QUERY PLAN Hash Join (cost=137324.20..247200.77 rows=1 width=31) (actual rows=1136175 loops=1) Hash Cond: ((b.field1 = a.field1) AND (b.field2 = a.field2)) -> Seq Scan on table2 b (cost=0.00..49694.75 rows=1136175 width=31) (actual rows=1136175 loops =1) -> Hash (cost=89610.68..89610.68 rows=2287368 width=24) (actual rows=1143684 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 15699kB -> Seq Scan on table1 a (cost=0.00..89610.68 rows=2287368 width=24) (actual rows=1143684 loops=1 ) Total runtime: 5055.118 ms (7 rows) My question is Why Have I a rows=1 in the explain and rows=1136175 in the explain analyze? I tried to tune Planner Cost Constants as default_statistics_target (integer) and / or from_collapse_limit (integer) join_collapse_limit (integer) but the query plan has been the same. Can anyone help me ? Thanks, Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 - Mob.+39 328 4164437 - Fax +39 0861 1850310 http://www.pgtraining.com <http://www.pgtraining.com-> - i...@pgtraining.com www.enricopirozzi.info - i...@enricopirozzi.info Skype sscotty71 - Gtalk sscott...@gmail.com
Re: [GENERAL] PostgreSQL Magazine #01 is out !
Great Job!!! ;) 2012/5/9 Simon Riggs : > On 9 May 2012 13:02, PostgreSQL Magazine wrote: >> Dear PostgreSQL users, >> >> I am very pleased to announce the release of the first issue of >> PostgreSQL Magazine. >> >> This issue #01 is brought to you thanks to the collective work of >> dozen of people. Writers, Editors, Reviewers. Kudos to them all ! >> >> Here's a quick view of the ToC : >> >> - PostgreSQL 9.1 : 10 awesome new features >> - NoSQL : The Key Value store everyone ignored >> - Interview : Stefan Kaltenbrunner >> - Opinion : Funding PostgreSQL Features >> - Waiting for 9.2 : Cascading Streaming Replication >> - Tips & Tricks : PostgreSQL in Mac OS X Lion >> >> The magazine is available online and on paper. You can either : >> >> * Read it Online: http://pgmag.org/01/read >> * Buy the Print Edition: http://pgmag.org/01/buy >> * or Download the PDF: http://pgmag.org/01/download >> >> The magazine is currently available only in "US Letter" and "A4" format. >> >> Finally, I would like to thank our benefactors… Fotolia.com has offered >> us a free subscription plan to access their stock photo database. We >> also received fundings from PostgreSQL Europe (PGEU) and Software in the >> Public Interest (SPI). Thanks a lot to them ! > > Well done. This is very good. > > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 Mob.+39 328 4164437 Fax +39 0861 1850310 www.enricopirozzi.info i...@enricopirozzi.info Skype sscotty71 Gtalk sscott...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ODBC mac os
Thank you very much ;) Enrico 2010/1/29 Dave Page : > On Fri, Jan 29, 2010 at 1:29 PM, Enrico Pirozzi wrote: >> Hi, >> I would like to find an odbc driver for mac os x, >> where I can find it? >> >> Thanks to all > > If you're running the one-click PG installer, you can install the ODBC > driver using StackBuilder. > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 Mob.+39 328 4164437 Fax +39 0861 1850310 www.enricopirozzi.info i...@enricopirozzi.info Skype sscotty71 Gtalk sscott...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ODBC mac os
Hi, I would like to find an odbc driver for mac os x, where I can find it? Thanks to all regards, Enrico -- That's one small step for man; one giant leap for mankind -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Records Number
> If you want an accurate, up-to-date count then you'll need to use > count(*) or have a trigger keep a summary-count for you. A simple > implementation will reduce concurrency to writes on that table however. Yes I solved by a trigger > Lots of discussion in the mailing-list archives on this. Thank you for your time Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 Mob.+39 328 4164437 Fax +39 0861 1850310 www.enricopirozzi.info i...@enricopirozzi.info Skype sscotty71 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Records Number
Hi all, I have a little problem, I would like to execute select * from table and i would like to retrieve the number of records without make select count(*) from table I could use directly the table instead of select, and in this this case I'm searching for something like the reltuples field in the pg_class table, but I need this value in real time. Any suggestion? Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 Mob.+39 328 4164437 Fax +39 0861 1850310 www.enricopirozzi.info i...@enricopirozzi.info Skype sscotty71 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] returns numbers of record
No I'm looking something else, as I wrote above :) Regards Enrico 2008/12/3 justin <[EMAIL PROTECTED]>: > Select Count(*) from (query) is what i believe you are looking for see > http://www.postgresql.org/docs/8.3/interactive/tutorial-agg.html > > > > Enrico Pirozzi wrote: >> >> Hi all, >> does it exists a way to know how many records a query returns? >> >> I thought sometime like >> >> DECLARE curs1 CURSOR FOR select * from table ; >> >> I thought if I can write MOVE LAST Is there any way to return >> the number of row for select * from table without execute a count(*) ? >> >> Thanks :) >> >> Enrico >> >> > > -- That's one small step for man; one giant leap for mankind www.enricopirozzi.info [EMAIL PROTECTED] Skype sscotty71 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] returns numbers of record
Hi all, does it exists a way to know how many records a query returns? I thought sometime like DECLARE curs1 CURSOR FOR select * from table ; I thought if I can write MOVE LAST Is there any way to return the number of row for select * from table without execute a count(*) ? Thanks :) Enrico -- That's one small step for man; one giant leap for mankind www.enricopirozzi.info [EMAIL PROTECTED] Skype sscotty71 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unsigned int type
Hi all, Is it present on postgresql 8.3.x any implementation of an unsigned int type? Regards, Enrico -- That's one small step for man; one giant leap for mankind www.enricopirozzi.info [EMAIL PROTECTED] Skype sscotty71 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
