Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Albe Laurenz
Greg Stark wrote: It's also applicable for the other stats; histogram buckets constructed from a 5% sample are more likely to be accurate than those constructed from a 0.1% sample. Same with nullfrac. The degree of improved accuracy, would, of course, require some math to determine. This

Re: Custom Scan APIs (Re: [HACKERS] Custom Plan node)

2013-12-10 Thread Shigeru Hanada
Hi KaiGai-san, 2013/12/8 Kohei KaiGai kai...@kaigai.gr.jp: The attached patches include documentation fixup by Hanada-san, and relocation of is_managed_relation (the portion to check whether the relation is a foreign table managed by a particular FDW) and has_wholerow_reference. I didn't

[HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Peter Geoghegan
pg_stat_statements' fingerprinting logic considers the following two statements as distinct: select 1 in (1, 2, 3); select 1 in (1, 2, 3, 4); This is because the ArrayExpr jumble case jumbles any ArrayExpr's list of elements recursively. In this case it's a list of Const nodes, and the

Re: [HACKERS] Time-Delayed Standbys

2013-12-10 Thread Andres Freund
On 2013-12-10 13:26:27 +0900, KONDO Mitsumasa wrote: (2013/12/09 20:29), Andres Freund wrote: On 2013-12-09 19:51:01 +0900, KONDO Mitsumasa wrote: Add my comment. We have to consider three situations. 1. PITR 2. replication standby 3. replication standby with restore_command I think

Re: [HACKERS] tracking commit timestamps

2013-12-10 Thread Heikki Linnakangas
On 10/23/2013 01:16 AM, Alvaro Herrera wrote: There has been some interest in keeping track of timestamp of transaction commits. This patch implements that. There are some seemingly curious choices here. First, this module can be disabled, and in fact it's turned off by default. At startup,

Re: [HACKERS] tracking commit timestamps

2013-12-10 Thread Andres Freund
On 2013-12-10 11:56:45 +0200, Heikki Linnakangas wrote: Speaking of the functionality this does offer, it seems pretty limited. A commit timestamp is nice, but it isn't very interesting on its own. You really also want to know what the transaction did, who ran it, etc. ISTM some kind of a

Re: [HACKERS] Optimize kernel readahead using buffer access strategy

2013-12-10 Thread Claudio Freire
On Tue, Dec 10, 2013 at 5:03 AM, KONDO Mitsumasa kondo.mitsum...@lab.ntt.co.jp wrote: I revise this patch and re-run performance test, it can work collectry in Linux and no complile wanings. I add GUC about enable_kernel_readahead option in new version. When this GUC is on(default), it works in

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Greg Stark
On 10 Dec 2013 08:28, Albe Laurenz laurenz.a...@wien.gv.at wrote: Doesn't all that assume a normally distributed random variable? I don't think so because of the law of large numbers. If you have a large population and sample it the sample behaves like a normal distribution when if the

Re: [HACKERS] New option for pg_basebackup, to specify a different directory for pg_xlog

2013-12-10 Thread Alvaro Herrera
Haribabu kommi escribió: To detect provided data and xlog directories are same or not, I reused the Existing make_absolute_path() code as follows. 1. Moved the make_absolute_path() function from miscinit.c to path.c and Changed all error reporting functions. And also it returns NULL incase

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Albe Laurenz
Greg Stark wrote: Doesn't all that assume a normally distributed random variable? I don't think so because of the law of large numbers. If you have a large population and sample it the sample behaves like a normal distribution when if the distribution of the population isn't. Statistics

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Claudio Freire
On Tue, Dec 10, 2013 at 11:02 AM, Greg Stark st...@mit.edu wrote: On 10 Dec 2013 08:28, Albe Laurenz laurenz.a...@wien.gv.at wrote: Doesn't all that assume a normally distributed random variable? I don't think so because of the law of large numbers. If you have a large population and

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Claudio Freire
On Tue, Dec 10, 2013 at 11:32 AM, Claudio Freire klaussfre...@gmail.com wrote: On Tue, Dec 10, 2013 at 11:02 AM, Greg Stark st...@mit.edu wrote: On 10 Dec 2013 08:28, Albe Laurenz laurenz.a...@wien.gv.at wrote: Doesn't all that assume a normally distributed random variable? I don't think

Re: [HACKERS] Extra functionality to createuser

2013-12-10 Thread Amit Kapila
On Tue, Dec 10, 2013 at 12:20 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Dec 7, 2013 at 11:39 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Fri, Dec 6, 2013 at 10:31 AM, Peter Eisentraut pete...@gmx.net wrote: How about only one role name per -g option, but allowing the -g option

Re: [HACKERS] Compression of tables

2013-12-10 Thread Merlin Moncure
On Tue, Dec 10, 2013 at 1:18 AM, Thomas Munro mu...@ip9.org wrote: Hi I have been wondering what the minimum useful heap table compression system would be for Postgres, in order to reduce disk footprint of large mostly static datasets. Do you think an approach similar to the static

Re: [HACKERS] JSON decoding plugin

2013-12-10 Thread Merlin Moncure
On Mon, Dec 9, 2013 at 10:53 AM, Euler Taveira eu...@timbira.com.br wrote: On 09-12-2013 13:12, Merlin Moncure wrote: This is pretty neat. Couple minor questions: *) Aren't you *en*coding data into json, not the other way around (decoding?) Yes. The 'decoding' came from the functionality

Re: [HACKERS] pg_archivecleanup bug

2013-12-10 Thread Bruce Momjian
On Thu, Dec 5, 2013 at 12:06:07PM -0800, Kevin Grittner wrote: An EDB customer reported a problem with pg_archivecleanup which I have looked into and found a likely cause.  It is, in any event, a bug which I think should be fixed.  It has to do with our use of the readdir() function:

[HACKERS] Why standby.max_connections must be higher than primary.max_connections?

2013-12-10 Thread 山田聡
* Hello hackers. I am struggling to understand why standby.max_connections must be higher than primary.max_connections.Do http://primary.max_connections.Do someone know the reason why? I know that standby cluster can not start if standby.max_connections is higher than primary.max_connections.

Re: [HACKERS] Dynamic Shared Memory stuff

2013-12-10 Thread Noah Misch
On Thu, Dec 05, 2013 at 06:12:48PM +0200, Heikki Linnakangas wrote: On 11/20/2013 09:58 PM, Robert Haas wrote: On Wed, Nov 20, 2013 at 8:32 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: * As discussed in the Something fishy happening on frogmouth thread, I don't like the fact that

[HACKERS] coredump of 9.3.2

2013-12-10 Thread Teodor Sigaev
Hi! Rather simple script (original query was a 500 lines of SQL with 100 Mb of gzipped dump. Query is looked strange, but actually it was auto-generated): CREATE TABLE t (a int, b int); CREATE TABLE tt (c int); INSERT INTO t VALUES (1,1), (2,2); INSERT INTO tt VALUES (2); SELECT * FROM

Re: [HACKERS] Backup throttling

2013-12-10 Thread Antonin Houska
Thanks for checking. The new version addresses your findings. // Antonin Houska (Tony) On 12/09/2013 03:49 PM, Fujii Masao wrote: On Fri, Dec 6, 2013 at 6:43 PM, Boszormenyi Zoltan z...@cybertec.at wrote: Hi, 2013-12-05 15:36 keltezéssel, Antonin Houska írta: On 12/02/2013 02:23 PM,

Re: [HACKERS] Dynamic Shared Memory stuff

2013-12-10 Thread Heikki Linnakangas
On 12/10/2013 07:27 PM, Noah Misch wrote: On Thu, Dec 05, 2013 at 06:12:48PM +0200, Heikki Linnakangas wrote: On 11/20/2013 09:58 PM, Robert Haas wrote: On Wed, Nov 20, 2013 at 8:32 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: * As discussed in the Something fishy happening on

Re: [HACKERS] Errors on missing pg_subtrans/ files with 9.3

2013-12-10 Thread Josh Berkus
Andres, all: We've just run across a case of this exact issue on 9.2.4. I thought it was supposed to be 9.3-only? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] Errors on missing pg_subtrans/ files with 9.3

2013-12-10 Thread Andres Freund
Hi, On 2013-12-10 10:38:32 -0800, Josh Berkus wrote: We've just run across a case of this exact issue on 9.2.4. I thought it was supposed to be 9.3-only? Could you please describe this exact issue? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/

Re: [HACKERS] Errors on missing pg_subtrans/ files with 9.3

2013-12-10 Thread Josh Berkus
On 12/10/2013 10:39 AM, Andres Freund wrote: Hi, On 2013-12-10 10:38:32 -0800, Josh Berkus wrote: We've just run across a case of this exact issue on 9.2.4. I thought it was supposed to be 9.3-only? Could you please describe this exact issue? Fatal errors due to missing pg_subtrans

Re: [HACKERS] coredump of 9.3.2

2013-12-10 Thread Josh Berkus
On 12/10/2013 09:39 AM, Teodor Sigaev wrote: SELECT * FROM t WHERE ( CASE WHEN a%2 IN (SELECT c FROM tt) THEN a END IN (SELECT c FROM tt) ); Wow, it wouldn't have occured to me that that was even supported syntax. I'm not suprised that it doesn't work ... --

Re: [HACKERS] Errors on missing pg_subtrans/ files with 9.3

2013-12-10 Thread Andres Freund
On 2013-12-10 10:44:30 -0800, Josh Berkus wrote: On 12/10/2013 10:39 AM, Andres Freund wrote: Hi, On 2013-12-10 10:38:32 -0800, Josh Berkus wrote: We've just run across a case of this exact issue on 9.2.4. I thought it was supposed to be 9.3-only? Could you please describe this

Re: [HACKERS] Errors on missing pg_subtrans/ files with 9.3

2013-12-10 Thread Josh Berkus
On 12/10/2013 10:48 AM, Andres Freund wrote: On 2013-12-10 10:44:30 -0800, Josh Berkus wrote: On 12/10/2013 10:39 AM, Andres Freund wrote: Hi, On 2013-12-10 10:38:32 -0800, Josh Berkus wrote: We've just run across a case of this exact issue on 9.2.4. I thought it was supposed to be

Re: [HACKERS] coredump of 9.3.2

2013-12-10 Thread Tom Lane
Teodor Sigaev teo...@sigaev.ru writes: SELECT * FROM t WHERE ( CASE WHEN a%2 IN (SELECT c FROM tt) THEN a END IN (SELECT c FROM tt) ); I suppose, the problem is connected to hashed subplan, but I'm not very familiar with executor. And this affects all

Re: [HACKERS] Errors on missing pg_subtrans/ files with 9.3

2013-12-10 Thread Andres Freund
On 2013-12-10 11:12:03 -0800, Josh Berkus wrote: On 12/10/2013 10:48 AM, Andres Freund wrote: On 2013-12-10 10:44:30 -0800, Josh Berkus wrote: On 12/10/2013 10:39 AM, Andres Freund wrote: Hi, On 2013-12-10 10:38:32 -0800, Josh Berkus wrote: We've just run across a case of this exact

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Simon Riggs
On 6 December 2013 09:21, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-05 17:52:34 -0800, Peter Geoghegan wrote: Has anyone ever thought about opportunistic ANALYZE piggy-backing on other full-table scans? That doesn't really help Greg, because his complaint is mostly that a fresh

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Andres Freund
On 2013-12-10 19:23:37 +, Simon Riggs wrote: On 6 December 2013 09:21, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-05 17:52:34 -0800, Peter Geoghegan wrote: Has anyone ever thought about opportunistic ANALYZE piggy-backing on other full-table scans? That doesn't really help

Re: [HACKERS] GIN improvements part 1: additional information

2013-12-10 Thread Alexander Korotkov
On Tue, Dec 10, 2013 at 12:26 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 12/09/2013 11:34 AM, Alexander Korotkov wrote: On Mon, Dec 9, 2013 at 1:18 PM, Heikki Linnakangas hlinnakan...@vmware.comwrote: Even if we use varbyte encoding, I wonder if it would be better to treat

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 11:23 AM, Simon Riggs si...@2ndquadrant.com wrote: However, these things presume that we need to continue scanning most of the blocks of the table, which I don't think needs to be the case. There is a better way. Do they? I think it's one opportunistic way of

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Simon Riggs
On 10 December 2013 19:49, Peter Geoghegan p...@heroku.com wrote: On Tue, Dec 10, 2013 at 11:23 AM, Simon Riggs si...@2ndquadrant.com wrote: However, these things presume that we need to continue scanning most of the blocks of the table, which I don't think needs to be the case. There is a

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Josh Berkus
On 12/10/2013 11:49 AM, Peter Geoghegan wrote: On Tue, Dec 10, 2013 at 11:23 AM, Simon Riggs si...@2ndquadrant.com wrote: I don't think that anyone believes that not doing block sampling is tenable, fwiw. Clearly some type of block sampling would be preferable for most or all purposes. As

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Greg Stark
On Tue, Dec 10, 2013 at 7:54 PM, Josh Berkus j...@agliodbs.com wrote: As discussed, we need math though. Does anyone have an ACM subscription and time to do a search? Someone must. We can buy one with community funds, but no reason to do so if we don't have to. Anyone in a university likely

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Simon Riggs
On 10 December 2013 19:54, Josh Berkus j...@agliodbs.com wrote: On 12/10/2013 11:49 AM, Peter Geoghegan wrote: On Tue, Dec 10, 2013 at 11:23 AM, Simon Riggs si...@2ndquadrant.com wrote: I don't think that anyone believes that not doing block sampling is tenable, fwiw. Clearly some type of

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 11:59 AM, Greg Stark st...@mit.edu wrote: But I don't really think this is the right way to go about this. Research papers are going to turn up pretty specialized solutions that are probably patented. We don't even have the basic understanding we need. I suspect a basic

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Heikki Linnakangas
On 12/10/2013 10:00 PM, Simon Riggs wrote: On 10 December 2013 19:54, Josh Berkus j...@agliodbs.com wrote: On 12/10/2013 11:49 AM, Peter Geoghegan wrote: On Tue, Dec 10, 2013 at 11:23 AM, Simon Riggs si...@2ndquadrant.com wrote: I don't think that anyone believes that not doing block sampling

Re: [HACKERS] Reference to parent query from ANY sublink

2013-12-10 Thread Antonin Houska
On 12/06/2013 03:33 PM, Kevin Grittner wrote: Antonin Houska antonin.hou...@gmail.com wrote: SELECT * FROMtab1 a LEFT JOIN tab2 b ON a.i = ANY ( SELECT k FROMtab3 c WHEREk = a.i); This query works with k in any or all tables, but

Re: [HACKERS] stats for network traffic WIP

2013-12-10 Thread Robert Haas
On Tue, Dec 10, 2013 at 12:29 AM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Dec 10, 2013 at 6:56 AM, Nigel Heron nhe...@querymetrics.com wrote: On Sat, Dec 7, 2013 at 1:17 PM, Fujii Masao masao.fu...@gmail.com wrote: Could you share the performance numbers? I'm really concerned about

Re: [HACKERS] plpgsql_check_function - rebase for 9.3

2013-12-10 Thread Robert Haas
On Tue, Dec 10, 2013 at 1:45 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Now, PG has no any tool for checking dependency between functions and other objects. What has positive effects - we have very simply deploying, that works in almost use cases very well - and works with our temporary

Re: [HACKERS] tracking commit timestamps

2013-12-10 Thread Robert Haas
On Tue, Dec 10, 2013 at 4:56 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Generally speaking, I'm not in favor of adding dead code, even if it might be useful to someone in the future. For one, it's going to get zero testing. Once someone comes up with an actual use case, let's add

Re: [HACKERS] plpgsql_check_function - rebase for 9.3

2013-12-10 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: This is a very good point. Annotating the function itself with markers that cause it to be more strictly checked will create a dump/reload problem that we won't enjoy solving. The decision to check the function more strictly or not would need to be

Re: [HACKERS] tracking commit timestamps

2013-12-10 Thread Alvaro Herrera
Robert Haas escribió: On Tue, Dec 10, 2013 at 4:56 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Speaking of the functionality this does offer, it seems pretty limited. A commit timestamp is nice, but it isn't very interesting on its own. You really also want to know what the

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Robert Haas
On Tue, Dec 10, 2013 at 4:30 AM, Peter Geoghegan p...@heroku.com wrote: pg_stat_statements' fingerprinting logic considers the following two statements as distinct: select 1 in (1, 2, 3); select 1 in (1, 2, 3, 4); This is because the ArrayExpr jumble case jumbles any ArrayExpr's list of

Re: [HACKERS] tracking commit timestamps

2013-12-10 Thread Robert Haas
On Tue, Dec 10, 2013 at 4:04 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas escribió: On Tue, Dec 10, 2013 at 4:56 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Speaking of the functionality this does offer, it seems pretty limited. A commit timestamp is nice, but it

Re: [HACKERS] Why standby.max_connections must be higher than primary.max_connections?

2013-12-10 Thread Robert Haas
On Tue, Dec 10, 2013 at 3:34 AM, 山田聡 satoshi.yamada...@gmail.com wrote: Hello hackers. I am struggling to understand why standby.max_connections must be higher than primary.max_connections.Do someone know the reason why? Because the KnownAssignedXIDs and lock tables on the standby need to be

Re: [HACKERS] plpgsql_check_function - rebase for 9.3

2013-12-10 Thread Josh Berkus
On 12/10/2013 12:50 PM, Tom Lane wrote: One would hope that turning off check_function_bodies would be sufficient to disable any added checking, though, so I don't see this being a problem for pg_dump. But there might be other scenarios where an additional knob would be useful. I can't think

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Mark Kirkwood
On 11/12/13 09:19, Heikki Linnakangas wrote: On 12/10/2013 10:00 PM, Simon Riggs wrote: On 10 December 2013 19:54, Josh Berkus j...@agliodbs.com wrote: On 12/10/2013 11:49 AM, Peter Geoghegan wrote: On Tue, Dec 10, 2013 at 11:23 AM, Simon Riggs si...@2ndquadrant.com wrote: I don't think that

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 1:09 PM, Robert Haas robertmh...@gmail.com wrote: I am very wary of implementing special-case logic here even though I know it could be useful to some people, simply because I fear that there could be a near-infinite variety of situations where, in a particular

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Andres Freund
On 2013-12-10 16:09:02 -0500, Robert Haas wrote: On Tue, Dec 10, 2013 at 4:30 AM, Peter Geoghegan p...@heroku.com wrote: I'm not sure that I agree, but there is anecdata that suggests that it isn't uncommon for these sorts of queries to be broken out when they're all traceable back to a

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Josh Berkus
On 12/10/2013 01:33 PM, Mark Kirkwood wrote: Yeah - and we seem to be back to Josh's point about needing 'some math' to cope with the rows within a block not being a purely random selection. Well, sometimes they are effectively random. But sometimes they are not. The Chaudri et al paper had a

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Greg Stark
On Tue, Dec 10, 2013 at 7:49 PM, Peter Geoghegan p...@heroku.com wrote: Back in 2005/6, I advocated a block sampling method, as described by Chaudri et al (ref?) I don't think that anyone believes that not doing block sampling is tenable, fwiw. Clearly some type of block sampling would be

Re: [HACKERS] stats for network traffic WIP

2013-12-10 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Yes, I think the overhead of this patch is far, far too high to contemplate applying it. It sends a stats collector message after *every socket operation*. Once per transaction would likely be too much overhead already (think: pgbench -S) but once

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Dec 10, 2013 at 4:30 AM, Peter Geoghegan p...@heroku.com wrote: pg_stat_statements' fingerprinting logic considers the following two statements as distinct: select 1 in (1, 2, 3); select 1 in (1, 2, 3, 4); [ and some people think it

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 1:41 PM, Andres Freund and...@2ndquadrant.com wrote: FWIW, I hit exactly this issue every single time I have looked at pg_stat_statements in some client's database making it nearly useless for analysis. So improving it might be worthwile. I think the thing that makes me

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Andres Freund
On 2013-12-10 14:30:36 -0800, Peter Geoghegan wrote: Did you really find pg_stat_statements to be almost useless in such situations? That seems worse than I thought. It's very hard to see where you should spend efforts when every logical query is split into hundreds of pg_stat_statement

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 2:38 PM, Andres Freund and...@2ndquadrant.com wrote: It's very hard to see where you should spend efforts when every logical query is split into hundreds of pg_stat_statement entries. Suddenly it's important whether a certain counts of parameters are more frequent than

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Robert Haas
On Tue, Dec 10, 2013 at 5:38 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-10 14:30:36 -0800, Peter Geoghegan wrote: Did you really find pg_stat_statements to be almost useless in such situations? That seems worse than I thought. It's very hard to see where you should spend

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 2:46 PM, Robert Haas robertmh...@gmail.com wrote: Right, but the flip side is that you could collapse things that people don't want collapsed. If you've got lots of query that differ only in that some of them say user_id IN (const1, const2) and others say user_id IN

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Andres Freund
On 2013-12-10 17:46:56 -0500, Robert Haas wrote: On Tue, Dec 10, 2013 at 5:38 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-10 14:30:36 -0800, Peter Geoghegan wrote: Did you really find pg_stat_statements to be almost useless in such situations? That seems worse than I thought.

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 2:55 PM, Peter Geoghegan p...@heroku.com wrote: You might get lucky and have this exact case, and be able to leverage the knowledge that the 2 constants in the ArrayExpr must be the latter and 1 must be the former, but experience suggests very probably not. When things

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Jeff Janes
On Mon, Dec 9, 2013 at 2:37 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Dec 9, 2013 at 4:18 PM, Jeff Janes jeff.ja...@gmail.com wrote: My reading of the code is that if it is not in the MCV, then it is assumed to have the average selectivity (about 1/n_distinct, but deflating top

Re: [HACKERS] Reference to parent query from ANY sublink

2013-12-10 Thread Kevin Grittner
Antonin Houska antonin.hou...@gmail.com wrote: I used the DDLs attached (tables.ddl) for this query too, not only for the queries in quaries.sql. Yes, if I had mentioned it and/or qualified the 'k' column reference, it wouldn't have broken anything. Apologies; I missed the attachments.  It

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Right, but the flip side is that you could collapse things that people don't want collapsed. If you've got lots of query that differ only in that some of them say user_id IN (const1, const2) and others say user_id IN (const1, const2, const3) and the

Re: [HACKERS] Dynamic Shared Memory stuff

2013-12-10 Thread Noah Misch
On Tue, Dec 10, 2013 at 07:50:20PM +0200, Heikki Linnakangas wrote: On 12/10/2013 07:27 PM, Noah Misch wrote: On Thu, Dec 05, 2013 at 06:12:48PM +0200, Heikki Linnakangas wrote: On Wed, Nov 20, 2013 at 8:32 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: * As discussed in the Something

Re: [HACKERS] Dynamic Shared Memory stuff

2013-12-10 Thread Andres Freund
On 2013-12-10 18:12:53 -0500, Noah Misch wrote: On Tue, Dec 10, 2013 at 07:50:20PM +0200, Heikki Linnakangas wrote: On 12/10/2013 07:27 PM, Noah Misch wrote: On Thu, Dec 05, 2013 at 06:12:48PM +0200, Heikki Linnakangas wrote: Let's not add more cases like that, if we can avoid it. Only

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Daniel Farina
On Tue, Dec 10, 2013 at 3:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: So my objection to what Peter is suggesting is not that it's a bad idea in isolation, but that I don't see where he's going to stop, short of reinventing every query-normalization behavior that exists in the planner. If this

Re: [HACKERS] Dynamic Shared Memory stuff

2013-12-10 Thread Tom Lane
Noah Misch n...@leadboat.com writes: On Tue, Dec 10, 2013 at 07:50:20PM +0200, Heikki Linnakangas wrote: Let's not add more cases like that, if we can avoid it. Only if we can avoid it for a modicum of effort and feature compromise. You're asking for PostgreSQL to reshape its use of

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Jim Nasby
On 12/10/13 2:17 PM, Peter Geoghegan wrote: On Tue, Dec 10, 2013 at 11:59 AM, Greg Stark st...@mit.edu wrote: But I don't really think this is the right way to go about this. Research papers are going to turn up pretty specialized solutions that are probably patented. We don't even have the

Re: [HACKERS] Why we are going to have to go DirectIO

2013-12-10 Thread Jim Nasby
Just to add a data point (and sorry, I can't find where someone was talking about numbers in the thread)... For a while earlier this year we were running a 3.x kernel and saw a very modest (1-2%) improvement in overall performance. This would be on a server with 512G RAM running ext4. -- Jim

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 3:26 PM, Jim Nasby j...@nasby.net wrote: I agree that looking for information on block level sampling specifically, and its impact on estimation quality is likely to not turn up very much, and whatever it does turn up will have patent issues. We have an entire

Re: [HACKERS] logical changeset generation v6.8

2013-12-10 Thread Robert Haas
On Wed, Dec 4, 2013 at 10:55 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-03 15:19:26 -0500, Robert Haas wrote: Yeah, you're right. I think the current logic will terminate when all flags are set to false or all attribute numbers have been checked, but it doesn't know that if

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Simon Riggs
On 10 December 2013 23:43, Peter Geoghegan p...@heroku.com wrote: On Tue, Dec 10, 2013 at 3:26 PM, Jim Nasby j...@nasby.net wrote: I agree that looking for information on block level sampling specifically, and its impact on estimation quality is likely to not turn up very much, and whatever it

Re: [HACKERS] Why we are going to have to go DirectIO

2013-12-10 Thread Jeff Janes
On Tue, Dec 3, 2013 at 11:39 PM, Claudio Freire klaussfre...@gmail.comwrote: On Wed, Dec 4, 2013 at 4:28 AM, Tatsuo Ishii is...@postgresql.org wrote: Can we avoid the Linux kernel problem by simply increasing our shared buffer size, say up to 80% of memory? It will be swap more easier.

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 3:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: A different point of view is that it's more or less an implementation artifact that pg_stat_statements doesn't already see the cases as equivalent; that happens only because it looks at the querytree before the planner gets

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Greg Stark
On Wed, Dec 11, 2013 at 12:14 AM, Simon Riggs si...@2ndquadrant.com wrote: Block sampling, with parameter to specify sample size. +1 Simon this is very frustrating. Can you define block sampling? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] Why we are going to have to go DirectIO

2013-12-10 Thread Andres Freund
On 2013-12-04 05:39:23 -0200, Claudio Freire wrote: Problem is, Postgres relies on a working kernel cache for checkpoints. Checkpoint logic would have to be heavily reworked to account for an impaired kernel cache. I don't think checkpoints are the critical problem with that, they are nicely

Re: [HACKERS] logical changeset generation v6.8

2013-12-10 Thread Robert Haas
On Wed, Dec 4, 2013 at 10:55 AM, Andres Freund and...@2ndquadrant.com wrote: I've primarily sent this, because I don't know of further required changes in 0001-0003. I am trying reviewing the other patches in detail atm. Committed #3 also. -- Robert Haas EnterpriseDB:

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Simon Riggs
On 11 December 2013 00:28, Greg Stark st...@mit.edu wrote: On Wed, Dec 11, 2013 at 12:14 AM, Simon Riggs si...@2ndquadrant.com wrote: Block sampling, with parameter to specify sample size. +1 Simon this is very frustrating. Can you define block sampling? Blocks selected using Vitter's

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Greg Stark
On Wed, Dec 11, 2013 at 12:40 AM, Simon Riggs si...@2ndquadrant.com wrote: When we select a block we should read all rows on that block, to help identify the extent of clustering within the data. So how do you interpret the results of the sample read that way that doesn't introduce bias? --

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Peter Geoghegan
On Tue, Dec 10, 2013 at 4:14 PM, Simon Riggs si...@2ndquadrant.com wrote: err, so what does stats target mean exactly in statistical theory? Why would I even mention that to a statistician? We want guidance. But yes, I bet I could give a statistician an explanation of statistics target that

Re: [HACKERS] [COMMITTERS] pgsql: Add a new reloption, user_catalog_table.

2013-12-10 Thread Michael Paquier
On Wed, Dec 11, 2013 at 9:34 AM, Robert Haas rh...@postgresql.org wrote: Add a new reloption, user_catalog_table. Sorry for not having completely followed the thread of logical replication, but wouldn't this deserve some documentation? Regards, -- Michael -- Sent via pgsql-hackers mailing

[HACKERS] Why the buildfarm is all pink

2013-12-10 Thread Tom Lane
I was surprised to see that my back-patches of the recent SubLink unpleasantness were failing on many of the buildfarm members, but only in the 9.1 and 9.0 branches. The difficulty appears to be that the EXPLAIN output for the new test query changes depending on whether or not tenk1 has been

Re: [HACKERS] [COMMITTERS] pgsql: Add a new reloption, user_catalog_table.

2013-12-10 Thread Andres Freund
On 2013-12-11 09:54:36 +0900, Michael Paquier wrote: On Wed, Dec 11, 2013 at 9:34 AM, Robert Haas rh...@postgresql.org wrote: Add a new reloption, user_catalog_table. Sorry for not having completely followed the thread of logical replication, but wouldn't this deserve some documentation? I'd

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Simon Riggs
On 11 December 2013 00:44, Greg Stark st...@mit.edu wrote: On Wed, Dec 11, 2013 at 12:40 AM, Simon Riggs si...@2ndquadrant.com wrote: When we select a block we should read all rows on that block, to help identify the extent of clustering within the data. So how do you interpret the results of

Re: [HACKERS] Why we are going to have to go DirectIO

2013-12-10 Thread Claudio Freire
On Tue, Dec 10, 2013 at 9:22 PM, Jeff Janes jeff.ja...@gmail.com wrote: Communicating more with the kernel (through posix_fadvise, fallocate, aio, iovec, etc...) would probably be good, but it does expose more kernel issues. posix_fadvise, for instance, is a double-edged sword ATM. I do

Re: [HACKERS] Why we are going to have to go DirectIO

2013-12-10 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: On Tue, Dec 3, 2013 at 11:39 PM, Claudio Freire klaussfre...@gmail.comwrote: Problem is, Postgres relies on a working kernel cache for checkpoints. Checkpoint logic would have to be heavily reworked to account for an impaired kernel cache. I don't

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Sergey E. Koposov
For what it's worth. I'll quote Chaudhuri et al. first line from the abstract about the block sampling. Block-level sampling is far more efficient than true uniform-random sampling over a large database, but prone to significant errors if used to create database statistics. And after

Re: [HACKERS] pg_stat_statements fingerprinting logic and ArrayExpr

2013-12-10 Thread Tom Lane
Peter Geoghegan p...@heroku.com writes: On Tue, Dec 10, 2013 at 3:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm wondering whether this doesn't indicate that we need to rethink where the fingerprinter has been plugged in. I'm not sure that somewhere in the planner, post-constant-folding, would

Re: [HACKERS] Get more from indices.

2013-12-10 Thread Etsuro Fujita
I wrote: Kyotaro HORIGUCHI wrote: I'm convinced of the validity of your patch. I'm satisfied with it. Then, if there are no objections of others, I'll mark this as Ready for Committer. Done. Thanks, Best regards, Etsuro Fujita -- Sent via pgsql-hackers mailing list

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Tom Lane
Peter Geoghegan p...@heroku.com writes: Again, it isn't as if the likely efficacy of *some* block sampling approach is in question. I'm sure analyze.c is currently naive about many things. It's not *that* naive; this is already about a third-generation algorithm. The last major revision

Re: [HACKERS] ANALYZE sampling is too good

2013-12-10 Thread Jeff Janes
On Tuesday, December 10, 2013, Simon Riggs wrote: On 11 December 2013 00:28, Greg Stark st...@mit.edu javascript:; wrote: On Wed, Dec 11, 2013 at 12:14 AM, Simon Riggs si...@2ndquadrant.comjavascript:; wrote: Block sampling, with parameter to specify sample size. +1 Simon this is

Re: [HACKERS] Why we are going to have to go DirectIO

2013-12-10 Thread Jeff Janes
On Tuesday, December 10, 2013, Tom Lane wrote: Jeff Janes jeff.ja...@gmail.com javascript:; writes: On Tue, Dec 3, 2013 at 11:39 PM, Claudio Freire klaussfre...@gmail.comjavascript:; wrote: Problem is, Postgres relies on a working kernel cache for checkpoints. Checkpoint logic would

Re: [HACKERS] Why the buildfarm is all pink

2013-12-10 Thread Robert Haas
On Tue, Dec 10, 2013 at 7:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: This doesn't make me happy. Aside from the sheer waste of cycles involved in re-analyzing the entire regression database, this test runs in parallel with half a dozen others, and it could cause plan instability in those. Of

Re: [HACKERS] Completing PL support for Event Triggers

2013-12-10 Thread Peter Eisentraut
On Mon, 2013-12-09 at 09:45 +0100, Dimitri Fontaine wrote: It looks like you started with the v1 of the plperl patch rather than the v2, where the only difference is in only using is_trigger or using both is_trigger and is_event_trigger. Your version currently uses both where I though we chose

Re: [HACKERS] logical changeset generation v6.8

2013-12-10 Thread Robert Haas
On Wed, Dec 4, 2013 at 10:55 AM, Andres Freund and...@2ndquadrant.com wrote: [ updated logical decoding patches ] Regarding patch #4, introduce wal decoding via catalog timetravel, which seems to be the bulk of what's not committed at this point... - I think this needs SGML documentation, same

Re: [HACKERS] [COMMITTERS] pgsql: Add a new reloption, user_catalog_table.

2013-12-10 Thread Robert Haas
On Tue, Dec 10, 2013 at 7:56 PM, Andres Freund and...@2ndquadrant.com wrote: On 2013-12-11 09:54:36 +0900, Michael Paquier wrote: On Wed, Dec 11, 2013 at 9:34 AM, Robert Haas rh...@postgresql.org wrote: Add a new reloption, user_catalog_table. Sorry for not having completely followed the

Re: [HACKERS] Why the buildfarm is all pink

2013-12-10 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Dec 10, 2013 at 7:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Anyway, bottom line is that I think we need to institute, and back-patch, some consistent scheme for when to analyze the standard tables during the regression tests, so that we don't

  1   2   >