[GENERAL] Creating table with data from a join

Hello, I am benchmarking different ways of putting data into table on table creation: 1. INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id; 2. CREATE TABLE c AS SELECT * FROM a JOIN b on a.id = b.id; 3. psql -c "COPY (SELECT * FROM a JOIN b on a.id = b.id) TO STDOUT" | parallel --block 128M --j

Re: [GENERAL] Index Only Scan vs Cache

Does PG has its own data cache? I don't think so. I think PG just using the filesystem cache. On Fri, Jul 10, 2015 at 2:06 AM, Andy Colson wrote: > On 7/9/2015 12:41 PM, Tom Lane wrote: > >> Andy Colson writes: >> >>> My question is: Will PG cache only the index (assuming it can always do >>>

Re: [GENERAL] Index Only Scan vs Cache

2015-07-14 11:59 GMT+02:00 Shujie Shang : > Does PG has its own data cache? I don't think so. > PG has own data cache - see shared_buffers Regards Pavel > I think PG just using the filesystem cache. > > On Fri, Jul 10, 2015 at 2:06 AM, Andy Colson wrote: > >> On 7/9/2015 12:41 PM, Tom Lane w

[GENERAL] Where to place suggestions for documentation improvements

Hello I have a generic question. Where should I/we place suggestions on possible improvements of the documentation? Is it here or better on pgsql-docs? Thanks Charles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgr

Re: [GENERAL] Creating table with data from a join

On 14 July 2015 at 21:12, Igor Stassiy wrote: > Hello, > > I am benchmarking different ways of putting data into table on table > creation: > > 1. INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id; > 2. CREATE TABLE c AS SELECT * FROM a JOIN b on a.id = b.id; > 3. psql -c "COPY (SELECT * FROM a

Re: [GENERAL] Creating table with data from a join

Thank you David. I tried to measure the time of COPY (SELECT * FROM a JOIN b ON a.id = b.id) TO '/tmp/dump.sql' and it took an order of magnitude time less (~10x) than the complete command (together with INSERT), so conversion is probably not the main factor of slowdown (unless conversion from text

Re: [GENERAL] Where to place suggestions for documentation improvements

On Tue, Jul 14, 2015 at 8:17 PM, Charles Clavadetscher wrote: > I have a generic question. Where should I/we place suggestions on possible > improvements of the documentation? Is it here or better on pgsql-docs? pgsql-docs may be a better place than this list dedicated to general questions, still

Re: [GENERAL] Where to place suggestions for documentation improvements

Thank you Michael I will post my suggestions on pgsql-docs to make sure ;-) Bye Charles > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Michael Paquier > Sent: Dienstag, 14. Juli 2015 14:21 > To: Charles Clavadet

Re: [GENERAL] could not create shared memory segment: Invalid argument

On 7/13/2015 7:08 PM, Ryan King - NOAA Affiliate wrote: Apologies ahead of time for not knowing which group to send to, but I wanted to see if anyone has encountered and resolved this type of error. I'm setting up postgresql 9.2 streaming replication on RH and after copying the master data direct

Re: [GENERAL] Creating table with data from a join

On 14/07/2015 11:12, Igor Stassiy wrote: > Hello, > > I am benchmarking different ways of putting data into table on table > creation: > > 1. INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id > ; > 2. CREATE TABLE c AS SELECT * FROM a JOIN b on a.id = b.

Re: [GENERAL] Creating table with data from a join

Julien, I have the following setting for WAL level: #wal_level = minimal (which defaults to minimal anyway) On Tue, Jul 14, 2015 at 6:19 PM Julien Rouhaud wrote: > On 14/07/2015 11:12, Igor Stassiy wrote: > > Hello, > > > > I am benchmarking different ways of putting data into table on table > >

Re: [GENERAL] Creating table with data from a join

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 14/07/2015 18:21, Igor Stassiy wrote: > Julien, I have the following setting for WAL level: #wal_level = > minimal (which defaults to minimal anyway) > > On Tue, Jul 14, 2015 at 6:19 PM Julien Rouhaud > mailto:julien.rouh...@dalibo.com>> > wrote:

Re: [GENERAL] Creating table with data from a join

On 14/07/2015 18:21, Igor Stassiy wrote: > Julien, I have the following setting for WAL level: #wal_level = minimal > (which defaults to minimal anyway) > Sorry, I sent my mail too early :/ So, option #2 is winner by design. You didn't say anything about your needs, so it's hard to help you much

Re: [GENERAL] Creating table with data from a join

Julien, I would gladly provide more information, I am just not sure what to add. I would be willing to leave the server compromised for things like corrupts or data losses during the time of this import, but the server has to be up and running before and after the import, if it is successful (so I

Re: [GENERAL] A table of magic constants

Dane Foster wrote: > Hi Michael, > > You nailed it. I am reading the documentation cover to cover. I started > chapter 9 two weeks ago but haven't found the time to read beyond 9.1 yet. > But for day to day usage on the MySQL to PostgreSQL migration project that > I'm working on I jump around in t

Re: [GENERAL] Creating table with data from a join

>Hello, > >I am benchmarking different ways of putting data into table on table creation: > >1. INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id; >2. CREATE TABLE c AS SELECT * FROM a JOIN b on a.id = b.id; >3. psql -c "COPY (SELECT * FROM a JOIN b on a.id = b.id) TO STDOUT" | >parallel --block

Re: [GENERAL] Index Only Scan vs Cache

>On 7/9/2015 12:41 PM, Tom Lane wrote: >> Andy Colson writes: >>> My question is: Will PG cache only the index (assuming it can always do >>> an Index Only Scan), or will it cache the table as well? I'm not sure that indexes on tiny tables are useful. They raise the options to consider by the q

Re: [GENERAL] timestamp check

Yes,But i need to display last digits also [image: Inline image 1] like 1500 08-09-10.738901 On Mon, Jul 13, 2015 at 8:18 PM, Francisco Olarte wrote: > Hi Ramesh: > > On Sun, Jul 12, 2015 at 8:21 AM, Ramesh T > wrote: > >> postgres query >> select current_timestamp- >> TO_TIMESTAMP(to_char(DA

Re: [GENERAL] timestamp check

i added .MS getting values,But Problem query keep on running but not displaying results,when i add like limit 5.it is return values.. what is the problem with query..? changed date and changed_dttimezone are are parameters.. select to_char((current_timestamp - TO_TIMESTAMP(to_char(chaged_date,'

Re: [GENERAL] timestamp check

On 07/14/2015 07:13 AM, Ramesh T wrote: i added .MS getting values,But Problem query keep on running but not displaying results,when i add like limit 5.it is return values.. what is the problem with query..? As has been explained several times already, subtracting one timestamp

Re: [GENERAL] Index Only Scan vs Cache

On 7/14/2015 1:19 PM, Marc Mamin wrote: On 7/9/2015 12:41 PM, Tom Lane wrote: Andy Colson writes: My question is: Will PG cache only the index (assuming it can always do an Index Only Scan), or will it cache the table as well? I'm not sure that indexes on tiny tables are useful. They rais

Re: [GENERAL] Index Only Scan vs Cache

On Tue, Jul 14, 2015 at 2:19 PM, Marc Mamin wrote: > > Any rule of the thumb with which number of pages per relation it is worth > to start indexing ? The code for the monitoring tool check_postgres uses table size larger than 5.12kb as a rule of thumb, expecting that for tables smaller than 5.

Re: [GENERAL] Disconnected but query still running

Eduardo Piombino wrote: > (the timer had stopped almost exactly at 2 hours, 7.210.123 ms to > be exact, which makes me think of a 2 hours timeout somewhere). The most likely cause of this would be that you were accessing the server through a firewall with neither the client nor the server config

Re: [GENERAL] Creating table with data from a join

On 14/07/2015 18:50, Igor Stassiy wrote: > Julien, I would gladly provide more information, I am just not sure what > to add. > Well, was your concern about why option #2 is the quickest, or is this runtime with option #2 still too slow for you ? > I would be willing to leave the server compromi

Re: [GENERAL] unexpected data beyond EOF in block 260 of relation pg_tblspc

Hi team, kindly help me with the below query- Below alarm was raised at the system where postgres database was used.. --- BGWPOL22-00:/var/opt/mediation/MMStorage6/Server8/CXC1734739_R8J/stora