Re: [PERFORM] Index-Advisor Tools

I have not used it yet, but from the presentation, very promising: https://medium.com/@ankane/introducing-dexter-the-automatic-indexer-for-postgres-5f8fa8b28f27 https://github.com/ankane/dexter -- https://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via pgsql-performance mailing list

Re: [PERFORM] Millions of tables

Something that is not talked about at all in this thread is caching. A bunch of memcache servers in front of the DB should be able to help with the 30ms constraint (doesn't have to be memcache, some caching technology). -- http://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via

Re: [PERFORM] Many-to-many performance problem

I thought this was a really interesting case, and would love to learn from it, please bare with me if my questions are naive. On 2016-06-10 08:13, Tom Lane wrote: > Rowan Seymour writes: >> Most of time, this query performs like https://explain.depesz.com/s/ksOC >>

Re: [PERFORM] Testing in AWS, EBS

On 2016-05-26 09:03, Artem Tomyuk wrote: > Why no? Or you missed something? I think Rayson is correct, but the double negative makes it hard to read: "So no EBS pre-warming does not apply to EBS volumes created from snapshots." Which I interpret as: So, "no EBS pre-warming", does not apply to

Re: [PERFORM] Testing in AWS, EBS

On 2016-05-25 19:08, Rayson Ho wrote: > Actually, when "EBS-Optimized" is on, then the instance gets dedicated > bandwidth to EBS. Hadn't realised that, thanks. Is the EBS bandwidth then somewhat limited depending on the type of instance too? -- http://yves.zioup.com gpg: 4096R/32B0F416 --

Re: [PERFORM] Testing in AWS, EBS

Indeed, old-style disk EBS vs new-style SSd EBS. Be aware that EBS traffic is considered as part of the total "network" traffic, and each type of instance has different limits on maximum network throughput. Those difference are very significant, do tests on the same volume between two different

Re: [PERFORM] Proposal for unlogged tables

On 2016-01-04 02:59, Mark Zealey wrote: > shutdown all unlogged tables will be truncated. Obviously with 9.5 we can now > alter tables to be logged/unlogged after insert but this will still write all > the inserts into the WAL. I haven't tried, but won't converting an unlogged table into a logged

Re: [PERFORM] SELECT slows down on sixth execution

On 2015-10-14 03:00, Albe Laurenz wrote: > > You are encountering "custom plans", introduced in 9.2. > > When a statement with parameters is executed, PostgreSQL will not only > generate > a generic plan, but for the first 5 executions it will substitute the > arguments > and generate and

Re: [PERFORM] How to reduce writing on disk ? (90 gb on pgsql_tmp)

On 2015-06-03 16:29, Joshua D. Drake wrote: On 06/03/2015 03:16 PM, Tomas Vondra wrote: What is more important, though, is the amount of memory. OP reported the query writes ~95GB of temp files (and dies because of full disk, so there may be more). The on-disk format is usually more

Re: [PERFORM] Postgres is using 100% CPU

On 2015-05-31 07:04, Jean-David Beyer wrote: On 05/30/2015 09:46 AM, Ashik S L wrote: We are using postgres SQL version 8.4.17.. Postgres DB szie is 900 MB and we are inserting 273 rows at once .and each row is of 60 bytes.Every time we insert 16380 bytes of data. Way back when, I was

Re: [PERFORM] Postgres is using 100% CPU

machine. As soon as it starts to Inserting rows into the postgres DB it will reach 100%cpu. It will comedown to normal after 40 minutes. I tried perform How many rows are you inserting at once? How (sql insert? copy? \copy? using a temp or unlogged table?)? -- http://yves.zioup.com gpg:

Re: [PERFORM] extract(year from date) doesn't use index but maybe could?

On 2015-04-19 15:33, Tom Lane wrote: It's possible that in the future someone will implement an optimization like this, but I'm not aware of anyone working on that and I wouldn't hold my breath. Yeah. In principle you could make the planner do this. As Adam Williams notes nearby,

Re: [PERFORM] unlogged tables

On 2015-04-13 17:49, Jeff Janes wrote: One way would be to lock dirty buffers from unlogged relations into shared_buffers (which hardly seems like a good thing) until the start of a super-checkpoint and then write them all out as fast as possible (which kind of defeats

Re: [PERFORM] unlogged tables

On 2015-04-13 14:16, dgabriel wrote: That will be a very useful feature. I agree, unlogged tables would be a lot more useful if they didn't disappear on re-start. could be expensive. Also i have to detect when postgres crush, i have no idea how i can detect if postgres crushed. Then i have