Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cFPPM-0005MW-RW for pgsql-performance@arkaria.postgresql.org; Fri, 09 Dec 2016 17:56:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cFPPM-0000er-ER for pgsql-performance@arkaria.postgresql.org; Fri, 09 Dec 2016 17:56:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cFPPL-0000eg-Rr for pgsql-performance@postgresql.org; Fri, 09 Dec 2016 17:56:31 +0000 Received: from post.visena.com ([46.226.10.50]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cFPPF-00055S-EZ for pgsql-performance@postgresql.org; Fri, 09 Dec 2016 17:56:31 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:To:From:Date; bh=QwyCNk2XdW3FcipeI0J8ybU8Wxzze5IuVbkEi8NavW0=; b=D7D97VINbm3eRAjiGIGwUl0ptk4dPmvJpHfSGEvlLOTfqV74Tk7TgJ4UWIfdDRas2S4iiwlVhwJKEHQz3zWcb5a8V8GHfHCp/2EMFOP0Nm2svGecH0KOs7IaZaEO9zUdByUVVBXPAOSlthMGOB0Jer4+Ac6RYaSZNG3CnPrYOdo=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1cFPPB-0005Kl-1a for pgsql-performance@postgresql.org; Fri, 09 Dec 2016 18:56:24 +0100 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1cFPPB-00068f-Pg for pgsql-performance@postgresql.org; Fri, 09 Dec 2016 18:56:21 +0100 Date: Fri, 9 Dec 2016 18:56:21 +0100 (CET) From: Andreas Joseph Krogh To: pgsql-performance@postgresql.org Message-ID: In-Reply-To: Subject: Re: Querying with multicolumn index MIME-Version: 1.0 X-Mailer: Visena Mail 2.0.3-SNAPSHOT X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1,HTML_MESSAGE=0.001) X-Pg-Spam-Score: -5.0 (-----) Content-Type: multipart/related; boundary="----=_Part_36_273572247.1481306181713" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------=_Part_35_1209982528.1481306181713 Content-Type: multipart/related; boundary="----=_Part_36_273572247.1481306181713" ------=_Part_36_273572247.1481306181713 Content-Type: multipart/alternative; boundary="----=_Part_37_1107904043.1481306181723" ------=_Part_37_1107904043.1481306181723 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 fredag 09. desember 2016 kl. 18:00:16, skrev Eric Jiang >: 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: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table "public.updates" =C2=A0 =C2=A0Column=C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= Type=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Modifiers =20 ------------+--------------------------+-----------------------------------= ------------------- =C2=A0id=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| integer=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null default nextval('updates_id_seq'::regclass) =C2=A0driver_id=C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | not null =C2=A0latitude=C2=A0 =C2=A0| double precision=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| not null =C2=A0longitude=C2=A0 | double precision=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= | not null =C2=A0time=C2=A0 =C2=A0 =C2=A0 =C2=A0| timestamp with time zone | not null= default now() =C2=A0vehicle_id | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 | Indexes: =C2=A0 =C2=A0 "updates_pkey" PRIMARY KEY, btree (id) =C2=A0 =C2=A0 "ix_updates_time" btree ("time") =C2=A0 =C2=A0 "updates_driver_id_time_idx" btree (driver_id, "time") =C2=A0 =C2=A0 "updates_vehicle_id_time_idx" btree (vehicle_id, "time") Table has about 15M records across 100 distinct driver_id. I want to get the last record for a specific driver: SELECT * FROM updates WHERE driver_id=3D123 ORDER BY "time" DESC LIMIT 1; 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. Good plan: =C2=A0Limit=C2=A0 (cost=3D0.11..1.38 rows=3D1 width=3D56) (actual time=3D2= .710..2.710 rows=3D1 loops=3D1) =C2=A0 =C2=A0->=C2=A0 Index Scan Backward using updates_driver_id_time_idx= on updates =C2=A0(cost=3D0.11..139278.28 rows=3D110051 width=3D56) (actual time=3D2.7= 09..2.709 rows=3D1 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (driver_id =3D 17127) =C2=A0Total runtime: 2.732 ms (4 rows) Bad plan: =C2=A0Limit=C2=A0 (cost=3D0.09..0.69 rows=3D1 width=3D56) (actual time=3D216769.111..216769.112 rows=3D1 loops=3D1) =C2=A0 =C2=A0->=C2=A0 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) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (driver_id =3D 30132) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Filter: 5132087 =C2=A0Total runtime: 216769.174 ms 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. 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. pgsql version: 9.3.14 and 9.5.3, already tried vacuum analyze. Thanks, Eric =C2=A0 You should be having this index: =C2=A0 create index=C2=A0updates_driver_time_idx ON=C2=A0updates(driver_id, "time"= DESC); =C2=A0 -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com =C2=A0 ------=_Part_37_1107904043.1481306181723 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 fredag 09. desember 2016 kl. 18:00:16, skrev Eric Jiang <eric@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:

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table "public.updates"
=C2=A0 =C2=A0Column=C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0T= ype=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Modifiers
------------+--------------------------+-----------------------------------= -------------------
=C2=A0id=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| integer=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null default
nextval('updates_id_seq'::regclass)
=C2=A0driver_id=C2=A0 | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | not null
=C2=A0latitude=C2=A0 =C2=A0| double precision=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| not null
=C2=A0longitude=C2=A0 | double precision=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|= not null
=C2=A0time=C2=A0 =C2=A0 =C2=A0 =C2=A0| timestamp with time zone | not null = default now()
=C2=A0vehicle_id | integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 |
Indexes:
=C2=A0 =C2=A0 "updates_pkey" PRIMARY KEY, btree (id)
=C2=A0 =C2=A0 "ix_updates_time" btree ("time")
=C2=A0 =C2=A0 "updates_driver_id_time_idx" btree (driver_id, &quo= t;time")
=C2=A0 =C2=A0 "updates_vehicle_id_time_idx" btree (vehicle_id, &q= uot;time")

Table has about 15M records across 100 distinct driver_id.

I want to get the last record for a specific driver:

SELECT * FROM updates WHERE driver_id=3D123 ORDER BY "time" DESC = LIMIT 1;

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.

Good plan:

=C2=A0Limit=C2=A0 (cost=3D0.11..1.38 rows=3D1 width=3D56) (actual time=3D2.= 710..2.710
rows=3D1 loops=3D1)
=C2=A0 =C2=A0->=C2=A0 Index Scan Backward using updates_driver_id_time_i= dx on updates
=C2=A0(cost=3D0.11..139278.28 rows=3D110051 width=3D56) (actual time=3D2.70= 9..2.709
rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (driver_id =3D 17127)
=C2=A0Total runtime: 2.732 ms
(4 rows)

Bad plan:

=C2=A0Limit=C2=A0 (cost=3D0.09..0.69 rows=3D1 width=3D56) (actual
time=3D216769.111..216769.112 rows=3D1 loops=3D1)
=C2=A0 =C2=A0->=C2=A0 Index Scan Backward using ix_updates_time on updat= es
(cost=3D0.09..272339.04 rows=3D448679 width=3D56) (actual
time=3D216769.110..216769.110 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: (driver_id =3D 30132)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Filter: 5132087
=C2=A0Total runtime: 216769.174 ms