Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 10.1 \(3251\))
Subject: Re: Querying with multicolumn index
From: Daniel Blanch Bataller <daniel.blanch.bataller@gmail.com>
In-Reply-To: 
 <CAOfJSTwzQ7Fx6Yjeg9mFkMsM5OVKPoa=EgkHceGdkr1TWg8vkA@mail.gmail.com>
Date: Sat, 10 Dec 2016 09:06:31 +0100
Cc: pgsql-performance@postgresql.org,
 Eric Clark <eclark@doublemap.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <3AE2FE1F-58FB-4E71-803A-1CE9206D1615@gmail.com>
References: 
 <CAOfJSTwzQ7Fx6Yjeg9mFkMsM5OVKPoa=EgkHceGdkr1TWg8vkA@mail.gmail.com>
To: Eric Jiang <eric@doublemap.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi

As a quick fix: Have you considered dropping ix_updates_time index?=20

I=E2=80=99ve 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 upda=
tes_driver_id_time_idx index instead.

For some reason the planner does not make a good estimation in this case. C=
an you show us EXPLAIN (ANALYZE, BUFFERS) before and after dropping ix_upda=
tes_time index? Can you show us too the output of SHOW shared_buffers; ?=20

I suspect the issue has to do with low shared_buffers configuration and cac=
he misses, and maybe some costs suboptimal configuration I=E2=80=99ll try t=
o find it out, if anyone can enlighten us it will be very welcomed.


P.S. Meanwhile If you still need 'time' index, you can create an index usin=
g =E2=80=98time' and =E2=80=98customer_id' in this order.


Cheers,

Daniel Blanch.


> El 9 dic 2016, a las 18:00, Eric Jiang <eric@doublemap.com> escribi=C3=B3:
>=20
> Hi all,
> I have a query that I *think* should use a multicolumn index, but
> sometimes isn't, resulting in slow queries.
>=20
> We have a DB that records GPS coordinates for vehicles:
>=20
>                               Table "public.updates"
>   Column   |           Type           |                      Modifiers
> ------------+--------------------------+---------------------------------=
---------------------
> id         | integer                  | not null default
> nextval('updates_id_seq'::regclass)
> driver_id  | integer                  | not null
> latitude   | double precision         | not null
> longitude  | double precision         | not null
> time       | timestamp with time zone | not null default now()
> vehicle_id | integer                  |
> Indexes:
>    "updates_pkey" PRIMARY KEY, btree (id)
>    "ix_updates_time" btree ("time")
>    "updates_driver_id_time_idx" btree (driver_id, "time")
>    "updates_vehicle_id_time_idx" btree (vehicle_id, "time")
>=20
> Table has about 15M records across 100 distinct driver_id.
>=20
> I want to get the last record for a specific driver:
>=20
> SELECT * FROM updates WHERE driver_id=3D123 ORDER BY "time" DESC LIMIT 1;
>=20
> For some values of driver_id, it does what I expect and uses
> updates_driver_id_time_idx to fetch the records in 2 ms or less. For
> other values of driver_id, it does an index scan backwards on
> ix_updates_time, taking upwards of 2 minutes.
>=20
> Good plan:
>=20
> Limit  (cost=3D0.11..1.38 rows=3D1 width=3D56) (actual time=3D2.710..2.710
> rows=3D1 loops=3D1)
>   ->  Index Scan Backward using updates_driver_id_time_idx on updates
> (cost=3D0.11..139278.28 rows=3D110051 width=3D56) (actual time=3D2.709..2=
.709
> rows=3D1 loops=3D1)
>         Index Cond: (driver_id =3D 17127)
> Total runtime: 2.732 ms
> (4 rows)
>=20
> Bad plan:
>=20
> Limit  (cost=3D0.09..0.69 rows=3D1 width=3D56) (actual
> time=3D216769.111..216769.112 rows=3D1 loops=3D1)
>   ->  Index Scan Backward using ix_updates_time on updates
> (cost=3D0.09..272339.04 rows=3D448679 width=3D56) (actual
> time=3D216769.110..216769.110 rows=3D1 loops=3D1)
>         Filter: (driver_id =3D 30132)
>         Rows Removed by Filter: 5132087
> Total runtime: 216769.174 ms
>=20
>=20
> From cursory testing, the difference seems to be based on how many
> total rows there are for a particular driver. The above query uses
> updates_driver_id_time_idx for drivers with less than about 300K rows,
> but uses ix_updates_time for drivers with more than about 300K rows.
>=20
> Anything we can do to make it do the "right" thing? We are also
> considering denormalizing the data and keeping a "cache" of the same
> data in another table.
>=20
> pgsql version: 9.3.14 and 9.5.3, already tried vacuum analyze.
>=20
> Thanks,
> Eric
>=20
>=20
> --=20
> Eric Jiang, DoubleMap
> eric@doublemap.com | www.doublemap.com
>=20
>=20
> --=20
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance