q: explain analyze

On 1/10/06, Mark <sendmailtomark@yahoo.com> wrote:

Hello,

When I run 'explain analyze' on a query, how do I know what index is
used and is it used at all. What are specific words should I look
for?

Is "Seq Scan" indicates that index has been used?
How do I know that it was Full Table Scan?

Thanks,
Mark.

"Seq Scan" is short for Sequential Scan (Full Table Scan)...

you have to look for the word index to see what indexes are you using
if any (the name of the indexes are used too, so if you now indexe's
names you can find them in the explain analyze quickly)

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

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 >= ...
is there any way to force usage of index?

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'

Thank you,

Mark.
--- Jaime Casanova <systemguards@gmail.com> wrote:

On 1/10/06, Mark <sendmailtomark@yahoo.com> wrote:

Hello,

When I run 'explain analyze' on a query, how do I know what index

is

used and is it used at all. What are specific words should I look
for?

Is "Seq Scan" indicates that index has been used?
How do I know that it was Full Table Scan?

Thanks,
Mark.

"Seq Scan" is short for Sequential Scan (Full Table Scan)...

you have to look for the word index to see what indexes are you
using
if any (the name of the indexes are used too, so if you now
indexe's
names you can find them in the explain analyze quickly)

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

__________________________________________
Yahoo! DSL � Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com

Mark <sendmailtomark@yahoo.com> writes:

I do have an index on amount, but I guess it won't be used for >= ...

The general rule is that an index is only helpful for extracting a
fairly small subset of the table ("small" can mean as little as 1%).
So a one-sided inequality is not usefully indexable unless the
comparison constant is near the end of the data range. The planner
does understand this and will do the right things as long as the
ANALYZE statistics are reasonably accurate.

is there any way to force usage of index?

You can try setting enable_seqscan = off, but you'll likely find
that the planner is making the right decision. (If it isn't,
you may want to play with the value of random_page_cost ... but
be wary of changing that based on a small number of test cases.)

Can I defined index for _NOT_EQUAL_ ?

No. See above.

regards, tom lane

On 1/10/06, Mark <sendmailtomark@yahoo.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 ;)