Re: [HACKERS] ECPG FETCH readahead

On Jun 24, 2010, at 2:13 PM, Michael Meskes wrote: I think, yes, it does make sense. Because we are talking about porting a whole lot of COBOL applications. COBOL??? yes, COBOL :). it is much more common than people think. it is not the first COBOL request for PostgreSQL hitting my desk.

[HACKERS] cross column correlation revisted

hello everybody, we are currently facing some serious issues with cross correlation issue. consider: 10% of all people have breast cancer. we have 2 genders (50:50). if i select all the men with breast cancer, i will get basically nobody - the planner will overestimate the output. this is the

Re: [HACKERS] cross column correlation revisted

On Jul 14, 2010, at 12:40 PM, Heikki Linnakangas wrote: On 14/07/10 13:12, PostgreSQL - Hans-Jürgen Schönig wrote: hello everybody, we are currently facing some serious issues with cross correlation issue. consider: 10% of all people have breast cancer. we have 2 genders (50:50). if i

Re: [HACKERS] cross column correlation revisted

. many thanks, hans On Jul 14, 2010, at 4:01 PM, Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 14/07/10 13:12, PostgreSQL - Hans-Jürgen Schönig wrote: maybe somehow like this ... ALTER TABLE x SET CORRELATION STATISTICS FOR (id = id2

Re: [HACKERS] cross column correlation revisted

hello ... look at the syntax i posted in more detail: ALTER TABLE x SET CORRELATION STATISTICS FOR (x.id = y.id AND x.id2 = y.id2) it says X and Y ... the selectivity of joins are what i am most interested in. cross correlation of columns within the same table are just a byproduct.

Re: [HACKERS] non-overlapping, consecutive partitions

On Jul 25, 2010, at 11:56 AM, Martijn van Oosterhout wrote: On Fri, Jul 23, 2010 at 10:04:00PM +0200, Hans-Jürgen Schönig wrote: create table foo ( x date ); create table foo_2010 () INHERITS (foo) create table foo_2009 () INHERITS (foo) create table foo_2008 () INHERITS

Re: [HACKERS] micro bucket sort ...

as tom pointed out - this is not possible. there is no limit 20 in my case - i just used it to indicate that limiting does not make the index scan possible which it does in some other cases. the partial sort thing simon pointed out is what is needed at this point. many thanks,

Re: [HACKERS] How to construct an exact plan

hello ... here is the ultimate revelation of planner hints in postgres ... let us praise oleg and teodor for solving a practical problem for practical people ... http://www.sai.msu.su/~megera/wiki/plantuner try this one ... it is excellent and definitely helpful for many many people out there.

Re: [HACKERS] Path question

On Sep 1, 2010, at 4:10 PM, Tom Lane wrote: Boszormenyi Zoltan z...@cybertec.at writes: we are experimenting with modifying table partitioning so the ORDER BY clause can be pushed down to child nodes on the grounds that: This is really premature, and anything you do along those lines now

Re: [HACKERS] Path question

hello tom, yeah, we have followed quite a lot of discussion as well ... and yes, no patches. as far as this problem is concerned: we are working on a patch and did some prototyping inside the planner already (attached). the code we have is pretty limited atm (such as checking for a sort clause

[HACKERS] plan time of MASSIVE partitioning ...

hello everybody, we came across an issue which turned out to be more serious than previously expected. imagine a system with, say, 1000 partitions (heavily indexed) or so. the time taken by the planner is already fairly heavy in this case. i tried this one with 5000 unindexed tables (just one

Re: [HACKERS] plan time of MASSIVE partitioning ...

On Sep 3, 2010, at 2:04 PM, Stephen Frost wrote: * PostgreSQL - Hans-Jürgen Schönig (postg...@cybertec.at) wrote: did anybody think of a solution to this problem. or more precisely: can there be a solution to this problem? Please post to the correct list (-performance) and provide

Re: [HACKERS] plan time of MASSIVE partitioning ...

On Sep 3, 2010, at 4:40 PM, Tom Lane wrote: =?iso-8859-1?Q?PostgreSQL_-_Hans-J=FCrgen_Sch=F6nig?= postg...@cybertec.at writes: imagine a system with, say, 1000 partitions (heavily indexed) or so. the time taken by the planner is already fairly heavy in this case. As the fine manual

[HACKERS] SELECT ... WHERE fti_query ORDER BY numeric_col LIMIT x - problem

I have toyed around with KNN a little and I am pretty impressed when it comes to the results we have seen in the GIS world. Given the infrastructure we have at the moment I wonder if KNN can help to speedup queries like that: SELECT ... WHERE fti_query ORDER BY numeric_col LIMIT x The use case

Re: [HACKERS] Getting rid of cheap-startup-cost paths earlier

On May 22, 2012, at 9:57 AM, Simon Riggs wrote: On 22 May 2012 06:50, Tom Lane t...@sss.pgh.pa.us wrote: Currently, the planner keeps paths that appear to win on the grounds of either cheapest startup cost or cheapest total cost. It suddenly struck me that in many simple cases (viz, those

Re: [HACKERS] Combine non-recursive and recursive CTEs?

On Jun 16, 2012, at 8:27 AM, Magnus Hagander wrote: I'm not sure if this is something I don't know how to do, or if it's something we simply can't do, or if it's something we could do but the syntax can't handle :-) Basically, I'd like to combine a recursive and a non-recursive CTE in the

Re: [HACKERS] bad posix_fadvise support causes initdb to exit ungracefully

hello ... 2.4? we know that some versions of 2.4 cause problems due to broken posix_fadvise. if i remember correctly we built some configure magic into PostgreSQL to check for this bug. what does this check do? many thanks, hans On Jun 15, 2011, at 6:12 PM, Merlin

Re: [HACKERS] SYNONYMS (again)

On Jun 23, 2011, at 12:52 AM, Alvaro Herrera wrote: Excerpts from Joshua D. Drake's message of mié jun 22 15:37:17 -0400 2011: Per: http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php It seems we did come up with a use case in the procpid discussion. The ability to change

Re: [HACKERS] SYNONYMS (again)

On Jun 23, 2011, at 12:52 AM, Alvaro Herrera wrote: Excerpts from Joshua D. Drake's message of mié jun 22 15:37:17 -0400 2011: Per: http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php It seems we did come up with a use case in the procpid discussion. The ability to change

Re: [HACKERS] Will switchover still need a checkpoint in 9.1 SR Hot Standby

On Aug 7, 2011, at 11:01 AM, Simon Riggs wrote: On Sun, Aug 7, 2011 at 8:57 AM, Hannu Krosing ha...@2ndquadrant.com wrote: In 9.0 (as in earlier versions) a former standby host has to do a full checkpoint before becoming available as an independent database instance in either switchover or

Re: [HACKERS] index-only scans

On Aug 12, 2011, at 10:03 PM, Heikki Linnakangas wrote: On 11.08.2011 23:06, Robert Haas wrote: Comments, testing, review appreciated... I would've expected this to use an index-only scan: postgres=# CREATE TABLE foo AS SELECT generate_series(1,10) AS id; SELECT 10 postgres=#

Re: [HACKERS] synchronized snapshots

On Aug 15, 2011, at 9:40 AM, Simon Riggs wrote: On Mon, Aug 15, 2011 at 2:31 AM, Joachim Wieland j...@mcknight.de wrote: In short, this is how it works: SELECT pg_export_snapshot(); pg_export_snapshot 03A1-1 (1 row) (and then in a different session)

[HACKERS] Caching Python modules

hello … i have just fallen over a nasty problem (maybe missing feature) with PL/Pythonu … consider: -- add a document to the corpus CREATE OR REPLACE FUNCTION textprocess.add_to_corpus(lang text, t text) RETURNS float4 AS $$ from SecondCorpus import SecondCorpus from

Re: [HACKERS] Caching Python modules

On Aug 17, 2011, at 2:19 PM, Jan Urbański wrote: On 17/08/11 14:09, PostgreSQL - Hans-Jürgen Schönig wrote: CREATE OR REPLACE FUNCTION textprocess.add_to_corpus(lang text, t text) RETURNS float4 AS $$ from SecondCorpus import SecondCorpus from SecondDocument import

Re: [HACKERS] help with plug-in function for additional (partition/shard) visibility checks

hello … i have been thinking about this issue for quite a while ... given your idea i am not sure how this can work at all. consider: begin; insert 1 insert 2 commit assume this ends up in the same node, now you split it into two … 1 and 2 will have exactly the

Re: [HACKERS] help with plug-in function for additional (partition/shard) visibility checks

- and: this what would be under your full control. what do you think? i got to think about it futher but i can envision that this could be feasible ... hans On Sep 2, 2011, at 2:36 PM, Hannu Krosing wrote: On Fri, 2011-09-02 at 14:01 +0200, PostgreSQL - Hans-Jürgen Schönig wrote: hello … i

Re: [HACKERS] help with plug-in function for additional (partition/shard) visibility checks

On Sep 2, 2011, at 2:59 PM, Hannu Krosing wrote: On Fri, 2011-09-02 at 14:51 +0200, PostgreSQL - Hans-Jürgen Schönig wrote: hello … the goal of the entire proxy thing is to make the right query go to the right node / nodes. we determine this by using a partitioning function and so

Re: [HACKERS] CUDA Sorting

On Sep 19, 2011, at 5:16 PM, Tom Lane wrote: Greg Stark st...@mit.edu writes: That said, to help in the case I described you would have to implement the tapesort algorithm on the GPU as well. I think the real problem would be that we are seldom sorting just the key values. If you have to

Re: [HACKERS] What is known about PostgreSQL HP-UX support?

On Oct 10, 2011, at 4:21 AM, Alex Goncharov wrote: [ Thanks all for the very productive discussion in the thread libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable which I originated. Very useful. Now on something different. ] About two years ago, I had to research some

Re: [HACKERS] index-only scans

On Oct 7, 2011, at 8:47 PM, Joshua D. Drake wrote: On 10/07/2011 11:40 AM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: Please find attached a patch implementing a basic version of index-only scans. I'm making some progress with this, but I notice what seems like a missing

[HACKERS] WIP: cross column correlation ...

hello everbody, we have spent some time in finally attacking cross column correlation. as this is an issue which keeps bugging us for a couple of applications (some years). this is a WIP patch which can do: special cross column correlation specific syntax:

Re: [HACKERS] WIP: cross column correlation ...

On Feb 23, 2011, at 2:58 AM, Robert Haas wrote: 2011/2/22 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at: how does it work? we try to find suitable statistics for an arbitrary length list of conditions so that the planner can use it directly rather than multiplying all

Re: [HACKERS] WIP: cross column correlation ...

Those are real problems, but I still want it. The last time I hit this problem I spent two days redesigning my schema and adding triggers all over the place to make things work. If I had been dealing with a 30TB database instead of a 300MB database I would have been royally up a creek.

Re: [HACKERS] WIP: cross column correlation ...

cheapest and easiest solution if you run into this: add fake functions which the planner cannot estimate properly. use OR to artificially prop up estimates or use AND to artificially lower them. there is actually no need to redesign the schema to get around it but it is such an ugly

Re: [HACKERS] WIP: cross column correlation ...

On Feb 23, 2011, at 3:46 PM, Robert Haas wrote: On Wed, Feb 23, 2011 at 12:50 AM, Nathan Boley npbo...@gmail.com wrote: Personally, I think the first thing we ought to do is add a real, bona fide planner hint to override the selectivity calculation manually, maybe something like this:

Re: [HACKERS] WIP: cross column correlation ...

On Feb 24, 2011, at 2:09 AM, Josh Berkus wrote: Personally, I think the first thing we ought to do is add a real, bona fide planner hint to override the selectivity calculation manually, maybe something like this: WHERE (x 5 AND y = 1) SELECTIVITY (0.1); Then, having provided a method

Re: [HACKERS] WIP: cross column correlation ...

Still, having more data a user can probe would be nice. I wonder why everyone avoids Microsoft's approach to the subject. Apparently, they go in the 'auto-tune as much as possible' direction. And tests we did a while ago, involving asking team from Microsoft and a team from oracle to

Re: [HACKERS] k-neighbourhood search in databases

hello ... i have put some research into that some time ago and as far as i have seen there is a 99% chance that no other database can do it the way we do it. it seems nobody comes even close to it (especially not in the flexibility-arena). oracle: disgusting workaround ...

[HACKERS] workaround for expensive KNN?

hello all ... given oleg's posting before i also wanted to fire up some KNN related question. let us consider a simple example. i got some million lines and i want all rows matching a tsquery sorted by price. i did some tests: test=# explain (analyze true, buffers true, costs true) SELECT id

Re: [HACKERS] workaround for expensive KNN?

hello ... i got that one ... idx_product_t_product_titleprice gist (to_tsvector('german'::regconfig, title), int_price) so, i have a combined index on text + number. to me the plan seems fine ... it looks like a prober KNN traversal. the difference between my plan and your plan seems to be

Re: [HACKERS] workaround for expensive KNN?

price has a problem :(. iphone can be a 20 cents bag or a sticker or a 900 euro thing signed by whoever ... so, words and the sort-number / price are not related in anyway. price is in this case no way to narrow down the problem (e.g. evaluate first or so). many thanks,

Re: [HACKERS] Backup throttling

On Aug 19, 2013, at 9:11 PM, Andres Freund wrote: On 2013-08-19 20:15:51 +0200, Boszormenyi Zoltan wrote: 2013-08-19 19:20 keltezéssel, Andres Freund írta: Hi, On 2013-07-24 09:20:52 +0200, Antonin Houska wrote: Hello, the purpose of this patch is to limit impact of pg_backup on running

Re: [HACKERS] Backup throttling

On Aug 21, 2013, at 10:57 AM, Andres Freund wrote: On 2013-08-21 08:10:42 +0200, PostgreSQL - Hans-Jürgen Schönig wrote: On Aug 19, 2013, at 9:11 PM, Andres Freund wrote: On 2013-08-19 20:15:51 +0200, Boszormenyi Zoltan wrote: 2013-08-19 19:20 keltezéssel, Andres Freund írta: Hi

Re: [HACKERS] Small GIN optimizations (after 9.4)

i think there is one more thing which would be really good in GIN and which would solve a ton of issues. atm GIN entries are sorted by item pointer. if we could sort them by a column it would fix a couple of real work issues such as ... SELECT ... FROM foo WHERE tsearch_query ORDER BY

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 18 Aug 2015, at 11:19, Albe Laurenz laurenz.a...@wien.gv.at wrote: Hans-Jürgen Schönig wrote: in addition to that you have the “problem” of transactions. if you failover in the middle of a transaction, strange things might happen from the application point of view. the good thing,

Re: [HACKERS] Proposal: Implement failover on libpq connect level.

On 18 Aug 2015, at 10:32, Albe Laurenz laurenz.a...@wien.gv.at wrote: Victor Wagner wrote: Rationale = Since introduction of the WAL-based replication into the PostgreSQL, it is possible to create high-availability and load-balancing clusters. However, there is no support for

Re: [HACKERS] WIP: Data at rest encryption

On 06/14/2016 09:59 PM, Jim Nasby wrote: On 6/12/16 2:13 AM, Ants Aasma wrote: On Fri, Jun 10, 2016 at 5:23 AM, Haribabu Kommi wrote: > 1. Instead of doing the entire database files encryption, how about > providing user an option to protect only some particular

Re: [HACKERS] remove checkpoint_warning

On 07/09/2016 11:12 PM, Tom Lane wrote: Alvaro Herrera writes: the checkpoint_warning feature was added by commit 2986aa6a668bce3cfb836 in November 2002 when we didn't have any logging of checkpointing at all. I propose to remove it: surely anyone who cares about

Re: [HACKERS] FDW and parallel execution

hello ... did you check out antonin houska's patches? we basically got code, which can do that. many thanks, hans On 04/02/2017 03:30 PM, Konstantin Knizhnik wrote: > Hi hackers and personally Robet (you are the best expert in both areas). > I want to ask one more question