Re: [HACKERS] Re: [BUGS] General Bug Report: Bug in optimizer - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: [HACKERS] Re: [BUGS] General Bug Report: Bug in optimizer
Date
Msg-id 199903181959.OAA07083@candle.pha.pa.us
Whole thread Raw
In response to Re: [HACKERS] Re: [BUGS] General Bug Report: Bug in optimizer  (Vadim Mikheev <vadim@krs.ru>)
List pgsql-hackers
> Bruce Momjian wrote:
> > 
> > My guess is that the creation of the index updates the table size
> > statistics.
> 
> Yes.
> 
> > However, when I see zero size, I don't know if it is accurate, or if
> > someone has added rows since the last vacuum/index creation, so I think
> > it is correct to use an index on a zero-length table if it is
> > appropriate.  If the size is 1, I will assume that number is accurate,
> > and do a sequential scan.
> > 
> > Does that make sense?
> 
> Yes. But we have to fix SeqScan for field1 = -1...

The basic problem is that the -1 is stored as:{ EXPR    :typeOid 0     :opType op    :oper       { OPER       :opno 558
     :opid 0       :opresulttype 23       }       :args (      { CONST       :consttype 23       :constlen 4
:constisnullfalse       :constvalue  4 [  4  0  0  0 ]        :constbyval true       }   )
 

This is clearly undesirable, and causes the optimizer to think it can't
use the index.  

Is this bug report for 6.4.*, or did are you running the current
development tree?  I assume you are running 6.4.*, and am surprised this
did not show as a larger problem.

I will look in the grammer for a fix.  This should come across as a
single -4 constant.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


pgsql-hackers by date:

Previous
From: Terry Mackintosh
Date:
Subject: Re: [HACKERS] Re: Developers Globe (FINAL) (fwd)
Next
From: Bruce Momjian
Date:
Subject: Re: [HACKERS] Re: [BUGS] General Bug Report: Bug in optimizer