Re: How to find out about index

On 8 Oct 2002 at 12:22, Savita wrote:

>
> Hi,
> What does the row indicate in the NOTICE: QUERY PLAN:

It's what it says. What follows is a query plan. A notice is used to indicate
warning style stuff to users but can be anything really. I'ts like a
fprintf(stderr..

> I am getting diff-2 values which is not same as the actual row present in the
> table.
> For example
> with select statement
> TESTDATA=# explain select * from os_customer_master;
> NOTICE: QUERY PLAN:
> Seq Scan on os_customer_master (cost=0.00..20.00 rows=1000 width=112)
> ==============================================================================
> with where clause
> TESTDATA=# explain select * from os_customer_master where
> outsourcer_legacy_custo
> mer_id='66084';
> NOTICE: QUERY PLAN:
> Seq Scan on os_customer_master (cost=0.00..22.50 rows=10 width=112)
> ================================================================================

Well the qeury plan is changed because of where condition. Relying upon last
updated statistics, planner know how many rows are in a relation and attempts
to project cost and other factor for a query. Obviously a where condition
changes this estimation..

> =
> After creating index on outsourcer_legacy_custo
> mer_id
> TESTDATA=# explain select * from os_customer_master where
> outsourcer_legacy_custo
> mer_id='66084';
> NOTICE: QUERY PLAN:
> Index Scan using cust_1 on os_customer_master (cost=0.00..12.22 rows=11
> width=112)

It has decided that an indexed scan would be better rather than a sequential
scan. Rows indicates expected number of rows but this is just an approximation.
Width is probably width of tuple returned. It would/should vary depending upon
what fields you select. But not sure of this width business..

HTH


Bye
 Shridhar

--
pension:    A federally insured chain letter.