Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to find out about index

How to find out about index

From
Savita
Date:
Hi all,

I am new to postgre.I want to create index on some of the column.

I just wanted to know that how to find out on which column we need to
create index because using explain command I am not getting the clear
picture as we get in oracle if we use the same explain plan command.

I am unable to decide where should I create the index based on which
criteria.

Any help will be highly appreciated.


--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

Re: How to find out about index

From
"Shridhar Daithankar"
Date:
On 8 Oct 2002 at 11:21, Savita wrote:

> Hi all,
>
> I am new to postgre.I want to create index on some of the column.
>
> I just wanted to know that how to find out on which column we need to
> create index because using explain command I am not getting the clear
> picture as we get in oracle if we use the same explain plan command.

Create indexes on columns that are used in where condition of select query.

HTH

Bye
 Shridhar

--
Lie, n.:    A very poor substitute for the truth, but the only one    discovered to
date.

Re: How to find out about index

From
Savita
Date:
Hi,

What does the row indicate in the NOTICE:  QUERY PLAN:

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)
=================================================================================
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)

 I can see that the cost has reduced but the rows has increased.What does this means and also what does width means.

any help will be highly appriciated.
 
 
 
 
 
 
 

Shridhar Daithankar wrote:

On 8 Oct 2002 at 11:21, Savita wrote:

> Hi all,
>
> I am new to postgre.I want to create index on some of the column.
>
> I just wanted to know that how to find out on which column we need to
> create index because using explain command I am not getting the clear
> picture as we get in oracle if we use the same explain plan command.

Create indexes on columns that are used in where condition of select query.

HTH

Bye
 Shridhar

--
Lie, n.:        A very poor substitute for the truth, but the only one  discovered to
date.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------
 

Re: How to find out about index

From
Martijn van Oosterhout
Date:
On Tue, Oct 08, 2002 at 12:22:15PM +0530, Savita wrote:
> Hi,
>
> What does the row indicate in the NOTICE:  QUERY PLAN:
>
> I am getting diff-2 values which is not same as the actual row present in the table.

Note that EXPLAIN produces an *estimate* of the number of rows. Obviously it
can't know the exact number unless it actually runs the query. EXPLAIN
ANALYZE will give you both the estimate and the actual.

> Seq Scan on os_customer_master  (cost=0.00..22.50 rows=10 width=112)
> Index Scan using cust_1 on os_customer_master  (cost=0.00..12.22 rows=11 width=112)
>
>  I can see that the cost has reduced but the rows has increased. What does
> this means and also what does width means.

It just means that due to some extra info, it estimates closer to 11 than 10
rows. It doesn't say anything about how many rows there really are.

The width is the estimated size of the tuples. If there is only one field
needed, the width will be small. You can see it change if you change the
columns output.

Try EXPLAIN ANALYZE.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Re: How to find out about index

From
"Shridhar Daithankar"
Date:
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.

Re: How to find out about index

From
Savita
Date:
Hi Martijn and Shridhar

Thanks very much for your help.

I have one more question

When I type the command
CYCLE4#EXPLAIN ANALYZE
CYCLE4# select * from os_customer_master where OUTSOURCER_LEGACY_CUSTOMER_ID='66';
why I get the error
ERROR:  parser: parse error at or near "ANALYZE"

is the command what I am typing is correct or it should be typed in different way.
 

Martijn van Oosterhout wrote:

On Tue, Oct 08, 2002 at 12:22:15PM +0530, Savita wrote:
> Hi,
>
> What does the row indicate in the NOTICE:  QUERY PLAN:
>
> I am getting diff-2 values which is not same as the actual row present in the table.

Note that EXPLAIN produces an *estimate* of the number of rows. Obviously it
can't know the exact number unless it actually runs the query. EXPLAIN
ANALYZE will give you both the estimate and the actual.

> Seq Scan on os_customer_master  (cost=0.00..22.50 rows=10 width=112)
> Index Scan using cust_1 on os_customer_master  (cost=0.00..12.22 rows=11 width=112)
>
>  I can see that the cost has reduced but the rows has increased. What does
> this means and also what does width means.

It just means that due to some extra info, it estimates closer to 11 than 10
rows. It doesn't say anything about how many rows there really are.

The width is the estimated size of the tuples. If there is only one field
needed, the width will be small. You can see it change if you change the
columns output.

Try EXPLAIN ANALYZE.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------
 

Re: How to find out about index

From
Robert Treat
Date:
Sounds like your running an older version of postgresql

Robert Treat

On Tue, 2002-10-08 at 04:44, Savita wrote:
> Hi Martijn and Shridhar
>
> Thanks very much for your help.
>
>
> I have one more question
>
>
> When I type the command
> CYCLE4#EXPLAIN ANALYZE
> CYCLE4# select * from os_customer_master where
> OUTSOURCER_LEGACY_CUSTOMER_ID='66';
> why I get the error
> ERROR:  parser: parse error at or near "ANALYZE"
>
>
> is the command what I am typing is correct or it should be typed in
> different way.
>
>
>
> Martijn van Oosterhout wrote:
>
>
> On Tue, Oct 08, 2002 at 12:22:15PM +0530, Savita wrote:
> > Hi,
> >
> > What does the row indicate in the NOTICE:  QUERY PLAN:
> >
> > I am getting diff-2 values which is not same as the actual row present
> in the table.
>
> Note that EXPLAIN produces an *estimate* of the number of rows.
> Obviously it
> can't know the exact number unless it actually runs the query. EXPLAIN
> ANALYZE will give you both the estimate and the actual.
>
>
> > Seq Scan on os_customer_master  (cost=0.00..22.50 rows=10 width=112)
> > Index Scan using cust_1 on os_customer_master  (cost=0.00..12.22
> rows=11 width=112)
> >
> >  I can see that the cost has reduced but the rows has increased. What
> does
> > this means and also what does width means.
>
>
> It just means that due to some extra info, it estimates closer to 11
> than 10
> rows. It doesn't say anything about how many rows there really are.
>
>
> The width is the estimated size of the tuples. If there is only one
> field
> needed, the width will be small. You can see it change if you change the
>
> columns output.
>
>
> Try EXPLAIN ANALYZE.
>

Re: How to find out about index

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Tue, Oct 08, 2002 at 12:22:15PM +0530, Savita wrote:
>> What does the row indicate in the NOTICE:  QUERY PLAN:

> Try EXPLAIN ANALYZE.

Also, do an ANALYZE first.  Those row counts look suspiciously like the
default ones that are used for never-ANALYZEd tables, where the planner
really has no idea what's in the table.

            regards, tom lane

Re: How to find out about index

From
Tom Lane
Date:
Savita <savita@india.hp.com> writes:
> When I type the command
> CYCLE4#EXPLAIN ANALYZE
> CYCLE4# select * from os_customer_master where OUTSOURCER_LEGACY_CUSTOMER_ID='66';
> why I get the error
> ERROR:  parser: parse error at or near "ANALYZE"

In that case you must be running PG 7.1 or older.  EXPLAIN's ANALYZE
option is new in 7.2.

This will also mean you need to say "VACUUM ANALYZE" to update the
planner statistics, not just "ANALYZE" as I suggested in my previous
message.

On the whole I'd recommend updating to PG 7.2.3 instead.

            regards, tom lane

Re: How to find out about index

From
Savita
Date:
Hi Everybody,

Thanks for all the help.I am able to create the indexs now but still not able to use
vaccum analyze command.I am using postgre 7.1.3 since this is our project requirement.

I have one more question,Is anybode has tried making postgre start up as window NT
service ???Is it possible to do this ???

Please give your suggestion on this.

Tom Lane wrote:

> Savita <savita@india.hp.com> writes:
> > When I type the command
> > CYCLE4#EXPLAIN ANALYZE
> > CYCLE4# select * from os_customer_master where OUTSOURCER_LEGACY_CUSTOMER_ID='66';
> > why I get the error
> > ERROR:  parser: parse error at or near "ANALYZE"
>
> In that case you must be running PG 7.1 or older.  EXPLAIN's ANALYZE
> option is new in 7.2.
>
> This will also mean you need to say "VACUUM ANALYZE" to update the
> planner statistics, not just "ANALYZE" as I suggested in my previous
> message.
>
> On the whole I'd recommend updating to PG 7.2.3 instead.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

Re: How to find out about index

From
"Shridhar Daithankar"
Date:
On 9 Oct 2002 at 11:29, Savita wrote:
> Thanks for all the help.I am able to create the indexs now but still not able to use
> vaccum analyze command.I am using postgre 7.1.3 since this is our project requirement.

I recommend you revaluate. I have seen customer insisting on RHL7.2 etc. But
frankly that doesn't make sense given 6 month cycle of typical linux distros.
Same goes for postgresql. I guess that 7.1.3 comes from what came bundled with
a particular version of a particular distro. That's not best way to evaluate
deployment requirements. IMO that is..


> I have one more question,Is anybode has tried making postgre start up as window NT
> service ???Is it possible to do this ???

Yes it is. The version is in it's beta I guess. I will post the url once I
found it.

Bye
 Shridhar

--
Death wish, n.:    The only wish that always comes true, whether or not one wishes
it to.

Re: How to find out about index

From
Savita
Date:
Thanks Shridhar

It will be very help full for me.

Shridhar Daithankar wrote:

> On 9 Oct 2002 at 11:29, Savita wrote:
> > Thanks for all the help.I am able to create the indexs now but still not able to use
> > vaccum analyze command.I am using postgre 7.1.3 since this is our project requirement.
>
> I recommend you revaluate. I have seen customer insisting on RHL7.2 etc. But
> frankly that doesn't make sense given 6 month cycle of typical linux distros.
> Same goes for postgresql. I guess that 7.1.3 comes from what came bundled with
> a particular version of a particular distro. That's not best way to evaluate
> deployment requirements. IMO that is..
>
> > I have one more question,Is anybode has tried making postgre start up as window NT
> > service ???Is it possible to do this ???
>
> Yes it is. The version is in it's beta I guess. I will post the url once I
> found it.
>
> Bye
>  Shridhar
>
> --
> Death wish, n.: The only wish that always comes true, whether or not one wishes
> it to.

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

Re: How to find out about index

From
"Shridhar Daithankar"
Date:
On 9 Oct 2002 at 11:29, Savita wrote:

> I have one more question,Is anybode has tried making postgre start up as window NT
> service ???Is it possible to do this ???

I hope you find these urls useful

http://archives.postgresql.org/pgsql-cygwin/2002-07/msg00043.php
http://www.tishler.net/jason/software/postgresql/postgresql-7.2.1.README
http://archives.postgresql.org/pgsql-cygwin/2002-08/msg00012.php
http://postgis.refractions.net/pipermail/postgis-users/2002-August/001359.html

Still I haven't found the announcement of native postgresql port. Still digging

Bye
 Shridhar

--
    "What terrible way to die."    "There are no good ways."        -- Sulu and Kirk, "That
Which Survives", stardate unknown

Re: How to find out about index

From
Savita
Date:
Hi all,

I have one more question regarding index.

I have 1 master table.
1 child table.
I am creating a view out of this two tables.

When I used explain command it is showing sqg scan on master table ,even
though I have created a index on some columns of master table

Any pointer???

Savita wrote:

> Hi all,
>
> I am new to postgre.I want to create index on some of the column.
>
> I just wanted to know that how to find out on which column we need to
> create index because using explain command I am not getting the clear
> picture as we get in oracle if we use the same explain plan command.
>
> I am unable to decide where should I create the index based on which
> criteria.
>
> Any help will be highly appreciated.
>
> --
> Best Regards
> - Savita
> ----------------------------------------------------
> Hewlett Packard (India)
> +91 80 2051288 (Phone)
> 847 1288 (HP Telnet)
> ----------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

Re: How to find out about index

From
"Shridhar Daithankar"
Date:
On 9 Oct 2002 at 14:45, Savita wrote:

> Hi all,
>
> I have one more question regarding index.
>
> I have 1 master table.
> 1 child table.
> I am creating a view out of this two tables.
>
> When I used explain command it is showing sqg scan on master table ,even
> though I have created a index on some columns of master table

May be master table has relatively few records so planner considers a
sequential scan better than indexed scan.

Bye
 Shridhar

--
union, n.:    A dues-paying club workers wield to strike management.

Problem running postgre as a windows NT service

From
Savita
Date:
Hi,

I would like to know few things about making postgre as NT service.Yesterday
with the help of this mailing list I got some good documentation about how
to create postgre as NT service.

1.I have installed the postgre and I am able to run it without making it a
service.

When I install it as a NT service.I am not able to understand how the client
will connect to it and also where to give the database name.In the
doucmentation I found that they have mentioned something like

Start the cygipc ipc-daemon:

    # net start ipc-daemon

 Start postmaster:

    # net start postmaster

Connect to PostgreSQL:

    # psql -U postgres template1

why should we do this Creating the postgre as a service it is not possible
to start it from start->sevices->control panel.If so then how the client
will give the database name to connect to the postgre.

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

Re: Problem running postgre as a windows NT service

From
"Nigel J. Andrews"
Date:
On Thu, 10 Oct 2002, Savita wrote:

> Connect to PostgreSQL:
>
>     # psql -U postgres template1
>
> why should we do this Creating the postgre as a service it is not possible
> to start it from start->sevices->control panel.If so then how the client
> will give the database name to connect to the postgre.
>

No idea about the control panel stuff but that last stage you list is nothing
to do with starting the service. psql connects to the database server it was
obviously listed in the documentation as a test to confirm the previous
commands had managed to start the server.


--
Nigel J. Andrews

Re: Problem running postgre as a windows NT service

From
Savita
Date:
Yes I understand that the psql is the command is to verify that the server is
started properly.

Once you install postgre as a NT service.WE will get two things in the control
panel.

I am able to start the service now using control panel.

But what I wanted is that  insdeed of using  psql command in command prompt is
there any other way to connect to postgre.because basically the pupose of making
postgre as a service is to avoid the use of command prompt.

Actually what I mean

"Nigel J. Andrews" wrote:

> On Thu, 10 Oct 2002, Savita wrote:
>
> > Connect to PostgreSQL:
> >
> >     # psql -U postgres template1
> >
> > why should we do this Creating the postgre as a service it is not possible
> > to start it from start->sevices->control panel.If so then how the client
> > will give the database name to connect to the postgre.
> >
>
> No idea about the control panel stuff but that last stage you list is nothing
> to do with starting the service. psql connects to the database server it was
> obviously listed in the documentation as a test to confirm the previous
> commands had managed to start the server.
>
> --
> Nigel J. Andrews

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

Re: Problem running postgre as a windows NT service

From
"Nigel J. Andrews"
Date:
On Thu, 10 Oct 2002, Savita wrote:

> Yes I understand that the psql is the command is to verify that the server is
> started properly.
>
> Once you install postgre as a NT service.WE will get two things in the control
> panel.
>
> I am able to start the service now using control panel.
>
> But what I wanted is that  insdeed of using  psql command in command prompt is
> there any other way to connect to postgre.because basically the pupose of making
> postgre as a service is to avoid the use of command prompt.

Ah. Well you need an application.

If you're trying to avoid typing then you're obviously not wanting to type
adhoc queries so you need a user interface, aka. application, that does what
you want it to. That is a custom application specific to your requirements.

In the meantime pgAdminII is probably the closest to what you want. Perhaps
even phpAdmin. I don't know where to get either of these from unfortunately.


--
Nigel J. Andrews

Re: Problem running postgre as a windows NT service

From
"Shridhar Daithankar"
Date:
On 10 Oct 2002 at 17:08, Savita wrote:

> Yes I understand that the psql is the command is to verify that the server is
> started properly.

Well, it's certainly more than that. e.g. try HTML output mode in it..;-)

> But what I wanted is that  insdeed of using  psql command in command prompt is
> there any other way to connect to postgre.because basically the pupose of making
> postgre as a service is to avoid the use of command prompt.

Use tora. I just downloaded the latest alpha version, set the network
authentication to password and bingo.. You are in..;-)

Bye
 Shridhar

--
HOW YOU CAN TELL THAT IT'S GOING TO BE A ROTTEN DAY:    #32: You call your
answering service and they've never heard of you.

Re: Problem running postgre as a windows NT service

From
Savita
Date:
Thanks Shridhar and Nigel,

I have downloaded the pgadminII and successfully able to use it with the
postmaster.

"Nigel J. Andrews" wrote:

> On Thu, 10 Oct 2002, Savita wrote:
>
> > Connect to PostgreSQL:
> >
> >     # psql -U postgres template1
> >
> > why should we do this Creating the postgre as a service it is not possible
> > to start it from start->sevices->control panel.If so then how the client
> > will give the database name to connect to the postgre.
> >
>
> No idea about the control panel stuff but that last stage you list is nothing
> to do with starting the service. psql connects to the database server it was
> obviously listed in the documentation as a test to confirm the previous
> commands had managed to start the server.
>
> --
> Nigel J. Andrews

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------