Thread: q: explain analyze
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. __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
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 ;)