Re: q: explain analyze

On 1/10/06, Mark <sendmailtomark(at)yahoo(dot)com> wrote:
> This is great,
> Now here's my explain analyze:
>
> Seq Scan on balance (cost=0.00..54.51 rows=147 width=106) (actual
> time=0.026..0.767 rows=62 loops=1)
> Filter: (amount >= 0::double precision)
>
> I do have an index on amount, but I guess it won't be used for >= ...
>

look at the "rows" field... the first one is the estimated by the
planner the second is the actual number of rows retrieved for that Seq
Scan...

so if 147 (the estimated) is about a 10% of the total records in your
table an index will not be used because it will be loss performance...

> is there any way to force usage of index?

you can try SET enable_seqscan=off; before executing your query...
that will increase the cost of a seq scan and not be used unless there
is no other way to do it or the other methods are incredible slower

> another question:
> Can I defined index for _NOT_EQUAL_ ?
>
> I have a column that can have 5 values and my where is
> WHERE type <> 'A' OR type <> 'B'
>
> _or_ better to use:
> WHERE type ='C' OR type = 'D' OR type = 'E'
>

is not a good idea if you only will have 5 different values...
although you can create a partial index... but this is good only if
you create for the value that will be appear less (maybe 10% of total
record or less)... and can only be used for that specific case...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)