Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TY0xJ-0002zO-P2 for pgsql-performance@postgresql.org; Mon, 12 Nov 2012 20:50:05 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TY0xD-0005sx-Rn for pgsql-performance@postgresql.org; Mon, 12 Nov 2012 20:50:04 +0000 Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz [60.234.150.59]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id qACKncoP003383 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Tue, 13 Nov 2012 09:49:47 +1300 Message-ID: <50A160E2.1050100@archidevsys.co.nz> Date: Tue, 13 Nov 2012 09:49:38 +1300 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:16.0) Gecko/20121026 Thunderbird/16.0.2 MIME-Version: 1.0 To: =?UTF-8?B?UmFmYcWCIFJ6ZXBlY2tp?= CC: ashutosh durugkar , pgsql-performance@postgresql.org Subject: Re: Planner sometimes doesn't use a relevant index with IN (subquery) condition References: In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by mbx.knossos.net.nz id qACKncoP003383 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/147 X-Sequence-Number: 48513 On 12/11/12 22:06, Rafa=C5=82 Rzepecki wrote: > This indeed works around the issue. Thanks! > > On Mon, Nov 12, 2012 at 9:53 AM, ashutosh durugkar wrote: >> Hey Rafal, >> >> >>> SELECT * FROM (SELECT run_id, utilization FROM stats) AS s WHERE >> run_id IN (SELECT run_id FROM runs WHERE server_id =3D 515); >> >> could you try this: >> >> >> SELECT * FROM (SELECT run_id, utilization FROM stats) AS s WHERE >> run_id =3D ANY(ARRAY(SELECT run_id FROM runs WHERE server_id =3D 515))= ; >> >> Thanks, >> >> On Sun, Nov 11, 2012 at 8:48 AM, Rafa=C5=82 Rzepecki >> wrote: >>> [Please CC me on replies, as I'm not subscribed; thank you.] >>> >>> I've ran into a problem with the query planner and IN (subquery) >>> conditions which I suspect to be a bug. I'll attempt to describe the >>> relevant details of my database and explain which behaviour I find >>> unexpected. I've also tried to trigger this behaviour in a clean >>> database; I think I've succeeded, but the conditions are a bit >>> different, so perhaps it's a different problem. I'll describe this >>> setup in detail below. >>> >>> I have a somewhat large table (~2.5M rows), stats, which is quite >>> often (several records a minute) INSERTed to, but never UPDATEd or >>> DELETEd from. (In case it's relevant, it has an attached AFTER INSERT >>> trigger which checks time and rebuilds an aggregate materialized view >>> every hour.) This is the schema: >>> # \d+ stats >>> Table >>> "serverwatch.stats" >>> Column | Type | >>> Modifiers | Storage | Description >>> >>> ------------------+-----------------------------+--------------------= --------------------------------+---------+------------- >>> id | integer | not null default >>> nextval('stats_id_seq'::regclass) | plain | >>> run_id | integer | not null >>> | plain | >>> start_time | timestamp without time zone | not null >>> | plain | >>> end_time | timestamp without time zone | not null >>> | plain | >>> cpu_utilization | double precision | >>> | plain | >>> disk_read_ops | bigint | >>> | plain | >>> disk_write_ops | bigint | >>> | plain | >>> network_out | bigint | >>> | plain | >>> network_in | bigint | >>> | plain | >>> disk_read_bytes | bigint | >>> | plain | >>> disk_write_bytes | bigint | >>> | plain | >>> Indexes: >>> "stats_pkey" PRIMARY KEY, btree (id) >>> "stats_day_index" btree (run_id, day(stats.*)) >>> "stats_month_index" btree (run_id, month(stats.*)) >>> "stats_week_index" btree (run_id, week(stats.*)) >>> Foreign-key constraints: >>> "stats_runs" FOREIGN KEY (run_id) REFERENCES runs(id) >>> Triggers: >>> stats_day_refresh_trigger AFTER INSERT OR UPDATE ON stats FOR EA= CH >>> STATEMENT EXECUTE PROCEDURE mat_view_refresh('serverwatch.stats_day') >>> Has OIDs: no >>> >>> day(), month() and week() functions are just trivial date_trunc on a >>> relevant field. The referenced table looks like this: >>> # \d+ runs >>> Table "serverwatch.= runs" >>> Column | Type | >>> Modifiers | Storage | Description >>> >>> -----------------+-----------------------------+---------------------= ------------------------------+---------+------------- >>> id | integer | not null default >>> nextval('runs_id_seq'::regclass) | plain | >>> server_id | integer | not null >>> | plain | >>> flavor | flavor | not null >>> | plain | >>> region | region | not null >>> | plain | >>> launch_time | timestamp without time zone | not null >>> | plain | >>> stop_time | timestamp without time zone | >>> | plain | >>> project_info_id | integer | not null >>> | plain | >>> owner_info_id | integer | not null >>> | plain | >>> Indexes: >>> "runs_pkey" PRIMARY KEY, btree (id) >>> "index_runs_on_flavor" btree (flavor) >>> "index_runs_on_owner_info_id" btree (owner_info_id) >>> "index_runs_on_project_info_id" btree (project_info_id) >>> "index_runs_on_region" btree (region) >>> "index_runs_on_server_id" btree (server_id) >>> Foreign-key constraints: >>> "runs_owner_info_id_fkey" FOREIGN KEY (owner_info_id) REFERENCES >>> user_infos(id) >>> "runs_project_info_id_fkey" FOREIGN KEY (project_info_id) >>> REFERENCES project_infos(id) >>> Referenced by: >>> TABLE "stats_day" CONSTRAINT "stats_day_runs" FOREIGN KEY (run_i= d) >>> REFERENCES runs(id) >>> TABLE "stats" CONSTRAINT "stats_runs" FOREIGN KEY (run_id) >>> REFERENCES runs(id) >>> Has OIDs: no >>> >>> Now consider this query - note I'm using a subselect here because the >>> problem originally manifested itself with a view: >>> SELECT * FROM (SELECT run_id, disk_write_ops FROM stats) AS s WHERE >>> run_id IN (SELECT id FROM runs WHERE server_id =3D 515); >>> >>> As might be expected, the planner chooses to use one of the three >>> indices with run_id: >>> http://explain.depesz.com/s/XU3Q >>> >>> Now consider a similar query, but with aggregation: >>> SELECT * FROM (SELECT run_id, SUM(disk_write_ops) FROM stats GROUP BY >>> run_id) AS s WHERE run_id IN (SELECT id FROM runs WHERE server_id =3D >>> 515); >>> >>> Now the picture is very different. The planner, unexplicably, >>> dismisses the index and opts instead to do a full scan on stats, the >>> table 2.5 million rows big. >>> http://explain.depesz.com/s/Rqt >>> >>> Note that the problem disappears when we replace the IN condition wit= h >>> literal: >>> SELECT * FROM (SELECT run_id, SUM(disk_write_ops) FROM stats GROUP BY >>> run_id) AS s WHERE run_id IN (1815, 1816); >>> >>> The ids are the result of the inner select ran separately, so the >>> query has the exact same result; it's worth pointing out that the >>> planner has a correct estimate on the selectivity of the condition - >>> exactly two rows from runs are selected, as expected. But when litera= l >>> is used the planner correctly chooses to use the index: >>> http://explain.depesz.com/s/lYc >>> >>> Similarly a correct plan is chosen when we unnest the inner SELECT: >>> SELECT run_id, SUM(disk_write_ops) FROM stats WHERE run_id IN (SELECT >>> id FROM runs WHERE server_id =3D 515) GROUP BY run_id; >>> http://explain.depesz.com/s/dlwZ >>> >>> >>> I've tried to replicate this on a clean database: >>> CREATE TABLE runs(run_id serial PRIMARY KEY, server_id INTEGER NOT NU= LL); >>> CREATE INDEX runs_server ON runs(server_id); >>> CREATE TABLE stats(entry_id serial PRIMARY KEY, run_id integer >>> REFERENCES runs NOT NULL, utilization INTEGER NOT NULL); >>> CREATE INDEX stats_runs ON stats(run_id); >>> >>> Now let's try some queries: >>> SELECT * FROM (SELECT run_id, utilization FROM stats) AS s WHERE >>> run_id IN (1212, 2323, 121, 561, 21, 561, 125, 2, 55, 52, 42); >>> http://explain.depesz.com/s/Kcb - fine, index used >>> >>> SELECT * FROM (SELECT run_id, utilization FROM stats) AS s WHERE >>> run_id IN (SELECT run_id FROM runs WHERE server_id =3D 515); >>> http://explain.depesz.com/s/QFs - seqscan! >>> Obviously it doesn't mean much, as the tables are empty and there are >>> no stats, but still a radically different plan is chosen for what is >>> essentially the same query. >>> >>> Note that in this case the behaviour is the same even when unnested: >>> SELECT run_id, utilization FROM stats WHERE run_id IN (SELECT run_id >>> FROM runs WHERE server_id =3D 515); >>> http://explain.depesz.com/s/y3GM >>> >>> So, is this a bug in the planner, or am I somehow subtly changing the >>> semantics of the query and don't notice? >>> I understand the planner perhaps tries to parallelize queries when a >>> SELECT is used in the IN clause, but given the stats it doesn't seem >>> to make much sense. >>> >>> Thanks, and let me know if you want me to test something on my >>> database over here or if there's some relevant info I've ommited. >>> >>> (PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc >>> (Ubuntu/Linaro 4.7.2-2ubuntu1) 4.7.2, 64-bit >>> running on Ubuntu 12.10, ubuntu package >>> postgresql-9.1-9.1.6-1ubuntu1:amd64, default configuration) >>> -- >>> Rafa=C5=82 Rzepecki >>> >>> >>> -- >>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql= .org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-performance >> > > Curious, would the following be of any use? SELECT DISTINCT r.run_id, s.utilization FROM runs AS r JOIN stats AS s USING (run_id) WHERE r.server_id =3D 515 /**/;/**/ Cheers, Gavin