Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Pavel Stehule
Hello 2012/9/2 Sergey Koposov kopo...@ast.cam.ac.uk: Hi, I'm experiencing the case when bitmap scan is ~ 70 times slower than index scan which seems to be caused by 1) very big table 2) some hash search logic (hash_search_with_hash_value ) Here is the explain analyze of the query with

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Peter Geoghegan
On 2 September 2012 06:21, Sergey Koposov kopo...@ast.cam.ac.uk wrote: Hi, I'm experiencing the case when bitmap scan is ~ 70 times slower than index scan which seems to be caused by 1) very big table 2) some hash search logic (hash_search_with_hash_value ) Here is the explain analyze of

Re: [HACKERS] Yet another failure mode in pg_upgrade

2012-09-02 Thread Bruce Momjian
On Sun, Sep 2, 2012 at 01:06:28AM -0400, Robert Haas wrote: For live check operation, you are checking a running server, so assuming the socket is in the current directory is not going to work. What the code does is to read the 5th line from the running server's postmaster.pid file, which

Re: [HACKERS] Yet another failure mode in pg_upgrade

2012-09-02 Thread Bruce Momjian
On Sat, Sep 1, 2012 at 02:35:06PM -0400, Bruce Momjian wrote: On Sat, Sep 1, 2012 at 02:23:22PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: + /* + * Report the Unix domain socket directory location to the postmaster. + */

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Sergey Koposov
On Sun, 2 Sep 2012, Pavel Stehule wrote: statistics on data_objid_idx table are absolutly out - so planner cannot find optimal plan That doesn't have anything to do with the problem, AFAIU. First, the data table is static and was analysed. Second, the query in question is the join, and

Re: [HACKERS] [GENERAL] Multiple Slave Failover with PITR

2012-09-02 Thread Bruce Momjian
Do we ever want to document a way to connect slaves to a new master, rather than recreating the slave? --- On Tue, Mar 27, 2012 at 10:47:48AM -0700, Ken Brush wrote: Hello everyone, I notice that the documentation at:

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Sergey Koposov
Thanks for your comments. On Sun, 2 Sep 2012, Peter Geoghegan wrote: On 2 September 2012 06:21, Sergey Koposov kopo...@ast.cam.ac.uk wrote: I think that this kind of question is better suited to the pgsql-performance list. Granted, it was presented as a bug report (though they're generally sent

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Tom Lane
Sergey Koposov kopo...@ast.cam.ac.uk writes: On Sun, 2 Sep 2012, Peter Geoghegan wrote: One obvious red-flag from your query plans is that there is a misestimation of the row return count of a few orders of magnitude in the Bitmap Index Scan node. Did you trying performing an ANALYZE to see

Re: [HACKERS] Fwd: PATCH: psql boolean display

2012-09-02 Thread Phil Sorber
On Sun, Sep 2, 2012 at 1:13 AM, Pavel Stehule pavel.steh...@gmail.com wrote: -- Forwarded message -- From: Pavel Stehule pavel.steh...@gmail.com Date: 2012/9/1 Subject: PATCH: psql boolean display To: Phil Sorber p...@omniti.com Hello I am looking to your patch: I have

Re: [HACKERS] Fwd: PATCH: psql boolean display

2012-09-02 Thread Tom Lane
Phil Sorber p...@omniti.com writes: What my patch was intended to do was let the end user set boolean output to any arbitrary values. While foo/bar is pretty useless, it was meant to reinforce that it was capable of any arbitrary value. I can think of a decent list of other output an end user

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Sergey Koposov
On Sun, 2 Sep 2012, Tom Lane wrote: Sergey Koposov kopo...@ast.cam.ac.uk writes: The problem is definitely the misestimation here: - Index Scan using data_objid_idx on data d1 (cost=0.00..26603.32 rows=415080 width=40) (actual time=0.010..0.050 rows=248 loops=11456)

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Peter Geoghegan
On 2 September 2012 16:26, Sergey Koposov kopo...@ast.cam.ac.uk wrote: After looking at them, I think I understand the reason -- the number of n_distinct for crts.data is terribly wrong. In reality it should be ~ 130 millions. I already faced this problem at certain point when doing group by

Re: [HACKERS] Fwd: PATCH: psql boolean display

2012-09-02 Thread Tom Lane
I wrote: Phil Sorber p...@omniti.com writes: What my patch was intended to do was let the end user set boolean output to any arbitrary values. While foo/bar is pretty useless, it was meant to reinforce that it was capable of any arbitrary value. I can think of a decent list of other output an

Re: [HACKERS] bitmap scan much slower than index scan, hash_search_with_hash_value

2012-09-02 Thread Sergey Koposov
On Sun, 2 Sep 2012, Peter Geoghegan wrote: On 2 September 2012 16:26, Sergey Koposov kopo...@ast.cam.ac.uk wrote: That's why we support altering that value with an ALTER TABLE...ALTER COLUMN DDL statement. You might at least consider increasing the statistics target for the column first

Re: [HACKERS] Fwd: PATCH: psql boolean display

2012-09-02 Thread Pavel Stehule
2012/9/2 Phil Sorber p...@omniti.com: On Sun, Sep 2, 2012 at 1:13 AM, Pavel Stehule pavel.steh...@gmail.com wrote: -- Forwarded message -- From: Pavel Stehule pavel.steh...@gmail.com Date: 2012/9/1 Subject: PATCH: psql boolean display To: Phil Sorber p...@omniti.com Hello

Re: [HACKERS] Fwd: PATCH: psql boolean display

2012-09-02 Thread Pavel Stehule
2012/9/2 Tom Lane t...@sss.pgh.pa.us: I wrote: Phil Sorber p...@omniti.com writes: What my patch was intended to do was let the end user set boolean output to any arbitrary values. While foo/bar is pretty useless, it was meant to reinforce that it was capable of any arbitrary value. I can

Re: [HACKERS] Yet another failure mode in pg_upgrade

2012-09-02 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: On Sun, Sep 2, 2012 at 01:06:28AM -0400, Robert Haas wrote: I don't think this is reducing the number of failure modes; it's just changing it from one set of obscure cases to a slightly different set of obscure cases. Tom reported problems with having

[HACKERS] [bugfix] sepgsql missed a case of CREATE TABLE AS

2012-09-02 Thread Kohei KaiGai
The attached patch fixes a bug in sepgsql module. Could you apply both v9.2 and master branch? When post_create hook is invoked, sepgsql's handler checks whether the current invocation context needs to have permission checks according to the command-tag saved on ProcessUtility_hook. But it

[HACKERS] [bugfix] sepgsql didn't follow the latest core API changes

2012-09-02 Thread Kohei KaiGai
This patch fixes a few portions on which sepgsql didn't follow the latest core API changes. 1) Even though the prototype of ProcessUtility_hook was recently changed, sepgsql side didn't follow this update, so it made build failed. 2) sepgsql internally uses GETSTRUCT() and HeapTupleGetOid()

[HACKERS] GPU and Database

2012-09-02 Thread Gaetano Mendola
May be someone of you is interested in this ADBIS workshop on GPUs in Databases http://gid2012.cs.put.poznan.pl/ Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] Yet another failure mode in pg_upgrade

2012-09-02 Thread Bruce Momjian
On Sun, Sep 2, 2012 at 01:13:52PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Sun, Sep 2, 2012 at 01:06:28AM -0400, Robert Haas wrote: I don't think this is reducing the number of failure modes; it's just changing it from one set of obscure cases to a slightly

Re: [HACKERS] Proof of concept: auto updatable views

2012-09-02 Thread Dean Rasheed
On 31 August 2012 07:59, Dean Rasheed dean.a.rash...@gmail.com wrote: On 30 August 2012 20:05, Robert Haas robertmh...@gmail.com wrote: On Sun, Aug 12, 2012 at 5:14 PM, Dean Rasheed dean.a.rash...@gmail.com wrote: None of this new code kicks in for non-security barrier views, so the kinds of

Re: [HACKERS] [v9.3] Row-Level Security

2012-09-02 Thread Dean Rasheed
On 17 July 2012 05:02, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2012/7/17 Robert Haas robertmh...@gmail.com: On Sun, Jul 15, 2012 at 5:52 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The attached patch is a revised version of row-level security feature. ... According to the Robert's comment, I

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-09-02 Thread Jeff Janes
On Thu, Aug 30, 2012 at 8:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Aug 30, 2012 at 4:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: On Thu, May 31, 2012 at 09:20:43AM +0900, Tatsuo Ishii wrote: Ok, I modified

[HACKERS] Fwd: [PERFORM] Loose Index Scans by Planner?

2012-09-02 Thread Jeff Janes
I'd like to create a ToDo item for loose index scans or skip scans, when the lead column has low cardinality and is not used in the where clause. This case can potentially be optimized by using the index as if it were a collection of N partitioned indexes, where N is the cardinality of the lead

Re: [HACKERS] PATCH: pgbench - random sampling of transaction written into log

2012-09-02 Thread Tomas Vondra
Dne 31.08.2012 00:01, Tomas Vondra napsal: On 30 Srpen 2012, 23:44, Robert Haas wrote: On Thu, Aug 30, 2012 at 3:48 PM, Tomas Vondra t...@fuzzy.cz wrote: That sounds like a pretty trivial patch. I've been thinking about yet another option - histograms (regular or with exponential bins). I

Re: [HACKERS] PATCH: pgbench - aggregation of info written into log

2012-09-02 Thread Tomas Vondra
Dne 30.08.2012 18:02, Robert Haas napsal: On Fri, Aug 24, 2012 at 5:25 PM, Tomas Vondra t...@fuzzy.cz wrote: This patch is a bit less polished (and more complex) than the other pgbench patch I've sent a while back, and I'm not sure how to handle the Windows branch. That needs to be fixed

Re: [HACKERS] PATCH: pgbench - aggregation of info written into log

2012-09-02 Thread Jeff Janes
On Sun, Sep 2, 2012 at 3:46 PM, Tomas Vondra t...@fuzzy.cz wrote: Fixed. I've kept use_log_agg only and I've removed the default. Also I've added one more check (that the total duration is a multiple of the aggregation interval). Hi Tomas, In the docs, -l is an option, not an application:

[HACKERS] pg_upgrade bugs

2012-09-02 Thread Andrew Dunstan
I have been wrestling for a couple of days trying to get pg_upgrade testing working on Windows, with a view to having it tested on the buildfarm. The test script has its own issues, which I'll deal with separately, but there are two issues in pg_upgrade's exec.c that make me suspect that if

Re: [HACKERS] pg_upgrade bugs

2012-09-02 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes: I have been wrestling for a couple of days trying to get pg_upgrade testing working on Windows, with a view to having it tested on the buildfarm. The test script has its own issues, which I'll deal with separately, but there are two issues in

Re: [HACKERS] Proof of concept: standalone backend with full FE/BE protocol

2012-09-02 Thread Heikki Linnakangas
On 03.09.2012 03:23, Tom Lane wrote: 1. As you can see above, the feature is triggered by specifying the new connection option standalone_datadir, whose value must be the location of the data directory. I also invented an option standalone_backend, which can be set to specify which postgres

Re: [HACKERS] pg_upgrade bugs

2012-09-02 Thread Alvaro Herrera
Excerpts from Andrew Dunstan's message of dom sep 02 22:11:42 -0300 2012: I have been wrestling for a couple of days trying to get pg_upgrade testing working on Windows, with a view to having it tested on the buildfarm. The test script has its own issues, which I'll deal with separately,

[HACKERS] pg_upgrade test mods for Windows/Mingw

2012-09-02 Thread Andrew Dunstan
The attached patch is what I had to do to get pg_upgrade's make check to run on Windows under Mingw. Mostly the changes have to do with getting paths right between Windows and MSys, or calling generated .bat files instead of shell scripts. cheers andrew diff --git

Re: [HACKERS] Proof of concept: standalone backend with full FE/BE protocol

2012-09-02 Thread Tom Lane
Heikki Linnakangas hlinn...@iki.fi writes: On 03.09.2012 03:23, Tom Lane wrote: 1. As you can see above, the feature is triggered by specifying the new connection option standalone_datadir, whose value must be the location of the data directory. I also invented an option standalone_backend,

Re: [HACKERS] Yet another failure mode in pg_upgrade

2012-09-02 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: Updated patch attached. [ looks at that for a bit... ] Now I see why you were on about that: the method you used here requires both clusters to have the same socket directory. Which is silly and unnecessary. Revised patch attached.

Re: [HACKERS] pg_upgrade test mods for Windows/Mingw

2012-09-02 Thread Gurjeet Singh
On Sun, Sep 2, 2012 at 11:29 PM, Andrew Dunstan and...@dunslane.net wrote: The attached patch is what I had to do to get pg_upgrade's make check to run on Windows under Mingw. Mostly the changes have to do with getting paths right between Windows and MSys, or calling generated .bat files

Re: [HACKERS] pg_upgrade bugs

2012-09-02 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes: Maybe, to reduce future backpatching pain, we could backpatch the change to exec_prog() API now that you have fixed the implementation? I'm inclined to think this is a good idea, but keep in mind we're less than four days from wrapping 9.2.0.

Re: [HACKERS] Proof of concept: standalone backend with full FE/BE protocol

2012-09-02 Thread Noah Misch
On Sun, Sep 02, 2012 at 11:34:42PM -0400, Tom Lane wrote: Heikki Linnakangas hlinn...@iki.fi writes: On 03.09.2012 03:23, Tom Lane wrote: 1. As you can see above, the feature is triggered by specifying the new connection option standalone_datadir, whose value must be the location of the

Re: [HACKERS] Proof of concept: standalone backend with full FE/BE protocol

2012-09-02 Thread Tom Lane
Noah Misch n...@leadboat.com writes: On Sun, Sep 02, 2012 at 11:34:42PM -0400, Tom Lane wrote: Heikki Linnakangas hlinn...@iki.fi writes: Are there security issues with this? If a user can specify libpq connection options, he can now execute any file he wants by passing it as

Re: [HACKERS] pg_upgrade test mods for Windows/Mingw

2012-09-02 Thread Tom Lane
Gurjeet Singh singh.gurj...@gmail.com writes: [ this needs a comment: ] `uname -a | sed 's/.* //'` = Msys Also, how about doing that just once and setting a variable to test as needed later? Multiple copied-and-pasted instances of line noise like that are risky.

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-09-02 Thread Robert Haas
On Sun, Sep 2, 2012 at 5:39 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Thu, Aug 30, 2012 at 8:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Aug 30, 2012 at 4:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: On

Re: [HACKERS] Proof of concept: standalone backend with full FE/BE protocol

2012-09-02 Thread Tom Lane
Noah Misch n...@leadboat.com writes: Windows does not have socketpair(), nor a strict pipe() equivalent. I expect switching to socketpair() makes the Windows side trickier in some ways and simpler in others. +1 for exploring that direction first. A bit of googling suggests that emulating

Re: [HACKERS] Proof of concept: standalone backend with full FE/BE protocol

2012-09-02 Thread Noah Misch
On Mon, Sep 03, 2012 at 12:11:20AM -0400, Tom Lane wrote: Noah Misch n...@leadboat.com writes: I don't think it's libpq's job to enforce security policy this way. In any event, it has no reason to presume an environment variable is a safer source. One easy thing we could do that would