Re: [HACKERS] Index-only scans with btree_gist

2015-03-30 Thread Heikki Linnakangas
On 03/29/2015 04:30 AM, Andreas Karlsson wrote: On 03/29/2015 03:25 AM, Andreas Karlsson wrote: On 03/28/2015 09:36 PM, Andreas Karlsson wrote: Thanks! Do you know if it is possible to add index-only scan support to range indexes? I have never looked at those and do not know if they are lossy.

Re: [HACKERS] Index-only scans with btree_gist

2015-03-28 Thread Andreas Karlsson
On 03/28/2015 09:36 PM, Andreas Karlsson wrote: Thanks! Do you know if it is possible to add index-only scan support to range indexes? I have never looked at those and do not know if they are lossy. Seems like range types are not compressed at all so implementing index-only scans was trivial.

Re: [HACKERS] Index-only scans with btree_gist

2015-03-28 Thread Andreas Karlsson
On 03/29/2015 03:25 AM, Andreas Karlsson wrote: On 03/28/2015 09:36 PM, Andreas Karlsson wrote: Thanks! Do you know if it is possible to add index-only scan support to range indexes? I have never looked at those and do not know if they are lossy. Seems like range types are not compressed at

Re: [HACKERS] Index-only scans with btree_gist

2015-03-28 Thread Andreas Karlsson
On 03/28/2015 02:12 PM, Heikki Linnakangas wrote: Looks good to me. Committed, thanks. Thanks! Do you know if it is possible to add index-only scan support to range indexes? I have never looked at those and do not know if they are lossy. Andreas -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Index-only scans with btree_gist

2015-03-28 Thread Heikki Linnakangas
On 03/28/2015 01:14 AM, Andreas Karlsson wrote: On 03/26/2015 10:31 PM, Heikki Linnakangas wrote: I've pushed Anastasia's patch to support index-only scans with GiST, and it's time to add opclasses support for all the opclasses that are not lossy. I think at least all the btree_gist opclasses

Re: [HACKERS] Index-only scans with btree_gist

2015-03-27 Thread Andreas Karlsson
On 03/26/2015 10:31 PM, Heikki Linnakangas wrote: I've pushed Anastasia's patch to support index-only scans with GiST, and it's time to add opclasses support for all the opclasses that are not lossy. I think at least all the btree_gist opclasses need to be supported, it would be pretty

Re: [HACKERS] Index-only scans for GiST.

2015-03-26 Thread Heikki Linnakangas
On 03/02/2015 12:43 AM, Heikki Linnakangas wrote: On 02/27/2015 04:19 PM, Anastasia Lubennikova wrote: I add MemoryContext listCxt to avoid memory leak. listCxt is created once in gistrescan (only for index-only scan plan ) and reseted when scan of the leaf page is finished. I do not sure if

[HACKERS] Index-only scans with btree_gist

2015-03-26 Thread Heikki Linnakangas
I've pushed Anastasia's patch to support index-only scans with GiST, and it's time to add opclasses support for all the opclasses that are not lossy. I think at least all the btree_gist opclasses need to be supported, it would be pretty surprising if they didn't support index-only scans, while

Re: [HACKERS] Index-only scans for GiST.

2015-03-01 Thread Heikki Linnakangas
On 02/27/2015 04:19 PM, Anastasia Lubennikova wrote: I add MemoryContext listCxt to avoid memory leak. listCxt is created once in gistrescan (only for index-only scan plan ) and reseted when scan of the leaf page is finished. I do not sure if the problem was completely solved, so I wait for

Re: [HACKERS] Index-only scans for GiST.

2015-02-27 Thread Anastasia Lubennikova
I add MemoryContext listCxt to avoid memory leak. listCxt is created once in gistrescan (only for index-only scan plan ) and reseted when scan of the leaf page is finished. I do not sure if the problem was completely solved, so I wait for feedback. * What's the reason for turning

Re: [HACKERS] Index-only scans for GiST.

2015-02-12 Thread Anastasia Lubennikova
Thanks for answer. Now it seems to be applied correctly. 2015-02-12 3:12 GMT+04:00 Thom Brown t...@linux.com: On 11 February 2015 at 22:50, Anastasia Lubennikova lubennikov...@gmail.com wrote: Finally there is a new version of patch (in attachments). It provides multicolumn index-only scan

Re: [HACKERS] Index-only scans for GiST.

2015-02-12 Thread Thom Brown
On 12 February 2015 at 10:40, Anastasia Lubennikova lubennikov...@gmail.com wrote: Thanks for answer. Now it seems to be applied correctly. (please avoid top-posting) Thanks for the updated patch. I can confirm that it now cleanly applies and compiles fine. I've run the tested in the SQL

Re: [HACKERS] Index-only scans for GiST.

2015-02-12 Thread Thom Brown
On 12 February 2015 at 16:40, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 02/12/2015 12:40 PM, Anastasia Lubennikova wrote: Thanks for answer. Now it seems to be applied correctly. * Documentation is missing. Anastasia provided a documentation patch in the first email. Thom

Re: [HACKERS] Index-only scans for GiST.

2015-02-12 Thread Fabrízio de Royes Mello
On Thu, Feb 12, 2015 at 3:07 PM, Thom Brown t...@linux.com wrote: On 12 February 2015 at 16:40, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 02/12/2015 12:40 PM, Anastasia Lubennikova wrote: Thanks for answer. Now it seems to be applied correctly. * Documentation is missing.

Re: [HACKERS] Index-only scans for GiST.

2015-02-12 Thread Heikki Linnakangas
On 02/12/2015 12:40 PM, Anastasia Lubennikova wrote: Thanks for answer. Now it seems to be applied correctly. Thanks, it would be great to get this completed. This still leaks memory with the same test query as earlier. The leak seems to be into a different memory context now; it used to be

Re: [HACKERS] Index-only scans for GiST.

2015-02-11 Thread Thom Brown
On 11 February 2015 at 22:50, Anastasia Lubennikova lubennikov...@gmail.com wrote: Finally there is a new version of patch (in attachments). It provides multicolumn index-only scan for GiST indexes. - Memory leak is fixed. - little code cleanup - example of performance test in attachmens

[HACKERS] Index-only scans for GiST.

2015-02-11 Thread Anastasia Lubennikova
Finally there is a new version of patch (in attachments). It provides multicolumn index-only scan for GiST indexes. - Memory leak is fixed. - little code cleanup - example of performance test in attachmens - function OIDs have debugging values (*) just to avoid merge conflicts while testing

Re: [HACKERS] Index-only scans for GIST

2014-10-27 Thread Thom Brown
On 18 August 2014 09:05, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 08/17/2014 07:15 PM, Anastasia Lubennikova wrote: 2014-08-07 0:30 GMT+04:00 Heikki Linnakangas hlinnakan...@vmware.com: * I'm getting two regression failures with this (opr_sanity and join). opr_sanity failure

Re: [HACKERS] Index-only scans for GIST

2014-08-18 Thread Anastasia Lubennikova
Updated patch * Compiler, merge and regression fails checked * Regression tests was impoved * GiST and amcanreturn docs updated -- Best regards, Lubennikova Anastasia indexonlyscan_gist2.patch Description: Binary data indexonlyscan_gist_docs.patch Description: Binary data -- Sent via

Re: [HACKERS] Index-only scans for GIST

2014-08-18 Thread Heikki Linnakangas
On 08/17/2014 07:15 PM, Anastasia Lubennikova wrote: 2014-08-07 0:30 GMT+04:00 Heikki Linnakangas hlinnakan...@vmware.com: * I'm getting two regression failures with this (opr_sanity and join). opr_sanity failure is corrected. But there is remain question with join. I check the latest

Re: [HACKERS] Index-only scans for GIST

2014-08-17 Thread Anastasia Lubennikova
2014-08-07 0:30 GMT+04:00 Heikki Linnakangas hlinnakan...@vmware.com: * I'm getting two regression failures with this (opr_sanity and join). opr_sanity failure is corrected. But there is remain question with join. I check the latest version of my github repo and there's no fail in join

Re: [HACKERS] Index-only scans for GIST

2014-08-06 Thread Heikki Linnakangas
On 08/01/2014 10:58 AM, Anastasia Lubennikova wrote: Hi, hackers! I work on a GSoC project Index-only scans for GIST https://wiki.postgresql.org/wiki/Support_for_Index-only_scans_for_GIST_GSoC_2014 Repository is https://github.com/lubennikovaav/postgres/tree/indexonlygist2 Patch is in

[HACKERS] Index-only scans for GIST

2014-08-01 Thread Anastasia Lubennikova
Hi, hackers! I work on a GSoC project Index-only scans for GIST https://wiki.postgresql.org/wiki/Support_for_Index-only_scans_for_GIST_GSoC_2014 Repository is https://github.com/lubennikovaav/postgres/tree/indexonlygist2 Patch is in attachments. It includes index-only scans for multicolumn GIST

Re: [HACKERS] Index-only scans for GIST

2014-08-01 Thread Fabrízio de Royes Mello
On Fri, Aug 1, 2014 at 4:58 AM, Anastasia Lubennikova lubennikov...@gmail.com wrote: Hi, hackers! I work on a GSoC project Index-only scans for GIST https://wiki.postgresql.org/wiki/Support_for_Index-only_scans_for_GIST_GSoC_2014 Repository is

Re: [HACKERS] Index-only scans for GIST

2014-08-01 Thread Anastasia Lubennikova
Thank you for comment Patch is already added in Performance topic. 2014-08-01 20:25 GMT+04:00 Fabrízio de Royes Mello fabriziome...@gmail.com : On Fri, Aug 1, 2014 at 4:58 AM, Anastasia Lubennikova lubennikov...@gmail.com wrote: Hi, hackers! I work on a GSoC project Index-only scans

Re: [HACKERS] Index-only scans for multicolumn GIST

2014-07-23 Thread Emre Hasegeli
That seems like a nonstarter :-(. Index-only scans don't have a license to return approximations. If we drop the behavior for circles, how much functionality do we have left? It should work with exact operator classes, box_ops, point_ops, range_ops, inet_ops. -- Sent via pgsql-hackers

Re: [HACKERS] Index-only scans for multicolumn GIST

2014-07-22 Thread Heikki Linnakangas
On 07/21/2014 10:47 PM, Anastasia Lubennikova wrote: Hi, hackers! There are new results of my work on GSoC project Index-only scans for GIST. Previous post is here: http://postgresql.1045698.n5.nabble.com/Index-only-scans-for-GIST-td5804892.html Repository is

Re: [HACKERS] Index-only scans for multicolumn GIST

2014-07-22 Thread Robert Haas
On Tue, Jul 22, 2014 at 2:55 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 07/21/2014 10:47 PM, Anastasia Lubennikova wrote: Hi, hackers! There are new results of my work on GSoC project Index-only scans for GIST. Previous post is here:

Re: [HACKERS] Index-only scans for multicolumn GIST

2014-07-22 Thread Tom Lane
Heikki Linnakangas hlinnakan...@vmware.com writes: For a circle, the GiST index stores a bounding box of the circle. The new fetch function reverses that, calculating the radius and center of the circle from the bounding box. Those conversions lose some precision due to rounding. Are we

[HACKERS] Index-only scans for multicolumn GIST

2014-07-21 Thread Anastasia Lubennikova
Hi, hackers! There are new results of my work on GSoC project Index-only scans for GIST. Previous post is here: http://postgresql.1045698.n5.nabble.com/Index-only-scans-for-GIST-td5804892.html Repository is https://github.com/lubennikovaav/postgres/tree/indexonlygist2 Patch is in attachments. It

Re: [HACKERS] Index-only scans and non-MVCC snapshots

2014-06-27 Thread Andres Freund
On 2014-06-26 22:47:47 -0600, Ryan Johnson wrote: Hi, As part of a research project, I'm trying to change Read Committed isolation to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every command [1]. Things appear to have gone reasonably well so far, except certain

Re: [HACKERS] Index-only scans and non-MVCC snapshots

2014-06-27 Thread Alvaro Herrera
Ryan Johnson wrote: On 26/06/2014 11:04 PM, Alvaro Herrera wrote: Ryan Johnson wrote: As part of a research project, I'm trying to change Read Committed isolation to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every command [1]. Are you aware of this? commit

Re: [HACKERS] Index-only scans and non-MVCC snapshots

2014-06-27 Thread Ryan Johnson
On 27/06/2014 3:14 AM, Andres Freund wrote: On 2014-06-26 22:47:47 -0600, Ryan Johnson wrote: Hi, As part of a research project, I'm trying to change Read Committed isolation to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every command [1]. Things appear to have gone

Re: [HACKERS] Index-only scans and non-MVCC snapshots

2014-06-27 Thread Andres Freund
On 2014-06-27 08:39:13 -0600, Ryan Johnson wrote: On 27/06/2014 3:14 AM, Andres Freund wrote: On 2014-06-26 22:47:47 -0600, Ryan Johnson wrote: Hi, As part of a research project, I'm trying to change Read Committed isolation to use HeapTupleSatisfiesNow rather than acquiring a new snapshot

Re: [HACKERS] Index-only scans and non-MVCC snapshots

2014-06-27 Thread Ryan Johnson
On 27/06/2014 8:20 AM, Alvaro Herrera wrote: Ryan Johnson wrote: On 26/06/2014 11:04 PM, Alvaro Herrera wrote: Ryan Johnson wrote: As part of a research project, I'm trying to change Read Committed isolation to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every command

[HACKERS] Index-only scans and non-MVCC snapshots

2014-06-26 Thread Ryan Johnson
Hi, As part of a research project, I'm trying to change Read Committed isolation to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every command [1]. Things appear to have gone reasonably well so far, except certain queries fail with ERROR: non-MVCC snapshots are not

Re: [HACKERS] Index-only scans and non-MVCC snapshots

2014-06-26 Thread Alvaro Herrera
Ryan Johnson wrote: As part of a research project, I'm trying to change Read Committed isolation to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every command [1]. Are you aware of this? commit 813fb0315587d32e3b77af1051a0ef517d187763 Author: Robert Haas

Re: [HACKERS] Index-only scans and non-MVCC snapshots

2014-06-26 Thread Ryan Johnson
On 26/06/2014 11:04 PM, Alvaro Herrera wrote: Ryan Johnson wrote: As part of a research project, I'm trying to change Read Committed isolation to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every command [1]. Are you aware of this? commit

Re: [HACKERS] Index-only scans for GIST

2014-05-29 Thread Robert Haas
On Sun, May 25, 2014 at 6:12 AM, Anastasia Lubennikova lubennikov...@gmail.com wrote: Hi, hackers! There are first results of my work on GSoC project Index-only scans for GIST. Cool. 1. Version of my project code is in forked repository

[HACKERS] Index-only scans for GIST

2014-05-25 Thread Anastasia Lubennikova
Hi, hackers! There are first results of my work on GSoC project Index-only scans for GIST. 1. Version of my project code is in forked repository https://github.com/lubennikovaav/postgres/tree/indexonlygist2 Patch is in attachments - This version is only for one-column indexes - fetch() method is

Re: [HACKERS] Index only scans wiki page

2012-11-15 Thread Peter Geoghegan
On 13 November 2012 01:25, Peter Geoghegan pe...@2ndquadrant.com wrote: Attached is a piece I wrote on the feature. That might form the basis of a new wiki page. Since no one else moved on this, I've completely replaced the existing page with the contents of the user-orientated document I

Re: [HACKERS] Index only scans wiki page

2012-11-13 Thread Robert Haas
On Mon, Nov 12, 2012 at 8:25 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 13 November 2012 01:03, Jeff Janes jeff.ja...@gmail.com wrote: https://wiki.postgresql.org/wiki/Index-only_scans This page is seriously out of date. It suggests they are not yet implemented, but only being

Re: [HACKERS] Index only scans wiki page

2012-11-13 Thread Peter Geoghegan
On 13 November 2012 16:37, Robert Haas robertmh...@gmail.com wrote: I found this an interesting read. As one of the people who worked on the feature, I'm sort of curious whether people have any experience yet with how this actually shakes out in the field. Are you (or is anyone) aware of

Re: [HACKERS] Index only scans wiki page

2012-11-13 Thread Peter Geoghegan
On 13 November 2012 16:37, Robert Haas robertmh...@gmail.com wrote: I found this an interesting read. As one of the people who worked on the feature, I'm sort of curious whether people have any experience yet with how this actually shakes out in the field. Are you (or is anyone) aware of

[HACKERS] Index only scans wiki page

2012-11-12 Thread Jeff Janes
https://wiki.postgresql.org/wiki/Index-only_scans This page is seriously out of date. It suggests they are not yet implemented, but only being talked about. Would someone who knows a lot about the subject (which is why I'm sending this hackers, not web) like to take a whack at updating this?

Re: [HACKERS] Index only scans wiki page

2012-11-12 Thread Peter Geoghegan
On 13 November 2012 01:03, Jeff Janes jeff.ja...@gmail.com wrote: https://wiki.postgresql.org/wiki/Index-only_scans This page is seriously out of date. It suggests they are not yet implemented, but only being talked about. Attached is a piece I wrote on the feature. That might form the basis

Re: [HACKERS] index-only scans versus serializable transactions

2012-09-04 Thread Kevin Grittner
Tom Lane wrote: Kevin Grittner writes: By not visiting the heap page for tuples, index-only scans fail to acquire all of the necessary predicate locks for correct behavior at the serializable transaction isolation level. The tag for the tuple-level predicate locks includes the xmin, to

Re: [HACKERS] index-only scans versus serializable transactions

2012-09-04 Thread Kevin Grittner
Kevin Grittner wrote: New version attached. Will apply if no further problems are found. Pushed to master and REL9_2_STABLE. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

[HACKERS] index-only scans versus serializable transactions

2012-09-03 Thread Kevin Grittner
By not visiting the heap page for tuples, index-only scans fail to acquire all of the necessary predicate locks for correct behavior at the serializable transaction isolation level. The tag for the tuple-level predicate locks includes the xmin, to avoid possible problems with tid re-use. (This

Re: [HACKERS] index-only scans versus serializable transactions

2012-09-03 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: By not visiting the heap page for tuples, index-only scans fail to acquire all of the necessary predicate locks for correct behavior at the serializable transaction isolation level. The tag for the tuple-level predicate locks includes the

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-05-04 Thread Simon Riggs
On 2 May 2012 13:41, Robert Haas robertmh...@gmail.com wrote: So on further reflection I'm thinking it may be best just to stick with a hard conflict for now and see what feedback we get from beta testers. Which is what I was expecting y'all to conclude once you'd looked at the task in more

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-05-02 Thread Robert Haas
On Thu, Apr 26, 2012 at 8:03 PM, Robert Haas robertmh...@gmail.com wrote: So, as a first step, I've committed a patch that just throws a hard conflict.  I think we probably want to optimize this further, and I'm going to work investigate that next.  But it seemed productive to get this much

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-26 Thread Robert Haas
On Mon, Apr 16, 2012 at 4:13 PM, Robert Haas robertmh...@gmail.com wrote: But fundamentally we all seem to be converging on some variant of the soft conflict idea. So, as a first step, I've committed a patch that just throws a hard conflict. I think we probably want to optimize this further,

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-16 Thread Noah Misch
On Fri, Apr 13, 2012 at 12:33:06PM -0400, Robert Haas wrote: In the department of query cancellations, I believe Noah argued previously that this wasn't really going to cause a problem. And, indeed, if the master has a mix of inserts, updates, and deletes, then it seems likely that any

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-16 Thread Simon Riggs
On Mon, Apr 16, 2012 at 8:02 AM, Noah Misch n...@leadboat.com wrote: On Fri, Apr 13, 2012 at 12:33:06PM -0400, Robert Haas wrote: In the department of query cancellations, I believe Noah argued previously that this wasn't really going to cause a problem.  And, indeed, if the master has a mix

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-16 Thread Heikki Linnakangas
On 16.04.2012 10:38, Simon Riggs wrote: On Mon, Apr 16, 2012 at 8:02 AM, Noah Mischn...@leadboat.com wrote: On Fri, Apr 13, 2012 at 12:33:06PM -0400, Robert Haas wrote: In the department of query cancellations, I believe Noah argued previously that this wasn't really going to cause a problem.

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-16 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Can we have a soft hot standby conflict that doesn't kill the query, but disables index-only-scans? Well, there wouldn't be any way for the planner to know whether an index-only scan would be safe or not. I think this would have

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-16 Thread Simon Riggs
On Mon, Apr 16, 2012 at 3:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Can we have a soft hot standby conflict that doesn't kill the query, but disables index-only-scans? Well, there wouldn't be any way for the planner to know

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-16 Thread Robert Haas
On Mon, Apr 16, 2012 at 4:26 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Can we have a soft hot standby conflict that doesn't kill the query, but disables index-only-scans? Yeah, something like that seems possible. For example, suppose the master includes, in each

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-16 Thread Robert Haas
On Mon, Apr 16, 2012 at 3:02 AM, Noah Misch n...@leadboat.com wrote: Do you refer to PD_ALL_VISIBLE as not merely a hint due to the requirement to prevent a page from simultaneously having a negative PD_ALL_VISIBLE and a positive visibility map bit?  That is to say, PD_ALL_VISIBLE is fully a

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-16 Thread Robert Haas
On Mon, Apr 16, 2012 at 1:58 PM, Simon Riggs si...@2ndquadrant.com wrote: If we do need to do something, then introduce concept of a visibility conflict. On replay: If feedback not set, set LSN of visibility conflict on PROCs that conflict, if not already set. On query: If feedback not

Re: [HACKERS] index-only scans vs. Hot Standby, round two

2012-04-15 Thread Simon Riggs
On Fri, Apr 13, 2012 at 5:33 PM, Robert Haas robertmh...@gmail.com wrote: Currently, we have a problem with index-only scans in Hot Standby mode: the xmin horizon on the standby might lag the master, and thus an index-only scan might mistakenly conclude that no heap fetch is needed when in

[HACKERS] index-only scans vs. Hot Standby, round two

2012-04-13 Thread Robert Haas
Currently, we have a problem with index-only scans in Hot Standby mode: the xmin horizon on the standby might lag the master, and thus an index-only scan might mistakenly conclude that no heap fetch is needed when in fact it is. I suggested that we handle this by suppressing generation of

[HACKERS] Index only scans and visibilitymap.c

2011-10-23 Thread Jeff Janes
With index only scans, the comments in src/backend/access/heap/visibilitymap.c are probably out of date, starting with: Currently, the visibility map is only used as a hint Also, is there a discussion of how and why index-only scans is safe? i.e. what lock, if any, has to be held while

Re: [HACKERS] index-only scans

2011-10-15 Thread Jeff Janes
On Fri, Oct 7, 2011 at 11:40 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@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 feature: there needs to be a

Re: [HACKERS] index-only scans

2011-10-15 Thread Jeff Janes
On Sat, Oct 15, 2011 at 2:16 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Fri, Oct 7, 2011 at 11:40 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Please find attached a patch implementing a basic version of index-only scans. I'm making some progress with

Re: [HACKERS] index-only scans

2011-10-12 Thread Tom Lane
I wrote: I was also toying with the notion of pushing the slot fill-in into the AM, so that the AM API is to return a filled TupleSlot not an IndexTuple. This would not save any cycles AFAICT --- at least in btree, we still have to make a palloc'd copy of the IndexTuple so that we can

Re: [HACKERS] index-only scans

2011-10-11 Thread Robert Haas
On Tue, Oct 11, 2011 at 12:19 AM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: I have mostly-working code for approach #3, but I haven't tried to make EXPLAIN work yet.  While looking at that I realized that there's a pretty good argument for adding the above-mentioned explicit TargetEntry

Re: [HACKERS] index-only scans

2011-10-11 Thread Alexander Korotkov
On Tue, Oct 11, 2011 at 2:36 PM, Robert Haas robertmh...@gmail.com wrote: Have you given any thought to what would be required to support index-only scans on non-btree indexes - particularly, GIST? ISTM we might have had a thought that some GIST opclasses but not others would be able to

Re: [HACKERS] index-only scans

2011-10-11 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Have you given any thought to what would be required to support index-only scans on non-btree indexes - particularly, GIST? ISTM we might have had a thought that some GIST opclasses but not others would be able to regurgitate tuples, or maybe even

Re: [HACKERS] index-only scans

2011-10-11 Thread Alexander Korotkov
On Tue, Oct 11, 2011 at 5:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: I haven't thought as far ahead as how we might get the information needed for a per-opclass flag. A syntax addition to CREATE OPERATOR CLASS might be the only way. Shouldn't it be implemented through additional interface

Re: [HACKERS] index-only scans

2011-10-11 Thread PostgreSQL - Hans-Jürgen Schönig
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

Re: [HACKERS] index-only scans

2011-10-11 Thread Tom Lane
Alexander Korotkov aekorot...@gmail.com writes: On Tue, Oct 11, 2011 at 5:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: I haven't thought as far ahead as how we might get the information needed for a per-opclass flag. A syntax addition to CREATE OPERATOR CLASS might be the only way. Shouldn't

Re: [HACKERS] index-only scans

2011-10-11 Thread Alexander Korotkov
On Wed, Oct 12, 2011 at 12:35 AM, Tom Lane t...@sss.pgh.pa.us wrote: Hm. I had been supposing that lossless compress functions would just be no-ops. If that's not necessarily the case then we might need something different from the opclass's decompress function to get back the original

Re: [HACKERS] index-only scans

2011-10-11 Thread Dimitri Fontaine
Tom Lane t...@sss.pgh.pa.us writes: I haven't thought as far ahead as how we might get the information needed for a per-opclass flag. A syntax addition to CREATE OPERATOR CLASS might be the only way. It looks to me like it's related to the RECHECK property. Maybe it's just too late, though.

Re: [HACKERS] index-only scans

2011-10-11 Thread Tom Lane
Alexander Korotkov aekorot...@gmail.com writes: On Wed, Oct 12, 2011 at 12:35 AM, Tom Lane t...@sss.pgh.pa.us wrote: Maybe, instead of a simple constant amcanreturn column, we need an AM API function that says whether the index can return data. I like idea of such AM API function. Since

Re: [HACKERS] index-only scans

2011-10-10 Thread Tom Lane
I wrote: I have mostly-working code for approach #3, but I haven't tried to make EXPLAIN work yet. While looking at that I realized that there's a pretty good argument for adding the above-mentioned explicit TargetEntry list representing the index columns to index-only plan nodes. Namely,

Re: [HACKERS] index-only scans

2011-10-09 Thread Tom Lane
I wrote: I believe that we should rejigger things so that when an index-only scan is selected, the executor *always* works from the data supplied by the index. Even if it has to visit the heap --- it will do that but just to consult the tuple's visibility data, and then use what it got from

Re: [HACKERS] index-only scans

2011-10-09 Thread Greg Stark
On Sun, Oct 9, 2011 at 9:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: At the moment I'm leaning to approach #3, but I wonder if anyone has a different opinion or another idea altogether. Would any of these make it more realistic to talk about the crazy plans Heikki suggested like doing two index

Re: [HACKERS] index-only scans

2011-10-09 Thread Tom Lane
Greg Stark st...@mit.edu writes: On Sun, Oct 9, 2011 at 9:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: At the moment I'm leaning to approach #3, but I wonder if anyone has a different opinion or another idea altogether. Would any of these make it more realistic to talk about the crazy plans

Re: [HACKERS] index-only scans

2011-10-09 Thread Greg Stark
On Sun, Oct 9, 2011 at 10:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: I don't think it's particularly relevant --- we would not want to use weird representations of the Vars outside the index scan nodes.  Above the scan they'd be just like any other upper-level Vars. I can't say I fully

Re: [HACKERS] index-only scans

2011-10-09 Thread Tom Lane
I wrote: There are at least four things we could do: ... 2. Set varno = OUTER (or maybe INNER). This is safe because there's no other use for OUTER/INNER in a table scan node. We would have to hack things so that the index tuple gets put into econtext-ecxt_outertuple (resp. ecxt_innertuple)

Re: [HACKERS] index-only scans

2011-10-09 Thread Greg Stark
On Mon, Oct 10, 2011 at 2:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: The need for this additional node list field also sways me in a direction that I'd previously been on the fence about, namely that I think index-only scans need to be their own independent plan node type instead of sharing a

Re: [HACKERS] index-only scans

2011-10-08 Thread Tom Lane
I wrote: Robert Haas robertmh...@gmail.com writes: Not really. We have detected a small performance regression when both heap and index fit in shared_buffers and an index-only scan is used. I have a couple of ideas for improving this. One is to store a virtual tuple into the slot instead of

Re: [HACKERS] index-only scans

2011-10-08 Thread Robert Haas
On Oct 8, 2011, at 11:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm inclined to do the last part immediately, since there's a performance argument for it, and then work on revising the executor implementation. Sounds great. Thanks for working on this. ...Robert -- Sent via pgsql-hackers

Re: [HACKERS] index-only scans

2011-10-07 Thread Tom Lane
Robert Haas robertmh...@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 feature: there needs to be a way to turn it off. Otherwise performance comparisons will be

Re: [HACKERS] index-only scans

2011-10-07 Thread Joshua D. Drake
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 feature: there needs to be a way to turn it off.

Re: [HACKERS] index-only scans

2011-10-07 Thread Robert Haas
On Fri, Oct 7, 2011 at 2:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@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 feature: there needs to be a

Re: [HACKERS] index-only scans

2011-10-07 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Oct 7, 2011 at 2:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm making some progress with this, but I notice what seems like a missing feature: there needs to be a way to turn it off.  Otherwise performance comparisons will be difficult to

Re: [HACKERS] index-only scans

2011-10-07 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Please find attached a patch implementing a basic version of index-only scans. This patch is the work of my colleague Ibrar Ahmed and myself, and also incorporates some code from previous patches posted by Heikki Linnakanagas. I've committed this

Re: [HACKERS] index-only scans

2011-10-07 Thread Robert Haas
On Fri, Oct 7, 2011 at 8:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: 1. The way that nodeIndexscan.c builds up the faux heap tuple is perhaps susceptible to improvement.  I thought about building a virtual tuple, but then what do I do with an OID column, if I have one?  Or maybe this should be

Re: [HACKERS] index-only scans

2011-10-06 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Please find attached a patch implementing a basic version of index-only scans. This patch is the work of my colleague Ibrar Ahmed and myself, and also incorporates some code from previous patches posted by Heikki Linnakanagas. I'm starting to review

Re: [HACKERS] index-only scans

2011-10-06 Thread Robert Haas
On Thu, Oct 6, 2011 at 3:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Please find attached a patch implementing a basic version of index-only scans.  This patch is the work of my colleague Ibrar Ahmed and myself, and also incorporates some code from

Re: [HACKERS] index-only scans

2011-10-06 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Not really. We have detected a small performance regression when both heap and index fit in shared_buffers and an index-only scan is used. I have a couple of ideas for improving this. One is to store a virtual tuple into the slot instead of building

Re: [HACKERS] index-only scans

2011-10-06 Thread Thom Brown
On 6 October 2011 21:11, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Not really.  We have detected a small performance regression when both heap and index fit in shared_buffers and an index-only scan is used. I have a couple of ideas for improving this.  One is

Re: [HACKERS] index-only scans

2011-09-25 Thread Marti Raudsepp
On Sun, Aug 14, 2011 at 00:31, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: That is somewhat compensated by the fact that tuples that are accessed more often are also more likely to be in cache. Fetching the heap tuple to check visibility is very cheap when the tuple is in

Re: [HACKERS] index-only scans

2011-09-25 Thread Robert Haas
On Sun, Sep 25, 2011 at 2:43 PM, Marti Raudsepp ma...@juffo.org wrote: On Sun, Aug 14, 2011 at 00:31, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: That is somewhat compensated by the fact that tuples that are accessed more often are also more likely to be in cache. Fetching

Re: [HACKERS] index-only scans

2011-09-23 Thread Cédric Villemain
2011/8/16 Anssi Kääriäinen anssi.kaariai...@thl.fi: On 08/14/2011 12:31 AM, Heikki Linnakangas wrote: The same idea could of course be used to calculate the effective cache hit ratio for each table. Cache hit ratio would have the problem of feedback loops, though. Yeah, I'm not excited

Re: [HACKERS] index-only scans

2011-08-16 Thread Anssi Kääriäinen
On 08/14/2011 12:31 AM, Heikki Linnakangas wrote: The same idea could of course be used to calculate the effective cache hit ratio for each table. Cache hit ratio would have the problem of feedback loops, though. Yeah, I'm not excited about making the planner and statistics more dynamic.

Re: [HACKERS] index-only scans

2011-08-15 Thread Jim Nasby
On Aug 13, 2011, at 4:31 PM, Heikki Linnakangas wrote: The example is much more realistic if the query is a fetch of N latest rows from a table. Very common use case, and the whole relation's visibility statistics are completely wrong for that query. That is somewhat compensated by the

  1   2   >