Re: [PERFORM] Index scan cost calculation

2015-12-03 Thread Glyn Astill
> From: Jim Nasby >To: Jeff Janes ; Glyn Astill >Cc: Pgsql-performance >Sent: Wednesday, 2 December 2015, 22:32 >Subject: Re: [PERFORM] Index scan cost calculation > > >On 11/30/15 5:03 PM, Jeff Janes wrote: >> It thinks the combination of (show, type, best, bl

Re: [PERFORM] Index scan cost calculation

2015-12-02 Thread Jim Nasby
On 11/30/15 5:03 PM, Jeff Janes wrote: It thinks the combination of (show, type, best, block) is enough to get down to a single row. One index adds "flag" to that (which is not useful to the query) and the other adds "row" to that, which is useful but the planner doesn't think it is because onc

Re: [PERFORM] Index scan cost calculation

2015-12-01 Thread Glyn Astill
> >Clauses that can't be used in an "indexable" way are excluded from the >index selectivity, but not from the total query selectivity. > >> Or is it just likely that the selection of the new index is just by chance? > >Bingo. > Got it, thanks! Very much appreciated. Glyn -- Sent via pgsql-p

Re: [PERFORM] Index scan cost calculation

2015-11-30 Thread Jeff Janes
On Mon, Nov 30, 2015 at 6:03 AM, Glyn Astill wrote: > > > > > If I create the index show+best+block+row+seat then the planner appears to > favour that, and all is well. Despite the startup cost estimate being the > same, and total cost being 0.01 higher. This is something I fail to > understa

Re: [PERFORM] Index scan cost calculation

2015-11-30 Thread Glyn Astill
> From: Jeff Janes > To: Glyn Astill > Cc: Pgsql-performance > Sent: Saturday, 28 November 2015, 19:25 > Subject: Re: [PERFORM] Index scan cost calculation > > > Why does the index seats_index02 exist in the first place? It looks > like an index designed for th

Re: [PERFORM] Index scan cost calculation

2015-11-28 Thread Jeff Janes
On Thu, Nov 26, 2015 at 8:11 AM, Glyn Astill wrote: > Hi All, > > Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and > trying to figure out why a particular index is being chosen over another for > updates/deletes. > > From what I can see the reason is that plans using e

Re: [PERFORM] Index scan cost calculation

2015-11-26 Thread Tom Lane
Glyn Astill writes: >> From: Tom Lane >> The problem will probably go away by itself as your table grows, but >> if you don't want to wait, you might want to reflect on which of the index >> columns might be (partially?) functionally dependent on the other columns, >> and whether you could redesi

Re: [PERFORM] Index scan cost calculation

2015-11-26 Thread Glyn Astill
- Original Message - > From: Tom Lane > To: Glyn Astill > Cc: Pgsql-performance > Sent: Thursday, 26 November 2015, 16:44 > Subject: Re: [PERFORM] Index scan cost calculation > > Glyn Astill writes: >> Using pg 9.4.5 I'm looking at a table set up b

Re: [PERFORM] Index scan cost calculation

2015-11-26 Thread Tom Lane
Glyn Astill writes: > Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and > trying to figure out why a particular index is being chosen over another for > updates/deletes. > From what I can see the reason is that plans using either index have the same > exactly the same

Re: [PERFORM] Index scan cost calculation

2015-11-26 Thread Glyn Astill
- Original Message - > From: Glyn Astill > To: Pgsql-performance > Sent: Thursday, 26 November 2015, 16:11 > Subject: [PERFORM] Index scan cost calculation > > Hi All, > > Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and

[PERFORM] Index scan cost calculation

2015-11-26 Thread Glyn Astill
Hi All, Using pg 9.4.5 I'm looking at a table set up by a 3rd party application and trying to figure out why a particular index is being chosen over another for updates/deletes. >From what I can see the reason is that plans using either index have the same >exactly the same cost. So rather I'