Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tU6SO-00E8lK-13 for pgsql-sql@arkaria.postgresql.org; Sat, 04 Jan 2025 15:52:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tU6SN-00BGj0-Er for pgsql-sql@arkaria.postgresql.org; Sat, 04 Jan 2025 15:52:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tTROa-0013UB-Ma for pgsql-sql@lists.postgresql.org; Thu, 02 Jan 2025 20:02:16 +0000 Received: from mail-am0eur02on20724.outbound.protection.outlook.com ([2a01:111:f403:2606::724] helo=EUR02-AM0-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tTROX-001lqn-2h for pgsql-sql@lists.postgresql.org; Thu, 02 Jan 2025 20:02:15 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=Fjxi7ZP5C64FcGI69ibu6i2z6zIab5ZtbPFNgmrdv/sqjXnKBa2vKSed81Pf8mMFWqoJXKZFZbCNvkBQkujVOlLPBhSLeNUyszc43FDS+ygKoFsn2hOgJLHRcfC/JluPTr+oUGe+tM8XS1GORk0AGmh043zNZEd9OEKdVW9XJpXkq9h0571AsRu+Fu1zyAeQdSerZH2RPO6N6YLwuW6NQRJ7Ugob6hUXZe73ameuV//lSYZajdPSPGlyStGRIdd8mAqV2JcmLz1vBFs0u2gvaq5SoNnWT91BcpGI+GEbR7cV7E55Em5ZzDBM/cdQgKvklBKV1LfWr3pcOgamS8Q3Fw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=KZnf+/Yd6oNWIJY2wxyri/qV9w9uieUj9/dkln1HCuI=; b=iS5zkal92IqKaPU9TB3WzRlaEIF5Go0+kIma+iZEYCedYtb3oqczp1KbD8J3UJwBgfgfGWzgz7w0ZumaKf0Nm6Gx+5tVM28+sfbUvS2ZeKpTx9IxG4CSCfgnN/JBMGh0xPy5MZhPUto6aE7+fGyBGfXoMqEDrhlyK+745oP6Sp4/jpFFbxJUbpFsxSZM/Vdf1FFCfnVHLeWRTEUhm6pIqK4vy3OCzcm2uIJ5WiCcMzrJi32EvXhJxt8wqm0D10GXJRLQapiQ2r3qf4aOmL2FEBv7PytJh1AA5LxKyZi3VamMroFJTb1pF+kJ5SdrJ1WRa8sbyRrc5fN6OrW5KISTqw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=asseco.pl; dmarc=pass action=none header.from=asseco.pl; dkim=pass header.d=asseco.pl; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=asseco.pl; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=KZnf+/Yd6oNWIJY2wxyri/qV9w9uieUj9/dkln1HCuI=; b=QxQOhUFUtCUwXEJWKgJrH6yhoqPwBRzWjePVHjUAGvn99XgL38mRYtiWXoqFeUyNtpBGdT4bsohC9/m5F2q1fLF2O9hSJJA3sUr+cHz6YNoyJo9H7Lzkthrv/IMCr1feMIof9bfUxpC8IE5OmSpvyxIStSjsnwKNjGWAYlbhM7Q= Received: from DU0PR04MB9419.eurprd04.prod.outlook.com (2603:10a6:10:35a::16) by DU0PR04MB9418.eurprd04.prod.outlook.com (2603:10a6:10:359::6) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8314.12; Thu, 2 Jan 2025 20:02:05 +0000 Received: from DU0PR04MB9419.eurprd04.prod.outlook.com ([fe80::25ff:14b2:b5bf:7512]) by DU0PR04MB9419.eurprd04.prod.outlook.com ([fe80::25ff:14b2:b5bf:7512%4]) with mapi id 15.20.8314.012; Thu, 2 Jan 2025 20:02:05 +0000 From: Tomasz Szypowski To: "pgsql-sql@lists.postgresql.org" Subject: View performance with implicit cast Thread-Topic: View performance with implicit cast Thread-Index: AdtdT8D9FHqkp7G6RrOZJX+LMa4J3A== Date: Thu, 2 Jan 2025 20:02:05 +0000 Message-ID: Accept-Language: pl-PL, en-US Content-Language: pl-PL X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_Enabled=true; MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_SetDate=2025-01-02T19:51:35Z; MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_Method=Standard; MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_Name=General; MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_SiteId=88152bde-cfa3-4a5c-b981-a785c624bb42; MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_ActionId=c9e530b9-b5eb-49ca-86f7-e9864058afa3; MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_ContentBits=0 authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=asseco.pl; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: DU0PR04MB9419:EE_|DU0PR04MB9418:EE_ x-ms-office365-filtering-correlation-id: 22c704dc-38b3-4055-c2e4-08dd2b685493 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|1800799024|366016|8096899003|38070700018; x-microsoft-antispam-message-info: =?us-ascii?Q?XeFwx+6NT3U54Tiozy56q/pVONOaVTDJc2iFVLd55jY2V7sWR/JHSrMbb5km?= =?us-ascii?Q?a+/5fY/hToiV7qzovEYF5HaaMWw4VfskBFDViWMHAoVUs2LGWCnmTJhuPSLb?= =?us-ascii?Q?vjepADMEyJxN4m+yZbFtYTiIVXtmkuLydkW3EDqkZUHlavBE8pJvqocEk8KQ?= =?us-ascii?Q?uw8SmqfT8PDzbnFNsTenBp8YcPdlh0YwvdcKJy6oD0BEwpkx1X7Nf20xKOHs?= =?us-ascii?Q?AVI+TYpeK+Ia49nOBVlmV+PLnj6AXV1cX5H7MdIGKZZfI2hd08FZnmRbQEL5?= =?us-ascii?Q?B8LM+G8rKFDrUsC34dPz1YYnFIgr5xk2geuOsC2uhAFi2G/FShhD+q4f9EzI?= =?us-ascii?Q?CtuMlpW9F+uSs9V3zQ0gchkSWT70WAVPEijWX+6ioqwmpltdVnZ+7ybid7wH?= =?us-ascii?Q?/xJZeeoyI7GKs74el5Gh9YZHdHnCGNowq9jDkyK1HHsSo7d1vgg2DGsf7Nbk?= =?us-ascii?Q?+wSiKUtad0G6sirN3Qg0f0q+0DRZGbqXfgKq2YyniATAReAJk+R4RaC7Nrha?= =?us-ascii?Q?RcKlHM9FewBgjbOuuww50qX8DEQYbC5AWxoY5cHa41yJKFFqsN8vJsyac4O2?= =?us-ascii?Q?DMMNxC6pC6bSwh3Mn1cRFoKIEZOb3HN01UGF/54NkQYInxSfcYh9nrrNyR1q?= =?us-ascii?Q?bURTFMBAtIM2fuDOIh3m6R9fiN0IKhdsH/AN67wyIp9lAN39x9foAjuQcP7I?= =?us-ascii?Q?5/m/UTVWLe5KbrvVlFd95KEOKWUlyr9g9kN/ZeTM55axwG+wsNT7wUETC4+8?= =?us-ascii?Q?OoQTs+7UWA/fYniFGrXRs8Af32hQQpA3LDi1Lt0+kBB1bRf6DYWaP2ndJPuB?= =?us-ascii?Q?r1AdDQLeotFWs04VH2FCxNX6U9GmC3rUDbq5zgukbkgD/Qh7s6+7B/KqCEC8?= =?us-ascii?Q?YeDEckL2AcqgdonDw6DqOZmDWv2y5ED+FtOlDY5o0oV+d3LsBDRHaaerY2nT?= =?us-ascii?Q?wW5yRfI7rP5cIaiHzG7cNttBFNeid21xiJAH9Wt6ys+jGlLPUAsmfPjqLKLn?= =?us-ascii?Q?ANXOCc8DSgsYmbD8fDBHKEfuKYKbE9gVY/catPf2vVTuFpJjGv7gYs3H/Bof?= =?us-ascii?Q?EAwJdWXqSQcfo6FnpuEmf0aKRIYamcBIn429MmY91E1QL84cMXZu7Rv3ZYgi?= =?us-ascii?Q?5HD2nB0lavemmNQu1AHWG70Ggk0QLc3t7b2yB4jYvSlPsgzgnAOcsyiQ3bor?= =?us-ascii?Q?YAtG81zeDN0tftSWP1UWFO9llOqjxriEued4LqmEcpTuC+h/TxkRO4aC7/9S?= =?us-ascii?Q?ZRIijyU4LO53boe6D0Xbb2Fpx/Sl4TGLex/jUq8ukJj3zSmdJBmeNRGFELDs?= =?us-ascii?Q?NLNSEXgZnsS7Dli+bDZxihj4lfF7k+WRtMIHizEInsBsnGTLwCpRNGABtWi5?= =?us-ascii?Q?d3B18rH/OcJccT3K4zqhh7Z7dIUNnVkON++nhrx0PD2maMFMkGBCtjOSMULt?= =?us-ascii?Q?7bpZ5QKwYGOa65iyxJvUthNVzjv6h+nz?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DU0PR04MB9419.eurprd04.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(1800799024)(366016)(8096899003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?Z7Np2omOLutJ7lJbmh8d7Dau27R5l8Z+kFIehJyP5oH0+/VVdB9BWHCqoHRT?= =?us-ascii?Q?z4/nAfSsjsl3TYhA7hbBNa/07+E3dGFZohMULvr68LXqkk0PV4JpfmBruTcO?= =?us-ascii?Q?+ETP9TtP2jORA/gDSb7hFTgwKufObiCiwzHGoMxN/Gzt2IONE2Pn5JsHTKoe?= =?us-ascii?Q?jtoq6UMbpxqHvuBOj6mSJOAcuaclYQtgFyW444/YecKgufOyO1q818M9XgfU?= =?us-ascii?Q?WYWvW/ejp63Bp2QU3uYvAablgvNgLpOqvyZyN8PkUFR4JY6whiOnc2YX7QEE?= =?us-ascii?Q?TPzoQ597GVEy/I+VHNs65LMp9mbH6YDTHZY/vHC4Ycgk4MfALVitCw3nAeM7?= =?us-ascii?Q?hv5CqhPFBqZebnbnNB7ZGleVxiD7NlVPS6XdeN/OzNNXPx+MEh/ZWGeCrElE?= =?us-ascii?Q?rceYqYqQbrVC01lek8vI3RyoY7sKqDu4/7fRQWOyaIeevCC9NiQEogKBX6pP?= =?us-ascii?Q?DhTaUJy/UZ6xxeLYsJZQyBIqU6wgOT++/bTq/LbJ7zP9yR60l8sEJ5MmXtZH?= =?us-ascii?Q?MGdLjkAk/Xo9E42Pp4Z5XytmmzlC9ohrtS4fRCNR1JYHC/grktdJkk67LMvb?= =?us-ascii?Q?ef8/38zOaM9ebu87LHemBGpsY8ghAiFYy0nlAcHn7QaAAQUQvJNVpwkxEpYx?= =?us-ascii?Q?mdWDYjJaobLCFMJcsovlUByuNsvQ7dN79AcRqsGzwOM0WdAGR6EBxrqFsidL?= =?us-ascii?Q?KJsXRWi6DJzMuUwP1UKyrpTsyppN951ftFRAcbxvAYT/M8C2vzIOAbk2BK28?= =?us-ascii?Q?xZChSYUWErxQixMU14X/5vInbZ3L5IzlN+l0SZifx430LI45YwdxazH4V+kB?= =?us-ascii?Q?+In9UD5ftgDuTdxhwXU6uaW1x4R6n3Ps8uHmuRm+pjVa26nUSeL+Rn9eN+ZV?= =?us-ascii?Q?vUuRX9GyNDHx/HrlZeQBgbDNqX+Gjn0tjNVqfFw2sqj1VWneXKzLflg4rGHy?= =?us-ascii?Q?bQOdQogCYJHnn6K04laOfvRY6pgcttvMPt8hi8q9ORs7ZRalRWKiXX5lem7J?= =?us-ascii?Q?c8dFh1GSN4wN64vOjQw3fWeFz+qO/bPMJP5nRTeyA7Cy6wkBIHqDV1Uw6l/B?= =?us-ascii?Q?zL+LNZiHcH0PxKsSqGWB9hqNua0JBZtYiaOnlDUf62yV+8ahClwVjMZg+tG8?= =?us-ascii?Q?nI4U616LBF78+oJ8eoWmEntzfcuVTzl4tr3C0X+b8zKvoeQmTDNz8AbP9kgD?= =?us-ascii?Q?2AQLfc8xUyl/7rkV+GmyvMfa0CWcnVfY49X/0JH6x4n8AAze95vBGT13KnDp?= =?us-ascii?Q?ywN1+tVAJwH/OVr9xvbEYatoCmaY+pdmIsrNNXaSYVCjm7Zj017tFloZKY5S?= =?us-ascii?Q?qXYdgAy7Het9EOftWNio/CxvYu7CE6Ot7j6Z+qCxOu+cmYrYwgHKmzX3fyRT?= =?us-ascii?Q?UGJEJyiFoP4O7Vxi2XyMXKh/+DqPc5j0GmywV2Tmrmj8OP523HjucSbEfLb5?= =?us-ascii?Q?U0YKzl8sHYQ9dhTEsrXIOwMf+CNrM6WQrZLHoyLRwjS1HO4lORvHyyS9tc6a?= =?us-ascii?Q?IM9ojXRNGRw63gj3aRdiay1q1jCFxZngf3ZHKAkpdQqu3QliiJQQRwVP0FD3?= =?us-ascii?Q?QRsV4N8EIV58zHKgU12/9+MGUtaCWeEmJ0rH6KQv?= Content-Type: multipart/alternative; boundary="_000_DU0PR04MB94192D3922E05C8E0A18F5F399142DU0PR04MB9419eurp_" MIME-Version: 1.0 X-OriginatorOrg: asseco.pl X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DU0PR04MB9419.eurprd04.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 22c704dc-38b3-4055-c2e4-08dd2b685493 X-MS-Exchange-CrossTenant-originalarrivaltime: 02 Jan 2025 20:02:05.1531 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 88152bde-cfa3-4a5c-b981-a785c624bb42 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: JaIKe0Kp0M9un4p5Uf0BhbhtnmAZsz7QmmmtKweReM7usbQiB16v1zkzpL42f/tS8ivzZo3nVyThridXcszJ61FFBB1NLzyh5EW+kSogOeg= X-MS-Exchange-Transport-CrossTenantHeadersStamped: DU0PR04MB9418 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DU0PR04MB94192D3922E05C8E0A18F5F399142DU0PR04MB9419eurp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello I have got an example, in which PostgreSQL could speed up: First, create and populate structures: create table test1 (date_1 timestamp); insert into test1 select generate_series( '2020-01-01', '2025-01-01', interval '1 minute'); CREATE INDEX idx_test1_date_1 on test1(date_1); create table test2 (date_2 date); insert into test2 select generate_series( '1900-01-01', '2200-01-01', interval '1 day'); CREATE INDEX idx_test2_date_2 on test2(date_2); Then create a view: create or replace view v_test as select date_1 from test1 union all select date_2 from test2 and now run the query: explain analyze select * from v_test where (date_1>=3D'2024-12-09') and (date_1<'2025-01-01'); You would see: Subquery Scan on v_test (cost=3D0.00..95710.28 rows=3D13702 width=3D8) (ac= tual time=3D440.998..472.586 rows=3D33143 loops=3D1) Filter: ((v_test.date_1 >=3D '2024-12-09 00:00:00'::timestamp without tim= e zone) AND (v_test.date_1 < '2025-01-01 00:00:00'::timestamp without time = zone)) Rows Removed by Filter: 2707311 -> Append (cost=3D0.00..54603.47 rows=3D2740454 width=3D8) (actual time= =3D0.038..332.601 rows=3D2740454 loops=3D1) -> Seq Scan on test1 (cost=3D0.00..37950.81 rows=3D2630881 width= =3D8) (actual time=3D0.035..165.235 rows=3D2630881 loops=3D1) -> Subquery Scan on "*SELECT* 2" (cost=3D0.00..2950.39 rows=3D109= 573 width=3D8) (actual time=3D0.025..16.285 rows=3D109573 loops=3D1) -> Seq Scan on test2 (cost=3D0.00..1580.73 rows=3D109573 wi= dth=3D4) (actual time=3D0.022..4.974 rows=3D109573 loops=3D1) Planning Time: 0.263 ms Execution Time: 473.598 ms I can imagine, that test2 cannot be searched by index due to the implicite = cast of the column date -> timestamp, but why the query doesn`t use index i= dx_test1_date_1? Furthermore it could use as well index idx_test2_date_2 Now let`s modify the view`s definition only to add explicit cast: create or replace view v_test as select date_1 from test1 union all select date_2::timestamp from test2 Run the query again: explain analyze select * from v_test where (date_1>=3D'2024-12-09') and (date_1<'2025-01-01'); Append (cost=3D0.43..3812.87 rows=3D31903 width=3D8) (actual time=3D0.024.= .17.287 rows=3D33143 loops=3D1) -> Index Only Scan using idx_test1_date_1 on test1 (cost=3D0.43..975.53= rows=3D31355 width=3D8) (actual time=3D0.023..5.500 rows=3D33120 loops=3D1= ) Index Cond: ((date_1 >=3D '2024-12-09 00:00:00'::timestamp without = time zone) AND (date_1 < '2025-01-01 00:00:00'::timestamp without time zone= )) Heap Fetches: 0 -> Seq Scan on test2 (cost=3D0.00..2677.83 rows=3D548 width=3D8) (actua= l time=3D3.744..8.610 rows=3D23 loops=3D1) Filter: (((date_2)::timestamp without time zone >=3D '2024-12-09 00= :00:00'::timestamp without time zone) AND ((date_2)::timestamp without time= zone < '2025-01-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 109550 Planning Time: 0.230 ms Execution Time: 18.937 ms So 1. Why the index is being used only when the column types are the same? 2. Why th other indexes are not being used? Maybe you can help me to rewrite the query Regards Thomas Szypowski --_000_DU0PR04MB94192D3922E05C8E0A18F5F399142DU0PR04MB9419eurp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello

 

I have got an example, in which PostgreSQL could spe= ed up:

 

First, create and populate structures:

 

create table test1 (date_1 timestamp);

insert into test1

select generate_series( '2020-01-01', '2025-01-01', = interval '1 minute');

CREATE INDEX idx_test1_date_1 on test1(date_1);=

 

create table test2 (date_2 date);

insert into test2

select generate_series( '1900-01-01', '2200-01-01', = interval '1 day');

CREATE INDEX idx_test2_date_2 on test2(date_2);=

 

Then create a view:

create or replace view v_test as

select date_1 from test1

union all

select date_2 from test2

 

and now run the query:

explain analyze

select * from  v_test

where (date_1>=3D'2024-12-09') and (date_1<'20= 25-01-01');

 

You would see:

Subquery Scan on v_test  (cost=3D0.00..95710.28= rows=3D13702 width=3D8) (actual time=3D440.998..472.586 rows=3D33143 loops= =3D1)

  Filter: ((v_test.date_1 >=3D '2024-12-09 0= 0:00:00'::timestamp without time zone) AND (v_test.date_1 < '2025-01-01 = 00:00:00'::timestamp without time zone))

  Rows Removed by Filter: 2707311

  ->  Append  (cost=3D0.00..54603.= 47 rows=3D2740454 width=3D8) (actual time=3D0.038..332.601 rows=3D2740454 l= oops=3D1)

        ->&nbs= p; Seq Scan on test1  (cost=3D0.00..37950.81 rows=3D2630881 width=3D8)= (actual time=3D0.035..165.235 rows=3D2630881 loops=3D1)

        ->&nbs= p; Subquery Scan on "*SELECT* 2"  (cost=3D0.00..2950.39 rows= =3D109573 width=3D8) (actual time=3D0.025..16.285 rows=3D109573 loops=3D1)<= o:p>

        &nbs= p;     ->  Seq Scan on test2  (cost=3D0.00= ..1580.73 rows=3D109573 width=3D4) (actual time=3D0.022..4.974 rows=3D10957= 3 loops=3D1)

Planning Time: 0.263 ms

Execution Time: 473.598 ms

 

I can imagine, that test2 cannot be searched by inde= x due to the implicite cast of the column date -> timestamp, but why the= query doesn`t use index idx_test1_date_1? Furthermore it could use as well= index idx_test2_date_2

 

Now let`s modify the view`s definition only to add e= xplicit cast:

create or replace view v_test as

select date_1 from test1

union all

select date_2::timestamp from test2

 

Run the query again:

explain analyze

select * from  v_test

where (date_1>=3D'2024-12-09') and (date_1<'20= 25-01-01');

 

Append  (cost=3D0.43..3812.87 rows=3D31903 widt= h=3D8) (actual time=3D0.024..17.287 rows=3D33143 loops=3D1)

  ->  Index Only Scan using idx_test1_d= ate_1 on test1  (cost=3D0.43..975.53 rows=3D31355 width=3D8) (actual t= ime=3D0.023..5.500 rows=3D33120 loops=3D1)

        Index Con= d: ((date_1 >=3D '2024-12-09 00:00:00'::timestamp without time zone) AND= (date_1 < '2025-01-01 00:00:00'::timestamp without time zone))

        Heap Fetc= hes: 0

  ->  Seq Scan on test2  (cost=3D0= .00..2677.83 rows=3D548 width=3D8) (actual time=3D3.744..8.610 rows=3D23 lo= ops=3D1)

        Filter: (= ((date_2)::timestamp without time zone >=3D '2024-12-09 00:00:00'::times= tamp without time zone) AND ((date_2)::timestamp without time zone < '20= 25-01-01 00:00:00'::timestamp without time zone))

        Rows Remo= ved by Filter: 109550

Planning Time: 0.230 ms

Execution Time: 18.937 ms

 

So

  1. Why the index is being used only when the column types are the same?<= o:p>
  2. Why th other indexes are not being used?

 

Maybe you can help me to rewrite the query

 

Regards

Thomas Szypowski

--_000_DU0PR04MB94192D3922E05C8E0A18F5F399142DU0PR04MB9419eurp_--