Re: [HACKERS] PATCH: index-only scans with partial indexes

At Thu, 31 Mar 2016 14:51:02 -0400, Tom Lane wrote in <19589.1459450...@sss.pgh.pa.us> > Kyotaro HORIGUCHI writes: > > Thank you for the comment. The new version is attached. > > Committed with rather heavy editorialization and a batch of

Re: [HACKERS] PATCH: index-only scans with partial indexes

Kyotaro HORIGUCHI writes: > Thank you for the comment. The new version is attached. Committed with rather heavy editorialization and a batch of regression test cases. regards, tom lane -- Sent via pgsql-hackers mailing list

Re: [HACKERS] PATCH: index-only scans with partial indexes

Thank you for the comment. The new version is attached. Some issues has not been addressed but the rest will be addresses in the next version. At Thu, 31 Mar 2016 08:42:50 +0200, Tomas Vondra wrote in <90d885f2-e5ce-6668-226f-c817154e4...@2ndquadrant.com> > On

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, At Wed, 30 Mar 2016 15:40:24 +0200, Tomas Vondra wrote in <8ad11fae-1cb7-2255-d80c-d1daafb53...@2ndquadrant.com> > FWIW the break was restored in the v9 by me. Yeah, I know it. Sorry for the misleading comment. > > FWIW, as mentioned upthread, I added the

Re: [HACKERS] PATCH: index-only scans with partial indexes

On 03/31/2016 01:36 AM, Tom Lane wrote: Kevin Grittner writes: I'm taking my name off as committer and marking it "Ready for Committer". If someone else wants to comment on the issues where Tom and Kyotaro-san still seem unsatisfied to the point where I can get my head

Re: [HACKERS] PATCH: index-only scans with partial indexes

Kevin Grittner writes: > I'm taking my name off as committer and marking it "Ready for > Committer". If someone else wants to comment on the issues where > Tom and Kyotaro-san still seem unsatisfied to the point where I > can get my head around it, I could maybe take it back

Re: [HACKERS] PATCH: index-only scans with partial indexes

On Wed, Mar 30, 2016 at 8:40 AM, Tomas Vondra wrote: >> === >> @@ -2697,6 +2697,7 @@ check_partial_indexes(PlannerInfo *root, RelOptInfo >> *rel) >> continue; /* don't repeat >> work if already proven OK */ >> >>

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 03/30/2016 06:01 AM, Kyotaro HORIGUCHI wrote: Thank you for polishing this. At Tue, 29 Mar 2016 13:31:19 -0500, Kevin Grittner wrote in I tried to whip this into shape, but there were a few areas

Re: [HACKERS] PATCH: index-only scans with partial indexes

Thank you for polishing this. At Tue, 29 Mar 2016 13:31:19 -0500, Kevin Grittner wrote in > I tried to whip this into shape, but there were a few areas I > didn't feel I had the necessary understanding to

Re: [HACKERS] PATCH: index-only scans with partial indexes

I tried to whip this into shape, but there were a few areas I didn't feel I had the necessary understanding to feel comfortable taking on the committer role for it. I've cleaned it up the best I could, fixing whitespace and typos, eliminating an unnecessary addition of an include, improving C

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 03/09/2016 09:29 AM, Kyotaro HORIGUCHI wrote: Hello, thank you for the comments. The new v8 patch is attched. I've looked at v8, and I do have a few minor comments: 1) indxpath.c uses get_plan_rowmark without including optimizer/prep.h so the compiler complains about missing

Re: [HACKERS] PATCH: index-only scans with partial indexes

On 3/9/16 3:29 AM, Kyotaro HORIGUCHI wrote: > Hello, thank you for the comments. The new v8 patch is attched. As far as I can see this patch should be marked "ready for review" so now it is. Thanks, -- -David da...@pgmasters.net -- Sent via pgsql-hackers mailing list

Re: [HACKERS] PATCH: index-only scans with partial indexes

Thank for changing status. At Wed, 16 Mar 2016 12:13:07 -0400, David Steele wrote in <56e98613.5000...@pgmasters.net> > On 3/9/16 3:29 AM, Kyotaro HORIGUCHI wrote: > > > Hello, thank you for the comments. The new v8 patch is attched. > > As far as I can see this patch

Re: [HACKERS] PATCH: index-only scans with partial indexes

Sorry, I should correct one point. At Wed, 09 Mar 2016 17:29:49 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20160309.172949.8413.horiguchi.kyot...@lab.ntt.co.jp> > Hello, thank you for the comments. The new v8 patch is attched. > > At Tue, 08

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, thank you for the comments. The new v8 patch is attched. At Tue, 08 Mar 2016 18:08:55 -0500, Tom Lane wrote in <21567.1457478...@sss.pgh.pa.us> > Kyotaro HORIGUCHI writes: > > Hello, This is a (maybe) committer-ready patch of a Tomas

Re: [HACKERS] PATCH: index-only scans with partial indexes

Kyotaro HORIGUCHI writes: > Hello, This is a (maybe) committer-ready patch of a Tomas > Vondra's project. I think this needs quite a bit of work yet. A few comments: * If we're going to pay the price of identifying implied restriction conditions in

Re: [HACKERS] PATCH: index-only scans with partial indexes

On Sat, Feb 27, 2016 at 6:19 PM, Michael Paquier wrote: > On Sat, Feb 27, 2016 at 1:08 AM, Robert Haas wrote: >> On Fri, Feb 26, 2016 at 6:16 PM, Michael Paquier >> wrote: >>> On Fri, Feb 26, 2016 at 4:18 PM, Kyotaro

Re: [HACKERS] PATCH: index-only scans with partial indexes

On Sat, Feb 27, 2016 at 1:08 AM, Robert Haas wrote: > On Fri, Feb 26, 2016 at 6:16 PM, Michael Paquier > wrote: >> On Fri, Feb 26, 2016 at 4:18 PM, Kyotaro HORIGUCHI >> wrote: >>> I marked this as "ready for

Re: [HACKERS] PATCH: index-only scans with partial indexes

On Fri, Feb 26, 2016 at 6:16 PM, Michael Paquier wrote: > On Fri, Feb 26, 2016 at 4:18 PM, Kyotaro HORIGUCHI > wrote: >> I marked this as "ready for commiter" and tried to add me as the >> *second* author. But the CF app forces certain

Re: [HACKERS] PATCH: index-only scans with partial indexes

On Fri, Feb 26, 2016 at 4:18 PM, Kyotaro HORIGUCHI wrote: > I marked this as "ready for commiter" and tried to add me as the > *second* author. But the CF app forces certain msyterious order > for listed names. Is there any means to arrange the author names > in

Re: [HACKERS] PATCH: index-only scans with partial indexes

I marked this as "ready for commiter" and tried to add me as the *second* author. But the CF app forces certain msyterious order for listed names. Is there any means to arrange the author names in desired order? At Fri, 26 Feb 2016 16:06:37 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, This is a (maybe) committer-ready patch of a Tomas Vondra's project. This patch applies on the current master and passes make check. This is to exclude some base-estrict clauses that are implied by index predicates on index scans on partial indexes. First, this patch adds a new member

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, At Thu, 25 Feb 2016 12:22:45 +0100, Tomas Vondra wrote in <56cee405.30...@2ndquadrant.com> > >> Attached is a v6 of the patch, which is actually the version > >> submitted by Kyotaro-san on 2015/10/8 rebased to current master and > >> with two additional

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 02/25/2016 11:56 AM, Kyotaro HORIGUCHI wrote: Hello, Tomas. my cerebral cortext gets squeezed by jumping from parser to planner. LOL At Wed, 24 Feb 2016 01:13:22 +0100, Tomas Vondra wrote in <56ccf5a2.5040...@2ndquadrant.com> Hi, On 12/06/2015 11:48

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, Tomas. my cerebral cortext gets squeezed by jumping from parser to planner. At Wed, 24 Feb 2016 01:13:22 +0100, Tomas Vondra wrote in <56ccf5a2.5040...@2ndquadrant.com> > Hi, > > On 12/06/2015 11:48 PM, Tomas Vondra wrote: > >/* > > * Frequently,

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 12/06/2015 11:48 PM, Tomas Vondra wrote: /* * Frequently, there will be no partial indexes, so first check to * make sure there's something useful to do here. */ have_partial = false; foreach(lc, rel->indexlist) { IndexOptInfo *index = (IndexOptInfo *)

Re: [HACKERS] PATCH: index-only scans with partial indexes

I have applied this patch to our working branch and during several weeks we ran various tests and benchmarks. We have not noticed any problems or performance degradation. And at some queries this patch cause very significant increase of performance - ten times: With this patch: postgres=#

Re: [HACKERS] PATCH: index-only scans with partial indexes

Konstantin Knizhnik wrote: > I am very interested in this patch because it allows to use partial indexes > to ... speed up inserts. > I have implemented "ALTER INDEX ... WHERE ..." construction which allows to > change predicate of partial index without necessity to fully rebuild it. > So it is

Re: [HACKERS] PATCH: index-only scans with partial indexes

Tomas Vondra wrote: > On 12/24/2015 04:05 AM, Michael Paquier wrote: > >Tomas, are you still working on that? This thread is stalling for 3 weeks. > > I haven't discovered anything interesting during the testing, so I guess the > "needs review" state is appropriate. Let's move the patch to the

Re: [HACKERS] PATCH: index-only scans with partial indexes

I am very interested in this patch because it allows to use partial indexes to ... speed up inserts. I have implemented "ALTER INDEX ... WHERE ..." construction which allows to change predicate of partial index without necessity to fully rebuild it. So it is not necessary to insert new records

Re: [HACKERS] PATCH: index-only scans with partial indexes

On Mon, Dec 7, 2015 at 7:48 AM, Tomas Vondra wrote: > Hello Kyotaro-san, > > Sorry for the long delay since your response in this thread :-( > > On 10/14/2015 08:06 AM, Kyotaro HORIGUCHI wrote: >> >> >> The table t is referred to twice by different (alias) names

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 12/24/2015 04:05 AM, Michael Paquier wrote: On Mon, Dec 7, 2015 at 7:48 AM, Tomas Vondra wrote: ... Otherwise the reworked patch seems fine to me, but I'll give it a bit more testing over the next few days. Thanks for the help so far! Tomas, are you

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello Kyotaro-san, Sorry for the long delay since your response in this thread :-( On 10/14/2015 08:06 AM, Kyotaro HORIGUCHI wrote: The table t is referred to twice by different (alias) names (though the diferrence is made by EXPLAIN, it shows that they are different rels in plantree). So

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, At Fri, 09 Oct 2015 16:32:31 +0200, Tomas Vondra wrote in <5617cfff.10...@2ndquadrant.com> > Hello, > > On 10/09/2015 02:59 AM, Kyotaro HORIGUCHI wrote: > >>> The cause of this seeming mismatch would be the place to hold > >>> indexrinfos. It is determined

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, On 10/09/2015 02:59 AM, Kyotaro HORIGUCHI wrote: The cause of this seeming mismatch would be the place to hold indexrinfos. It is determined only by baserestrictinfo and indpred. Any other components are not involved. So IndexClauseSet is found not to be the best place after all, I

Re: [HACKERS] PATCH: index-only scans with partial indexes

On 10/08/2015 07:30 AM, Kyotaro HORIGUCHI wrote: Hello, The attached patch applies on the latest v5 patch and will address above issues. (And modifies expected files, which are the manifestation of this improovement). As you see, it is a quite bad choice. Ugly and unreadable and fragile.

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, At Thu, 08 Oct 2015 15:24:35 +0200, Tomas Vondra wrote in <56166e93.8000...@2ndquadrant.com> > > > On 10/08/2015 07:30 AM, Kyotaro HORIGUCHI wrote: > > Hello, > > > >> The attached patch applies on the latest v5 patch and will > >> address above issues.

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, > The attached patch applies on the latest v5 patch and will > address above issues. (And modifies expected files, which are the > manifestation of this improovement). As you see, it is a quite bad choice. Ugly and unreadable and fragile. The cause of this seeming mismatch would be the

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, At Thu, 01 Oct 2015 01:36:51 +0200, Tomas Vondra wrote in <560c7213.3010...@2ndquadrant.com> > > Good point. I think we may simply point indexrinfos to the existing > > list > > of restrictions in that case - we don't need to copy it. So no > > additional

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, it looks fine. > >> cost_index() seems to need to be fixed. It would count excluded > >> clauses in estimate. > > > > Hmm, good point. The problem is that extract_nonindex_conditions uses > > baserel->baserestrictinfo again, i.e. it does not skip the implied > > clauses. So we may either

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello! On 09/30/2015 10:29 AM, Kyotaro HORIGUCHI wrote: By the way your comment for indexrinfos is as following, * 'indexrinfos' is a list of RestrictInfo nodes from the query's WHERE * or JOIN conditions, excluding those implied by the index predicate * (if the index is not partial, the

Re: [HACKERS] PATCH: index-only scans with partial indexes

On 09/30/2015 12:55 PM, Tomas Vondra wrote: Hello! On 09/30/2015 10:29 AM, Kyotaro HORIGUCHI wrote: By the way your comment for indexrinfos is as following, * 'indexrinfos' is a list of RestrictInfo nodes from the query's WHERE * or JOIN conditions, excluding those implied by the index

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, At Sat, 26 Sep 2015 18:00:33 +0200, Tomas Vondra wrote in <5606c121.10...@2ndquadrant.com> > Hi, > > On 09/26/2015 01:28 PM, Tomas Vondra wrote: > > > The patch does not change the check_index_only implementation - it > > still needs to check the clauses,

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, On 09/29/2015 12:27 PM, Kyotaro HORIGUCHI wrote: Hi, At Sat, 26 Sep 2015 18:00:33 +0200, Tomas Vondra wrote in <5606c121.10...@2ndquadrant.com> Hi, On 09/26/2015 01:28 PM, Tomas Vondra wrote: The patch does not change the check_index_only

Re: [HACKERS] PATCH: index-only scans with partial indexes

On 09/29/2015 04:57 PM, Tomas Vondra wrote: Hello, On 09/29/2015 12:27 PM, Kyotaro HORIGUCHI wrote: ... cost_index() seems to need to be fixed. It would count excluded clauses in estimate. Hmm, good point. The problem is that extract_nonindex_conditions uses baserel->baserestrictinfo

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, At Tue, 29 Sep 2015 16:57:03 +0200, Tomas Vondra wrote in <560aa6bf.5030...@2ndquadrant.com> > >>> The patch does not change the check_index_only implementation - it > >>> still needs to check the clauses, just like in v1 of the patch. To > >>> make this

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 09/18/2015 03:46 AM, Kyotaro HORIGUCHI wrote: Hello, At Thu, 17 Sep 2015 17:40:27 +0200, Tomas Vondra wrote in <55fadeeb.4000...@2ndquadrant.com> Yes, this seems sane. I've been poking at this a bit too, and I came to the same plan in general, except

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 09/26/2015 01:28 PM, Tomas Vondra wrote: The patch does not change the check_index_only implementation - it still needs to check the clauses, just like in v1 of the patch. To make this re-check unnecessary, we'd have to stick the remaining clauses somewhere, so that check_index_only can

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, sorry in advance for hardly readable long descriptions.. At Mon, 14 Sep 2015 13:27:47 +0200, Tomas Vondra wrote in <55f6af33.8000...@2ndquadrant.com> > I don't think this is particularly related to the patch, because some > of the anomalies can be observed even

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello Horiguchi-san, On 09/17/2015 12:45 PM, Kyotaro HORIGUCHI wrote: After all, what should be done to properly ignore useless conditions would be, 1. Make create_index_paths() to make the list of restrict clauses which are implied by the index predicate of the index in focus.

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hello, At Thu, 17 Sep 2015 17:40:27 +0200, Tomas Vondra wrote in <55fadeeb.4000...@2ndquadrant.com> > Yes, this seems sane. I've been poking at this a bit too, and I came > to the same plan in general, except that I think it's better to build > list of clauses that

Re: [HACKERS] PATCH: index-only scans with partial indexes

On 09/14/2015 12:51 PM, Kyotaro HORIGUCHI wrote: I rethinked on this from the first. Sorry. Hi, this looks to be a bug of cost_index(). The attached patch would fix that. No, that's wrong. please forget the patch. The qual in qpquals should be indexquals which is excluded because it is

Re: [HACKERS] PATCH: index-only scans with partial indexes

I rethinked on this from the first. > Sorry. > > > Hi, this looks to be a bug of cost_index(). The attached patch > > would fix that. > > No, that's wrong. please forget the patch. The qual in qpquals > should be indexquals which is excluded because it is not > necessary to be applied. The

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, At Sun, 13 Sep 2015 23:21:30 +0200, Tomas Vondra wrote in <55f5e8da.8080...@2ndquadrant.com> > That's indeed strange, but after poking into that for a while, it > seems rather like a costing issue. Let me demonstrate: ... > Now, both plans are index only scans,

Re: [HACKERS] PATCH: index-only scans with partial indexes

On 09/14/2015 09:35 AM, Kyotaro HORIGUCHI wrote: Hi, ,,, Which is exactly the difference between costs from amcostestimate idx1: 4769.115000 + 0.015 * 297823 = 9236.46 idx2: 6258.23 + 0.010 * 297823 = 9236.46 These calculations are exactly right, but you overlooked the

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, this looks to be a bug of cost_index(). The attached patch would fix that. = The following part in cost_index, > cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple; > > run_cost += cpu_per_tuple * tuples_fetched; Adds, *cpu_tuple_cost* (which is 0.01) + qpqual_cost.per_tuple

Re: [HACKERS] PATCH: index-only scans with partial indexes

Sorry. > Hi, this looks to be a bug of cost_index(). The attached patch > would fix that. No, that's wrong. please forget the patch. The qual in qpquals should be indexquals which is excluded because it is not necessary to be applied. The right way would be remove the cost for qpqual in

Re: [HACKERS] PATCH: index-only scans with partial indexes

Tomas Vondra wrote: > That means I've been unable to measure any significant overhead > of the patch. I've run a lot of benchmarks, and with anything resembling a common query the differences in planning time are lost in the noise. (I didn't create a better

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 09/13/2015 08:03 PM, Kevin Grittner wrote: In my view, the most disappointing thing about the patch is that when both indexes are present, it doesn't use the narrower one. If *only* the narrower index is present, it runs the index-only scan using that index for count(b) and count(*),

Re: [HACKERS] PATCH: index-only scans with partial indexes

On 4 September 2015 at 22:03, Tomas Vondra wrote: > A summary of 100 EXPLAIN timings looks like this: > > > master A B C D E F > - > min

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 09/05/2015 10:53 AM, Simon Riggs wrote: On 4 September 2015 at 22:03, Tomas Vondra > wrote: A summary of 100 EXPLAIN timings looks like this: master A B C D E

Re: [HACKERS] PATCH: index-only scans with partial indexes

25.08.2015 20:19, Jeff Janes пишет: On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra > wrote: Hi, currently partial indexes end up not using index only scans in most cases, because check_index_only() is overly

Re: [HACKERS] PATCH: index-only scans with partial indexes

On Fri, Sep 4, 2015 at 4:28 AM, Anastasia Lubennikova < a.lubennik...@postgrespro.ru> wrote: > > > 25.08.2015 20:19, Jeff Janes пишет: > > On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra < > tomas.von...@2ndquadrant.com> wrote: > >> Hi, >> >> currently partial indexes end up not using index only

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 09/04/2015 06:10 PM, Jeff Janes wrote: How can we evaluate Tom's performance concerns? I tried turning log_planner_stats on and using the regression test as a load generator, but I don't think that that is very demanding of a test. I've done a bit of benchmarking today, trying to

Re: [HACKERS] PATCH: index-only scans with partial indexes

On Fri, Jul 10, 2015 at 11:29 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: Hi, currently partial indexes end up not using index only scans in most cases, because check_index_only() is overly conservative, as explained in this comment: * XXX this is overly conservative for partial

Re: [HACKERS] PATCH: index-only scans with partial indexes

Hi, On 07/10/2015 10:43 PM, Tom Lane wrote: Tomas Vondra tomas.von...@2ndquadrant.com writes: currently partial indexes end up not using index only scans in most cases, because check_index_only() is overly conservative, as explained in this comment: ... I've done a bunch of tests, and I do

Re: [HACKERS] PATCH: index-only scans with partial indexes

Tomas Vondra tomas.von...@2ndquadrant.com writes: currently partial indexes end up not using index only scans in most cases, because check_index_only() is overly conservative, as explained in this comment: ... I've done a bunch of tests, and I do see small (hardly noticeable) increase in