Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cFxFt-0003ZD-HY for pgsql-performance@arkaria.postgresql.org; Sun, 11 Dec 2016 06:05:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cFxFs-00008R-90 for pgsql-performance@arkaria.postgresql.org; Sun, 11 Dec 2016 06:05:00 +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 1cFxFq-00008G-Er for pgsql-performance@postgresql.org; Sun, 11 Dec 2016 06:04:58 +0000 Received: from mail-wm0-x244.google.com ([2a00:1450:400c:c09::244]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cFxFi-0007FT-5s for pgsql-performance@postgresql.org; Sun, 11 Dec 2016 06:04:57 +0000 Received: by mail-wm0-x244.google.com with SMTP id u144so3906400wmu.0 for ; Sat, 10 Dec 2016 22:04:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=OCbSQEY/eMH2M6FTCLSFzNHPvR7U/tLFig84k5eR768=; b=VyFZC7emxXG8NcEPu1oRzbWcVjoGBfNRGEVqgjprZvC3Q/eClRPSgjVhN8aZqnpv4c TW/9w8u59kGDJGhwH1ckZ+xD99CO/J+snGVgRyC4DGiESgUxgZkPt9zZLfDse5ExssUv BMT3UvY23eLXOlScvJRzF1bx+YfX+LqCvg3WcbeOMyD6q72WArwvA7WpTotLHBY2nE3z 1kZ7QhZCvo8MGPtjPV5MzHl5o/GHiVds3JLMwsYMO3EOcnEfpFYsQxBVJ/FcMVVX+tju lk9Z4ncnHqmsYsr1rndMwqixgwWchKToiL7VQWypY8sOapTB7KeeVitR+4wlXxG/nVYe G15w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=OCbSQEY/eMH2M6FTCLSFzNHPvR7U/tLFig84k5eR768=; b=P5rKLPMxjkKzvsZAyVyBGMPTm/M51OrP1qwbb4hcsoI5Y4HOshUSBC74Ns3OUQVXdF s5skNQJ4hMLs2S9WaZx/Xv5vbbx7HW2AF4B4UfsGjvARoHeVpwm/FtSlPysb816HM/y6 AsIU9IPQjdTM3J/i4lkrnynyqKw8mXuqxccPIkHOPahyP0hiV1R4xKVrGEhbytOYLt04 4sXpEI0rlYXcEzjmjouOrrr7sXGjxt2Qlm1OAku9Vv/Tiglu0H7qCwzLVyTgfMHNDVf4 BRd6+FgPcFiyc2Wh3+FnNi47dBC10LMi/KXA/jqoLMLvrM3ZjUE0i1MS5MT0uKOI1RQD 00/w== X-Gm-Message-State: AKaTC03E7JjDAeEYkYMoCPUDYhcqLH0zYZ7X55JAgkm2CsDDiGJ8OnGjpgwxLXArbZD1IQ== X-Received: by 10.28.27.133 with SMTP id b127mr4763903wmb.59.1481436287418; Sat, 10 Dec 2016 22:04:47 -0800 (PST) Received: from [192.168.1.33] (219.red-83-44-188.dynamicip.rima-tde.net. [83.44.188.219]) by smtp.gmail.com with ESMTPSA id c133sm28997840wme.12.2016.12.10.22.04.46 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sat, 10 Dec 2016 22:04:46 -0800 (PST) From: Daniel Blanch Bataller Message-Id: <36867AC4-DDD8-464E-A0F7-066B4C88300C@gmail.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_4CBF8500-BC3B-45F4-AC7B-8F25BFB486E7" Mime-Version: 1.0 (Mac OS X Mail 10.1 \(3251\)) Subject: Re: Querying with multicolumn index Date: Sun, 11 Dec 2016 07:04:45 +0100 In-Reply-To: Cc: Tom Lane , Eric Jiang , pgsql-performance@postgresql.org, Eric Clark To: Tomas Vondra References: <5353.1481327501@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3251) X-Pg-Spam-Score: -2.7 (--) 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 --Apple-Mail=_4CBF8500-BC3B-45F4-AC7B-8F25BFB486E7 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi all, Thomas is absolutely right, the distribution I synthetically made, had 6M r= ecords but very old, 9M old, as you can see it had to skip 9M records befor= e finding a suitable record using time index.=20 EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id =3D 100 OR= DER BY "time" DESC LIMIT 1; QU= ERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------ Limit (cost=3D0.44..0.65 rows=3D1 width=3D36) (actual time=3D3827.807..38= 27.807 rows=3D1 loops=3D1) Buffers: shared hit=3D24592 read=3D99594 written=3D659 -> Index Scan Backward using updates_time_idx on updates (cost=3D0.44.= .1284780.53 rows=3D6064800 width=3D36) (actual time=3D3827.805..3827.805 ro= ws=3D1 loops=3D1) Filter: (driver_id =3D 100) Rows Removed by Filter: 9000000 Buffers: shared hit=3D24592 read=3D99594 written=3D659 Planning time: 0.159 ms Execution time: 3827.846 ms (8 rows) Here you have my tests where I was able to reproduce the problem using defa= ult settings on 9.6, 9.5 and 9.3. 9.6 and 9.5 choose the wrong index, while= 9.3 didn=E2=80=99t. (update: 9.5 didn=E2=80=99t fail last time)=20 --Apple-Mail=_4CBF8500-BC3B-45F4-AC7B-8F25BFB486E7 Content-Disposition: attachment; filename=test_bad_index_choice.sql Content-Type: application/octet-stream; x-unix-mode=0644; name="test_bad_index_choice.sql" Content-Transfer-Encoding: 7bit -- create database CREATE DATABASE test; \c test -- set timing \timing -- create table CREATE TABLE updates ( id serial, driver_id int not null, latitude double precision not null, longitude double precision not null, "time" timestamp with time zone not null default now(), vehicle_id integer); -- populate table with 99 homogeneus distributed values INSERT INTO updates SELECT q, q % 99, q, q, to_timestamp(q), q % 99 FROM generate_series(1, 15000000) q; -- populate table with 1 value with 6M rows, this data will be 9M records old, under 'time' field point of view . INSERT INTO updates SELECT q + 15000000, 100, q, q, to_timestamp(q), -- timestamp will start at 1 at end at 6M 100 FROM generate_series(1, 6000000) q; -- add constraints and indexes ALTER TABLE updates ADD PRIMARY KEY (id); CREATE INDEX ON updates ("time"); CREATE INDEX ON updates (driver_id, "time"); CREATE INDEX ON updates (vehicle_id, "time"); -- check behavior ANALYZE updates; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1; -- drop time index and rerun queries DROP INDEX updates_time_idx ; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1; --Apple-Mail=_4CBF8500-BC3B-45F4-AC7B-8F25BFB486E7 Content-Disposition: attachment; filename=bad_idx_choice.9.6.out Content-Type: application/octet-stream; x-unix-mode=0644; name="bad_idx_choice.9.6.out" Content-Transfer-Encoding: 7bit -- create database CREATE DATABASE test; CREATE DATABASE \c test You are now connected to database "test" as user "postgres". -- set timing \timing Timing is on. -- create table CREATE TABLE updates ( id serial, driver_id int not null, latitude double precision not null, longitude double precision not null, "time" timestamp with time zone not null default now(), vehicle_id integer); CREATE TABLE Time: 4,946 ms -- populate table with 99 homogeneus distributed values INSERT INTO updates SELECT q, q % 99, q, q, to_timestamp(q), q % 99 FROM generate_series(1, 15000000) q; INSERT 0 15000000 Time: 65135,307 ms -- populate table with 1 value with 6M rows, this data will be 9M records old, under 'time' field point of view . INSERT INTO updates SELECT q + 15000000, 100, q, q, to_timestamp(q), -- timestamp will start at 1 at end at 6M 100 FROM generate_series(1, 6000000) q; INSERT 0 6000000 Time: 25952,116 ms -- add constraints and indexes ALTER TABLE updates ADD PRIMARY KEY (id); ALTER TABLE Time: 16492,748 ms CREATE INDEX ON updates ("time"); CREATE INDEX Time: 12857,518 ms CREATE INDEX ON updates (driver_id, "time"); CREATE INDEX Time: 25581,842 ms CREATE INDEX ON updates (vehicle_id, "time"); CREATE INDEX Time: 25521,754 ms -- check behavior ANALYZE updates; ANALYZE Time: 271,494 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..2.71 rows=1 width=36) (actual time=0.073..0.073 rows=1 loops=1) Buffers: shared read=5 -> Index Scan Backward using updates_driver_id_time_idx on updates (cost=0.56..336808.92 rows=156800 width=36) (actual time=0.071..0.071 rows=1 loops=1) Index Cond: (driver_id = 50) Buffers: shared read=5 Planning time: 1.556 ms Execution time: 0.121 ms (7 rows) Time: 2,931 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.44..0.65 rows=1 width=36) (actual time=3827.807..3827.807 rows=1 loops=1) Buffers: shared hit=24592 read=99594 written=659 -> Index Scan Backward using updates_time_idx on updates (cost=0.44..1284780.53 rows=6064800 width=36) (actual time=3827.805..3827.805 rows=1 loops=1) Filter: (driver_id = 100) Rows Removed by Filter: 9000000 Buffers: shared hit=24592 read=99594 written=659 Planning time: 0.159 ms Execution time: 3827.846 ms (8 rows) Time: 3828,523 ms -- drop time index and rerun queries DROP INDEX updates_time_idx ; DROP INDEX Time: 24,188 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..2.71 rows=1 width=36) (actual time=0.038..0.038 rows=1 loops=1) Buffers: shared read=5 -> Index Scan Backward using updates_driver_id_time_idx on updates (cost=0.56..336808.92 rows=156800 width=36) (actual time=0.036..0.036 rows=1 loops=1) Index Cond: (driver_id = 50) Buffers: shared read=5 Planning time: 0.154 ms Execution time: 0.058 ms (7 rows) Time: 0,645 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..0.69 rows=1 width=36) (actual time=0.038..0.038 rows=1 loops=1) Buffers: shared hit=2 read=3 -> Index Scan Backward using updates_driver_id_time_idx on updates (cost=0.56..746893.16 rows=6064800 width=36) (actual time=0.037..0.037 rows=1 loops=1) Index Cond: (driver_id = 100) Buffers: shared hit=2 read=3 Planning time: 0.075 ms Execution time: 0.058 ms (7 rows) Time: 0,324 ms --Apple-Mail=_4CBF8500-BC3B-45F4-AC7B-8F25BFB486E7 Content-Disposition: attachment; filename=bad_idx_choice.9.5.out Content-Type: application/octet-stream; x-unix-mode=0644; name="bad_idx_choice.9.5.out" Content-Transfer-Encoding: 7bit -- create database CREATE DATABASE test; psql:test_bad_index_choice.sql:2: ERROR: database "test" already exists \c test You are now connected to database "test" as user "postgres". -- set timing \timing Timing is on. -- create table CREATE TABLE updates ( id serial, driver_id int not null, latitude double precision not null, longitude double precision not null, "time" timestamp with time zone not null default now(), vehicle_id integer); psql:test_bad_index_choice.sql:15: ERROR: relation "updates" already exists Time: 16,148 ms -- populate table with 99 homogeneus distributed values INSERT INTO updates SELECT q, q % 99, q, q, to_timestamp(q), q % 99 FROM generate_series(1, 15000000) q; psql:test_bad_index_choice.sql:18: ERROR: duplicate key value violates unique constraint "updates_pkey" DETAIL: Key (id)=(1) already exists. Time: 4738,561 ms -- populate table with 1 value with 6M rows, this data will be 9M records old, under 'time' field point of view . INSERT INTO updates SELECT q + 15000000, 100, q, q, to_timestamp(q), -- timestamp will start at 1 at end at 6M 100 FROM generate_series(1, 6000000) q; psql:test_bad_index_choice.sql:21: ERROR: duplicate key value violates unique constraint "updates_pkey" DETAIL: Key (id)=(15000001) already exists. Time: 1925,354 ms -- add constraints and indexes ALTER TABLE updates ADD PRIMARY KEY (id); psql:test_bad_index_choice.sql:24: ERROR: multiple primary keys for table "updates" are not allowed Time: 1,561 ms CREATE INDEX ON updates ("time"); CREATE INDEX Time: 84739,176 ms CREATE INDEX ON updates (driver_id, "time"); CREATE INDEX Time: 170537,927 ms CREATE INDEX ON updates (vehicle_id, "time"); CREATE INDEX Time: 166386,984 ms -- check behavior ANALYZE updates; ANALYZE Time: 2035,129 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..2.43 rows=1 width=36) (actual time=1.961..1.961 rows=1 loops=1) Buffers: shared hit=1 read=4 -> Index Scan Backward using updates_driver_id_time_idx1 on updates (cost=0.56..282334.99 rows=151527 width=36) (actual time=1.956..1.956 rows=1 loops=1) Index Cond: (driver_id = 50) Buffers: shared hit=1 read=4 Planning time: 4.054 ms Execution time: 2.035 ms (7 rows) Time: 10,833 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..0.69 rows=1 width=36) (actual time=1.118..1.119 rows=1 loops=1) Buffers: shared hit=2 read=3 -> Index Scan Backward using updates_driver_id_time_idx1 on updates (cost=0.56..767429.93 rows=6028203 width=36) (actual time=1.116..1.116 rows=1 loops=1) Index Cond: (driver_id = 100) Buffers: shared hit=2 read=3 Planning time: 0.304 ms Execution time: 1.172 ms (7 rows) Time: 2,057 ms -- drop time index and rerun queries DROP INDEX updates_time_idx ; DROP INDEX Time: 7,846 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..2.43 rows=1 width=36) (actual time=0.041..0.041 rows=1 loops=1) Buffers: shared hit=5 -> Index Scan Backward using updates_driver_id_time_idx1 on updates (cost=0.56..282334.99 rows=151527 width=36) (actual time=0.038..0.038 rows=1 loops=1) Index Cond: (driver_id = 50) Buffers: shared hit=5 Planning time: 0.356 ms Execution time: 0.085 ms (7 rows) Time: 0,964 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..0.69 rows=1 width=36) (actual time=0.052..0.052 rows=1 loops=1) Buffers: shared hit=5 -> Index Scan Backward using updates_driver_id_time_idx1 on updates (cost=0.56..767429.93 rows=6028203 width=36) (actual time=0.051..0.051 rows=1 loops=1) Index Cond: (driver_id = 100) Buffers: shared hit=5 Planning time: 0.205 ms Execution time: 0.090 ms (7 rows) Time: 0,596 ms --Apple-Mail=_4CBF8500-BC3B-45F4-AC7B-8F25BFB486E7 Content-Disposition: attachment; filename=bad_idx_choice.9.3.out Content-Type: application/octet-stream; x-unix-mode=0644; name="bad_idx_choice.9.3.out" Content-Transfer-Encoding: 7bit -- create database CREATE DATABASE test; psql:test_bad_index_choice.sql:2: ERROR: database "test" already exists \c test You are now connected to database "test" as user "postgres". -- set timing \timing Timing is on. -- create table CREATE TABLE updates ( id serial, driver_id int not null, latitude double precision not null, longitude double precision not null, "time" timestamp with time zone not null default now(), vehicle_id integer); psql:test_bad_index_choice.sql:15: ERROR: relation "updates" already exists Time: 125,904 ms -- populate table with 99 homogeneus distributed values INSERT INTO updates SELECT q, q % 99, q, q, to_timestamp(q), q % 99 FROM generate_series(1, 15000000) q; psql:test_bad_index_choice.sql:18: ERROR: duplicate key value violates unique constraint "updates_pkey" DETAIL: Key (id)=(1) already exists. Time: 3476,584 ms -- populate table with 1 value with 6M rows, this data will be 9M records old, under 'time' field point of view . INSERT INTO updates SELECT q + 15000000, 100, q, q, to_timestamp(q), -- timestamp will start at 1 at end at 6M 100 FROM generate_series(1, 6000000) q; psql:test_bad_index_choice.sql:21: ERROR: duplicate key value violates unique constraint "updates_pkey" DETAIL: Key (id)=(15000001) already exists. Time: 1286,336 ms -- add constraints and indexes ALTER TABLE updates ADD PRIMARY KEY (id); psql:test_bad_index_choice.sql:24: ERROR: multiple primary keys for table "updates" are not allowed Time: 1,046 ms CREATE INDEX ON updates ("time"); CREATE INDEX Time: 58828,806 ms CREATE INDEX ON updates (driver_id, "time"); CREATE INDEX Time: 96053,484 ms CREATE INDEX ON updates (vehicle_id, "time"); CREATE INDEX Time: 174520,698 ms -- check behavior ANALYZE updates; ANALYZE Time: 7129,911 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..2.92 rows=1 width=36) (actual time=1.033..1.033 rows=1 loops=1) Buffers: shared hit=1 read=4 -> Index Scan Backward using updates_driver_id_time_idx1 on updates (cost=0.56..354385.25 rows=150527 width=36) (actual time=1.032..1.032 rows=1 loops=1) Index Cond: (driver_id = 50) Buffers: shared hit=1 read=4 Total runtime: 1.065 ms (6 rows) Time: 6,421 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..2.91 rows=1 width=36) (actual time=16.900..16.900 rows=1 loops=1) Buffers: shared hit=2 read=3 -> Index Scan Backward using updates_driver_id_time_idx1 on updates (cost=0.56..14285339.97 rows=6079504 width=36) (actual time=16.898..16.898 rows=1 loops=1) Index Cond: (driver_id = 100) Buffers: shared hit=2 read=3 Total runtime: 16.937 ms (6 rows) Time: 17,305 ms -- drop time index and rerun queries DROP INDEX updates_time_idx ; DROP INDEX Time: 14,870 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 50 ORDER BY "time" DESC LIMIT 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.56..2.92 rows=1 width=36) (actual time=0.015..0.015 rows=1 loops=1) Buffers: shared hit=5 -> Index Scan Backward using updates_driver_id_time_idx1 on updates (cost=0.56..354385.25 rows=150527 width=36) (actual time=0.014..0.014 rows=1 loops=1) Index Cond: (driver_id = 50) Buffers: shared hit=5 Total runtime: 0.046 ms (6 rows) Time: 0,452 ms EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time" DESC LIMIT 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.56..2.91 rows=1 width=36) (actual time=0.027..0.027 rows=1 loops=1) Buffers: shared hit=5 -> Index Scan Backward using updates_driver_id_time_idx1 on updates (cost=0.56..14285339.97 rows=6079504 width=36) (actual time=0.026..0.026 rows=1 loops=1) Index Cond: (driver_id = 100) Buffers: shared hit=5 Total runtime: 0.041 ms (6 rows) Time: 0,263 ms --Apple-Mail=_4CBF8500-BC3B-45F4-AC7B-8F25BFB486E7 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 However when I tried to add more than one value with this strange distribut= ion ~ 30% of distribution to one value the index bad choice problem didn=E2= =80=99t happen again in none of the different versions. I Hope this helps. Regards, Daniel Blanch. > El 10 dic 2016, a las 21:34, Tomas Vondra = escribi=C3=B3: >=20 > Hi, >=20 > 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. >>=20 >> I tried to duplicate this behavior, without success. Are you running >> with nondefault planner parameters? >>=20 >=20 > My guess is this is a case of LIMIT the matching rows are uniformly distr= ibuted in the input data. The planner likely concludes that for a driver wi= th a lot of data we'll find the first row using ix_updates_time very quickl= y, and that it will be cheaper than inspecting the larger multi-column inde= x. But imagine a driver with a lots of data long time ago. That breaks the = LIMIT fairly quickly. >=20 > regards >=20 > --=20 > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >=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 --Apple-Mail=_4CBF8500-BC3B-45F4-AC7B-8F25BFB486E7 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --Apple-Mail=_4CBF8500-BC3B-45F4-AC7B-8F25BFB486E7--