Re: [GENERAL] 2 left joins causes seqscan
> But the two queries don't return the same results. Of course the > second one will be faster. > The equivalent of your first query is to take the result sets from > these two queries (...) > it's not > too surprising that the planner can't come up with the optimal > plan; you've posed quite a challenge for it. > The point that i was trying to make by doing 2 queries and unioning them is, that it is faster to use 2 index scans than to use sequential scans. I can't quite recognize the challenge that i'm posing the query planner, but i am willing/hoping to learn more about it. AFAIK, the planner has some statistics about the frequencies in which values in the columns occur. That way, it can calculate the approx number of records that will have to be fetched and considering the latency of a rotating hard disk, it can calculate what is likely to be faster: a sequential scan or using the index for random reads. In this case, the planner can calculate the number of records that need to be fetched from B, in my case it says it expects 4 of them in both cases. Combined, it would fetch max 8 records from B, in contrast to 40K or even twice that. I can't understand what is confusing the planner. Cheers, Willy-Bas
[GENERAL] Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on Linux?
Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on Linux? Thanks, Edson -- 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] Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on Linux?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/13/2014 08:24 AM, Edson Carlos Ericksson Richter wrote: > Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on > Linux? No personal experience, but someone recently pointed out this article to me: "Btrfs considered … helpful" http://no0p.github.io/postgresql/2014/09/06/benchmarking-postgresql-btrfs-zlib.html Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1 iQIcBAEBAgAGBQJUFImdAAoJEDfy90M199hlvocQAJLgkZ9wppb/FjXPgZs5aWKZ 4DSm/V1CxcVLOjf4TdmnrbUPugu6wR1da03wvfrLSaLmk7mte45X2kLh4KFZ6I2T LMo1EoJwkQWS7a0/wNkPjeWmdG1UMbVTEhCSEC1MF/8LTOjd+2EXGvZM84msyUc5 PanWJvddJouoqNTdhHUigEifkDhjsbvB/xewVPmXhEwqMahnRXG8emO1H7D+uCz9 1kZqX8NdWJlTPh5a42ZIE5JflymQ5MFWt3V42SEy1AxK5ooBTkIafgMhJTfIssB4 yl6X2AwTQnuSZh0Rpea8f+uhSyOGlErdhbkgXQRpPyXF6PN19nHSFSfYDF4xQCfA fBHznprz4yL0Olbo/5u7/wjMV1dlN8ZNsZD/bMTSbR/B1FRvCxT1k9LAaTDdqXqb m+NvD6RY78A8T4iaFpfSU3Kits+WN9+q8iqAQI20mi2ECC+2FaDQxzDYQ8hWt5rR A7D/9LzOZvPvVN2teVEmrP2vhbOBFyV+bpAGR4v3l5MhIOMoyv4QaWD9ol1sktw/ 0qRB9iDecGwt8zPqhssireVrN4KfkGLcRQytlyl/sjyyiYq2sP4JNfKQQMby2mWc 2BjWxUZKrgv+dKulSj7uMhIsxXf3kH6VeHmk43rQ1wg9geb1oW3oTsffwxhRJR7F Nc1qLPqaCU7KMiJY32TE =xmyh -END PGP SIGNATURE- -- 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] Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on Linux?
On 09/13/2014 11:14 AM, Joe Conway wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/13/2014 08:24 AM, Edson Carlos Ericksson Richter wrote: Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on Linux? Yes. It ran great for over a month but once we had some serious data in it (multiple terrabytes) it died, more than once, and the last time was so bad it was unrecoverable. This was just this summer and yes we were using the latest. We have found the ZFS on Linux is working quite nicely. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc "If we send our children to Caesar for their education, we should not be surprised when they come back as Romans." -- 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] Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on Linux?
I do have a development machine using constrained SSD disk, and I would like to take advantage of compression. But would not want to reformat everything on ZFS (I'm actually using Ext4). Nevertheless, seems that Joe had a bad experience with Btrfs (my expectation was to just migrate from Ext4 to Btfrs and be able to take advantage of a brand new FS features). Thanks, I'll play a little with both (using VM) and check if there is anything simpler. Edson On 13-09-2014 16:32, Joshua D. Drake wrote: On 09/13/2014 11:14 AM, Joe Conway wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/13/2014 08:24 AM, Edson Carlos Ericksson Richter wrote: Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on Linux? Yes. It ran great for over a month but once we had some serious data in it (multiple terrabytes) it died, more than once, and the last time was so bad it was unrecoverable. This was just this summer and yes we were using the latest. We have found the ZFS on Linux is working quite nicely. JD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] After upgrade from 9.2.9 to 9.3.5 got reduced contention
After a week or so since upgrade from 9.2.9 to 9.3.5 I'm very surprised (in a good way) by the gain of performance. Most noticeable change is the reduced contention for records by exclusive/shared locks. I can see increased numbers of requests fulfilled per minute (but I can't be exact on those numbers, just I can see graph going high - which is good). Also, I've noticed that backup time dropped from 44 minutes to 30 minutes (for about 60Gb in multiple databases) - which is really good change (35% decrease) on same hardware. I still had no time to measure how much gain on improved indexing and other topics I know received improvements. Thanks, PostgreSQL team! Regards, Edson -- 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] After upgrade from 9.2.9 to 9.3.5 got reduced contention
On Sat, Sep 13, 2014 at 07:44:44PM -0300, Edson Carlos Ericksson Richter wrote: > After a week or so since upgrade from 9.2.9 to 9.3.5 I'm very > surprised (in a good way) by the gain of performance. > > Most noticeable change is the reduced contention for records by > exclusive/shared locks. > > I can see increased numbers of requests fulfilled per minute (but I > can't be exact on those numbers, just I can see graph going high - > which is good). That is very good. The new key-value locking must have helped here. It was very hard to implement, but I am glad it is showing a benefit. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
