Re: [PERFORM] Performance bug in prepared statement binding in 9.2?
Josh Berkus writes: >> What I'm hoping will happen next is that the complainants will hot-patch >> that and see if it fixes their problems. We can't really determine >> what to do without that information. > Unfortunately, the original reporter of this issue will not be available > for testing for 2-3 weeks, and I haven't been able to devise a synthetic > test which clearly shows the issue. Well, we had a synthetic test from the other complainant. What's at stake now is whether this is a good enough fix for real-world cases. I'm willing to wait ... 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] Performance bug in prepared statement binding in 9.2?
Tom, > There's an abbreviated version of this argument in the comments in > my proposed patch at > http://www.postgresql.org/message-id/11927.1384199...@sss.pgh.pa.us > What I'm hoping will happen next is that the complainants will hot-patch > that and see if it fixes their problems. We can't really determine > what to do without that information. Unfortunately, the original reporter of this issue will not be available for testing for 2-3 weeks, and I haven't been able to devise a synthetic test which clearly shows the issue. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Horrific time for getting 1 record from an index?
On Tue, Nov 12, 2013 at 6:22 PM, Jim Nasby wrote: > On 11/12/13 6:17 PM, Jeff Janes wrote: >> >> I verified that this is the case--the empty pages remain linked in the >> tree until a vacuum removes them. But walking through empty leaf pages is >> way faster than resolving pages full of pointers to dead-to-all tuple, so >> the kill code still gives a huge benefit. But of course nothing will do >> much good until the transaction horizon advances. > > > Aaaand... that gets to the other problem... our SAN performance is pretty > abysmal. It took ~270 seconds to read 80MB of index pages (+ whatever heap) > to get to the first live tuple. (This was run close enough to the vacuum > that I don't think visibility of these tuples would have changed > mid-stream). That's awful, but 'par for the course' for SANs in my experience. If my math is right, that works out to 27ms / page read. But each index page read can cause multiple heap page reads depending on how the data is organized so I think you are up against the laws of physics. All we can do is to try and organize data so that access patterns are less radom and/or invest in modern hardware. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] CREATE TABLE AS WITH FREEZE ?
Hello, Does anything speaks again adding a "WITH FREEZE" option to "CREATE TABLE AS" , similar to the new COPY FREEZE feature ? best regards, Marc Mamin
Re: [PERFORM] BitMap Heap Scan & BitMap Index Scan
On 10/11/13 08:32, monalee_dba wrote: > I would like to know, What is BitMap Heap Scan & BitMap Index Scan? When > I use EXPLAIN for query, which has LEFT JOIN with 4 different table then > some time query planner uses Bitmap Heap Scan and some time Bitmap Index > Scan? Check out this great presentation: http://momjian.us/main/writings/pgsql/optimizer.pdf The way I understand it is this (Correct me if I am wrong). The bitmap index scan uses an index to build a bitmap where each bit corresponds to a data buffer (8k). Since a buffer can contain multiple tuples and not all of them must match the condition another run over the heap pages is needed to find the matching tuples. This is the bitmap heap scan. It iterates over the table data buffers found in the bitmap index scan and selects only those tuples that match the filter (hence the recheck thing you see in explain) and visibility conditions. Torsten -- 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] Order By Clause, Slows Query Performance?
monalee_dba wrote: > Eg. SELECT col1, col2, col3,col10 FROM table1; > > For above query If I didn't mention ORDER BY clause, then I want to know > selected data will appear in which order by a query planner? > > Because I have huge size table, and when I applied ORDER BY col1, col2..in > query the > performance is soo bad that I can't offred. > What should I do ? Because my requirement is data with ordered column. A B-Tree index may help with that: http://www.postgresql.org/docs/current/static/indexes-ordering.html Consider a multicolumn index. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance