On Sat, Oct 18, 2014 at 02:20:45PM -0400, Bruce Momjian wrote:
> On Sat, Oct 18, 2014 at 06:15:03PM +0200, Marko Tiikkaja wrote:
> > On 10/18/14, 5:46 PM, Tom Lane wrote:
> > >Marko Tiikkaja writes:
> > >>Yes, exactly; if I had had the option to disable the index from the
> > >>optimizer's point o
On 10/18/14, 8:58 AM, Bruce Momjian wrote:
On Fri, Oct 17, 2014 at 11:03:04PM -0400, Tom Lane wrote:
Bruce Momjian writes:
On Fri, Oct 17, 2014 at 06:15:37PM -0400, Tom Lane wrote:
Those stats were perfectly valid: what the planner is looking for is
accurate minimum and maximum values for the
On 10/19/14, 2:09 PM, Tom Lane wrote:
Yeah, perhaps. I'd been wondering about adding a tie-breaking rule,
but that's a much simpler way to think about it. OTOH, that approach
could result in some instability in the choice of index: if you've got
both (field_we_care_about, some_int_field) and (f
Jim Nasby writes:
> On 10/17/14, 10:16 PM, Tom Lane wrote:
>> BTW, on re-reading that code I notice that it will happily seize upon
>> the first suitable index ("first" in OID order), regardless of how many
>> lower-order columns that index has got. This doesn't make any difference
>> I think for
On 10/17/14, 10:16 PM, Tom Lane wrote:
I wrote:
Because it needs up-to-date min/max values in order to avoid being
seriously misled about selectivities of values near the endpoints.
See commit 40608e7f949fb7e4025c0ddd5be01939adc79eec.
BTW, on re-reading that code I notice that it will happily
On Sat, Oct 18, 2014 at 06:15:03PM +0200, Marko Tiikkaja wrote:
> On 10/18/14, 5:46 PM, Tom Lane wrote:
> >Marko Tiikkaja writes:
> >>Yes, exactly; if I had had the option to disable the index from the
> >>optimizer's point of view, I'd have seen that it's not used for looking
> >>up any data by a
On 10/18/14, 5:46 PM, Tom Lane wrote:
Marko Tiikkaja writes:
Yes, exactly; if I had had the option to disable the index from the
optimizer's point of view, I'd have seen that it's not used for looking
up any data by any queries, and thus I would have known that I can
safely drop it without slow
Marko Tiikkaja writes:
> On 10/18/14, 4:33 PM, Bruce Momjian wrote:
>> Well, if the index is there, why not use it? I thought the problem was
>> just that you had no visibility into how those statistics were being
>> accessed.
> Yes, exactly; if I had had the option to disable the index from the
Bruce Momjian writes:
> How many other cases do we have where the statistics are getting
> incremented and there is no user visibility into the operation?
* system catalog accesses
* vacuum/analyze/cluster/etc
The fact that system-initiated accesses get counted in the statistics
is a feature, no
On Sat, Oct 18, 2014 at 04:38:37PM +0200, Marko Tiikkaja wrote:
> On 10/18/14, 4:33 PM, Bruce Momjian wrote:
> >On Sat, Oct 18, 2014 at 04:29:41PM +0200, Marko Tiikkaja wrote:
> >>Another idea had was some way to tell the optimizer not to use that
> >>particular index for stats lookups, but probabl
On 10/18/14, 4:33 PM, Bruce Momjian wrote:
On Sat, Oct 18, 2014 at 04:29:41PM +0200, Marko Tiikkaja wrote:
Another idea had was some way to tell the optimizer not to use that
particular index for stats lookups, but probably the use case for
such a feature would be a bit narrow.
Well, if the in
On Sat, Oct 18, 2014 at 04:29:41PM +0200, Marko Tiikkaja wrote:
> On 10/18/14, 5:16 AM, Tom Lane wrote:
> >BTW, on re-reading that code I notice that it will happily seize upon
> >the first suitable index ("first" in OID order), regardless of how many
> >lower-order columns that index has got. Thi
On 10/18/14, 5:16 AM, Tom Lane wrote:
BTW, on re-reading that code I notice that it will happily seize upon
the first suitable index ("first" in OID order), regardless of how many
lower-order columns that index has got. This doesn't make any difference
I think for get_actual_variable_range's own
On Fri, Oct 17, 2014 at 11:03:04PM -0400, Tom Lane wrote:
> Bruce Momjian writes:
> > On Fri, Oct 17, 2014 at 06:15:37PM -0400, Tom Lane wrote:
> >> Those stats were perfectly valid: what the planner is looking for is
> >> accurate minimum and maximum values for the index's leading column, and
> >
I wrote:
> Because it needs up-to-date min/max values in order to avoid being
> seriously misled about selectivities of values near the endpoints.
> See commit 40608e7f949fb7e4025c0ddd5be01939adc79eec.
BTW, on re-reading that code I notice that it will happily seize upon
the first suitable index (
Bruce Momjian writes:
> On Fri, Oct 17, 2014 at 06:15:37PM -0400, Tom Lane wrote:
>> Those stats were perfectly valid: what the planner is looking for is
>> accurate minimum and maximum values for the index's leading column, and
>> that's what it got. You're correct that a narrower index could ha
On Fri, Oct 17, 2014 at 06:15:37PM -0400, Tom Lane wrote:
> Marko Tiikkaja writes:
> > On 10/17/14, 11:59 PM, Tom Lane wrote:
> >> Well, the index might've been getting used in queries too in a way that
> >> really only involved the first column. I think you're solving the wrong
> >> problem here
On 10/18/14, 12:15 AM, Tom Lane wrote:
Marko Tiikkaja writes:
I think there's a big difference between "this index was used to look up
stuff for planning" and "this index was used to answer queries quickly".
I think that's utter nonsense.
Well you probably know a bit more about the optimize
Marko Tiikkaja writes:
> On 10/17/14, 11:59 PM, Tom Lane wrote:
>> Well, the index might've been getting used in queries too in a way that
>> really only involved the first column. I think you're solving the wrong
>> problem here. The right problem is how to identify indexes that are
>> being us
On 10/17/14, 11:59 PM, Tom Lane wrote:
Marko Tiikkaja writes:
On 10/17/14, 11:47 PM, Tom Lane wrote:
Marko Tiikkaja writes:
So what I'd like to have is a way to be able to distinguish between
indexes being used to answer queries, and ones being only used for stats
lookups during planning.
On 10/17/14, 4:49 PM, Marko Tiikkaja wrote:
On 10/17/14, 11:47 PM, Tom Lane wrote:
Marko Tiikkaja writes:
This week we had one of the most annoying problems I've ever encountered
with postgres. We had a big index on multiple columns, say, foo(a, b,
c). According to pg_stat_all_indexes the i
Marko Tiikkaja writes:
> On 10/17/14, 11:47 PM, Tom Lane wrote:
>> Marko Tiikkaja writes:
>>> So what I'd like to have is a way to be able to distinguish between
>>> indexes being used to answer queries, and ones being only used for stats
>>> lookups during planning.
>> Why? Used is used.
> Be
On 10/17/14, 11:47 PM, Tom Lane wrote:
Marko Tiikkaja writes:
This week we had one of the most annoying problems I've ever encountered
with postgres. We had a big index on multiple columns, say, foo(a, b,
c). According to pg_stat_all_indexes the index was being used *all the
time*. However,
Marko Tiikkaja writes:
> This week we had one of the most annoying problems I've ever encountered
> with postgres. We had a big index on multiple columns, say, foo(a, b,
> c). According to pg_stat_all_indexes the index was being used *all the
> time*. However, after looking into our queries
Hi,
This week we had one of the most annoying problems I've ever encountered
with postgres. We had a big index on multiple columns, say, foo(a, b,
c). According to pg_stat_all_indexes the index was being used *all the
time*. However, after looking into our queries more closely, it turns
o
25 matches
Mail list logo
