pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: Cestmir Hybl <cestmirl@freeside.sk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Ignoring index on (A is null), (A is not null)
Date: Thu, 30 Oct 2003 10:56:29 -0300
Message-ID: <1067522189.781.14.camel@taz> (raw)
In-Reply-To: <022001c39ed9$bfb09b20$0200a8c0@stratos>
References: <017201c39d85$533eed70$0200a8c0@stratos>
	<022001c39ed9$bfb09b20$0200a8c0@stratos>

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, 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 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=0.00..437.14 rows=29164
> width=4)
> >    Index Cond: (a > 1000)
> > --------------------------------------------------------------------------
> --
> > --
> >  Seq Scan on table1  (cost=0.00..448.22 rows=1 width=4)
> >    Filter: (a IS NULL)
> > --------------------------------------------------------
> >  Seq Scan on table1  (cost=0.00..448.22 rows=30222 width=4)
> >    Filter: (a IS NOT NULL)
> > ------------------------------------------------------------
> >  Seq Scan on table1  (cost=0.00..523.77 rows=29164 width=4)
> >    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
> 

Attachments:

  signature.asc (application/pgp-signature, 186B) download

view thread (6+ messages)  latest in thread

Message-ID: <1067522189.781.14.camel@taz>
Permalink:  ../1067522189.781.14.camel@taz/
Also on:    postgresql.org/message-id/1067522189.781.14.camel@taz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: franco@akyasociados.com.ar, cestmirl@freeside.sk
  Subject: Re: Ignoring index on (A is null), (A is not null)
  In-Reply-To: <1067522189.781.14.camel@taz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox