X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 07589D1B592 for ; Thu, 30 Oct 2003 13:57:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 60290-02 for ; Thu, 30 Oct 2003 09:56:56 -0400 (AST) Received: from lucifer.oficina (unknown [200.69.203.237]) by svr1.postgresql.org (Postfix) with ESMTP id C2026D1B57C for ; Thu, 30 Oct 2003 09:56:50 -0400 (AST) Received: from [192.168.1.190] (taz.oficina [192.168.1.190]) (authenticated bits=0) by lucifer.oficina (8.12.9/8.12.9) with ESMTP id h9UDuTrl013443; Thu, 30 Oct 2003 10:56:29 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Subject: Re: Ignoring index on (A is null), (A is not null) From: Franco Bruno Borghesi To: Cestmir Hybl Cc: pgsql-performance@postgresql.org In-Reply-To: <022001c39ed9$bfb09b20$0200a8c0@stratos> References: <017201c39d85$533eed70$0200a8c0@stratos> <022001c39ed9$bfb09b20$0200a8c0@stratos> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-8zkkx1U5saUbh5mb/a4l" Organization: AK y Asociados S.R.L. Message-Id: <1067522189.781.14.camel@taz> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4 Date: Thu, 30 Oct 2003 10:56:29 -0300 X-MailScanner-Information: Please contact the ISP for more information X-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/754 X-Sequence-Number: 4502 --=-8zkkx1U5saUbh5mb/a4l Content-Type: multipart/alternative; boundary="=-SEiTixuZx7WWUudUILjA" --=-SEiTixuZx7WWUudUILjA Content-Type: text/plain Content-Transfer-Encoding: quoted-printable try this: EXPLAIN [ANALYZE] SELECT a FROM table1 WHERE a IS NULL OR a>2; SET enable_seqscan TO off; EXPLAIN [ANALYZE] SELECT a FROM table1 WHERE a IS NULL OR a>2; =20 and compare the costs and times of both executions. This will tell you why postgresql is not using an index. For example, if you have 1000 rows in your table, they will fit in only one page of the table, so postgresql will (correctly) think that fetching and procesing this only page will be faster than fetching the index page, procesing it, and fetching and procesing the table page. Or perhaps there are so many rows that match your condition, that postgresql realizes that using and index or not it will still have to visit almost every page in the table. Many things can cause postgresql to think that a seqscan is better than an indexscan, If after comparing the EXPLAINs you see that postgresql is wrong, you should tweak your postgresql.conf (for example the cpu_index_tuple_cost value). hope it helps. On Thu, 2003-10-30 at 08:34, Cestmir Hybl wrote: > Are you seeing this question as totally off-topic in this list, or there = is > really no one who knows something about indexing "is null" bits in postgr= es? >=20 > Regards > CH >=20 >=20 > > Hi, > > > > suppose, for simplicity, there is a table with index like this: > > > > create table TABLE1 ( > > A integer > > ); > > create index TABLE1_A on TABLE1 (A); > > > > My question is: why psql (7.3.3) does not use index when filtering by A= IS > > NULL, A IS NOT > > NULL expressions? > > > > In fact, I need to filter by expression ((A is null) or (A > const)). > > > > Is there a way to filter by this expression using index? > > > > Functional index cannot be used (except strange solution with CASE-ing = and > > converting NULL values into some integer constant) > > > > > > > > -----------------------------------------------------------------------= --- > -- > > -- > > Index Scan using table1_a on table1 (cost=3D0.00..437.14 rows=3D29164 > width=3D4) > > Index Cond: (a > 1000) > > -----------------------------------------------------------------------= --- > -- > > -- > > Seq Scan on table1 (cost=3D0.00..448.22 rows=3D1 width=3D4) > > Filter: (a IS NULL) > > -------------------------------------------------------- > > Seq Scan on table1 (cost=3D0.00..448.22 rows=3D30222 width=3D4) > > Filter: (a IS NOT NULL) > > ------------------------------------------------------------ > > Seq Scan on table1 (cost=3D0.00..523.77 rows=3D29164 width=3D4) > > Filter: ((a IS NULL) OR (a > 1000)) > > ------------------------------------------------------------ > > > > > > CH >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? >=20 > http://www.postgresql.org/docs/faqs/FAQ.html >=20 --=-SEiTixuZx7WWUudUILjA Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable try this:
EXPLAIN [ANALYZE] SELECT a FROM table1 WHERE a IS NULL OR a>2;
SET enable_seqscan TO off;
EXPLAIN [ANALYZE] SELECT a FROM table1 WHERE a IS NULL OR a>2;

and compare the costs and times of both executions. This will tell you why = postgresql is not using an index.

For example, if you have 1000 rows in your table, they will fit in only one= page of the table, so postgresql will (correctly) think that fetching and = procesing this only page will be faster than fetching the index page, proce= sing it, and fetching and procesing the table page.
Or perhaps there are so many rows that match your condition, that postgresq= l realizes that using and index or not it will still have to visit almost e= very page in the table.

Many things can cause postgresql to think that a seqscan is better than an = indexscan, If after comparing the EXPLAINs you see that postgresql is wrong= , you should tweak your postgresql.conf (for example the cpu_index_tuple_co= st value).

hope it helps.

On Thu, 2003-10-30 at 08:34, Cestmir Hybl wrote:
Are you seeing this question as totally off=
-topic in this list, or there is
really no one who knows something about indexing "is null" bits i=
n postgres?

Regards
CH


> Hi,
>
> suppose, for simplicity, there is a table with index like this:
>
> create table TABLE1 (
>   A integer
> );
> create index TABLE1_A on TABLE1 (A);
>
> My question is: why psql (7.3.3) does not use index when filtering by =
A IS
> NULL, A IS NOT
> NULL expressions?
>
> In fact, I need to filter by expression ((A is null) or (A > const)=
).
>
> Is there a way to filter by this expression using index?
>
> Functional index cannot be used (except strange solution with CASE-ing=
 and
> converting NULL values into some integer constant)
>
>
>
> ----------------------------------------------------------------------=
----
--
> --
>  Index Scan using table1_a on table1  (cost=3D0.00..437.14 rows=3D29164
width=3D4)
>    Index Cond: (a > 1000)
> ----------------------------------------------------------------------=
----
--
> --
>  Seq Scan on table1  (cost=3D0.00..448.22 rows=3D1 width=3D4)
>    Filter: (a IS NULL)
> --------------------------------------------------------
>  Seq Scan on table1  (cost=3D0.00..448.22 rows=3D30222 width=3D4)
>    Filter: (a IS NOT NULL)
> ------------------------------------------------------------
>  Seq Scan on table1  (cost=3D0.00..523.77 rows=3D29164 width=3D4)
>    Filter: ((a IS NULL) OR (a > 1000))
> ------------------------------------------------------------
>
>
> CH


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html
--=-SEiTixuZx7WWUudUILjA-- --=-8zkkx1U5saUbh5mb/a4l Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA/oRiN21dVnhLsBV0RAnIuAJ42ilTVJ7z8ByTxPWuNFvPfRTp/rwCgiXuy gyH/7fInjxlt/fCJXY8F8bk= =w+UK -----END PGP SIGNATURE----- --=-8zkkx1U5saUbh5mb/a4l--