Hi all,If anyone still interested in the issue I think I have a very plausible explanation of Eric’s postgresql bad index choice that is: bloated updates_driver_id_time_idx index.Though it’s possible to fool postgresql planner, as I’ve shown in previous tests, this happens with a very concrete data
Hi all,
Thomas is absolutely right, the distribution I synthetically made, had 6M
records but very old, 9M old, as you can see it had to skip 9M records before
finding a suitable record using time index.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY
"time" DE
On Sat, Dec 10, 2016 at 4:49 PM, Tom Lane wrote:
>> We aren't using any special planner settings - all enable_* options are "on".
>
> No, I'm asking about the cost settings (random_page_cost etc). The cost
> estimates you're showing seem impossible with the default settings.
Tom, really apprecia
Tomas Vondra writes:
> On 12/10/2016 12:51 AM, Tom Lane wrote:
>> I tried to duplicate this behavior, without success. Are you running
>> with nondefault planner parameters?
> My guess is this is a case of LIMIT the matching rows are uniformly
> distributed in the input data. The planner likely
Eric Jiang writes:
> We aren't using any special planner settings - all enable_* options are "on".
No, I'm asking about the cost settings (random_page_cost etc). The cost
estimates you're showing seem impossible with the default settings.
regards, tom lane
--
Sent via
Hi,
On 12/10/2016 12:51 AM, Tom Lane wrote:
Eric Jiang writes:
I have a query that I *think* should use a multicolumn index, but
sometimes isn't, resulting in slow queries.
I tried to duplicate this behavior, without success. Are you running
with nondefault planner parameters?
My guess i
We aren't using any special planner settings - all enable_* options are "on".
On Sat, Dec 10, 2016 at 12:06 AM, Daniel Blanch Bataller
wrote:
> As a quick fix: Have you considered dropping ix_updates_time index?
We do occasionally want to use ix_updates_time, although not very often.
> I’ve bee
Hi
As a quick fix: Have you considered dropping ix_updates_time index?
I’ve been able to reproduce the issue, but with bigger numbers than you. When I
dropped ix_updates_time it went much much faster. It uses
updates_driver_id_time_idx index instead.
For some reason the planner does not make
Eric Jiang writes:
> I have a query that I *think* should use a multicolumn index, but
> sometimes isn't, resulting in slow queries.
I tried to duplicate this behavior, without success. Are you running
with nondefault planner parameters?
regards, tom lane
--
Sent via
On Fri, Dec 9, 2016 at 9:56 AM, Andreas Joseph Krogh
wrote:
> You should be having this index:
>
> create index updates_driver_time_idx ON updates(driver_id, "time" *DESC)*;
>
I'm not sure I understand the intent of this fix - are you saying that
btree indexes only work in a certain direction?
På fredag 09. desember 2016 kl. 18:00:16, skrev Eric Jiang mailto:e...@doublemap.com>>:
Hi all,
I have a query that I *think* should use a multicolumn index, but
sometimes isn't, resulting in slow queries.
We have a DB that records GPS coordinates for vehicles:
11 matches
Mail list logo
