[SQL] finding unused indexes?

[George Pavlov]

Anybody have a clever way to quickly find whether there are any unused
indexes in a PG DB? One way I have done is to take queries from the DB
log, prepend an explain to each and grep the results, but I am wondering
if there are either any index  usage stats maintained somewhere inside
Postgres or if there is a slicker/less cumbersome way of doing it. Also
indexes used by functions are hard to simulate that way.

George

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] finding unused indexes?

[Jim Buttafuoco]

check out pg_stat_user_indexes, you will need to turn on the stats collection 
in your postgresql.conf file first.

Jim


-- Original Message ---
From: "George Pavlov" <[EMAIL PROTECTED]>
To: 
Sent: Tue, 1 Aug 2006 09:05:34 -0700
Subject: [SQL] finding unused indexes?

> Anybody have a clever way to quickly find whether there are any unused
> indexes in a PG DB? One way I have done is to take queries from the DB
> log, prepend an explain to each and grep the results, but I am wondering
> if there are either any index  usage stats maintained somewhere inside
> Postgres or if there is a slicker/less cumbersome way of doing it. Also
> indexes used by functions are hard to simulate that way.
> 
> George
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
--- End of Original Message ---


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] finding unused indexes?

[Brad Nicholson]

On Tue, 2006-08-01 at 09:05 -0700, George Pavlov wrote:
> Anybody have a clever way to quickly find whether there are any unused
> indexes in a PG DB? One way I have done is to take queries from the DB
> log, prepend an explain to each and grep the results, but I am wondering
> if there are either any index  usage stats maintained somewhere inside
> Postgres or if there is a slicker/less cumbersome way of doing it. Also
> indexes used by functions are hard to simulate that way.
> 


Check out pg_stat_user_indexes, pg_stat_sys_indexes and
pg_statio_all_indexes

You can very clearly see the index usage there.  You might have to mess
with the statistics collector section in the postgresql.conf file in
order to collect the information.

Brad.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Joining a result set from four (4) tables

[Aaron Bono]

On 7/31/06, John Tregea <[EMAIL PROTECTED]> wrote:
Hi aaron,Here are the 'create table' statements. I have indicated what are theprimary and foreign keys with trailing comments.ThanksJohnAaron Bono wrote:> Can you include the table create statements with primary and foreign
> keys?  That would help a lot.CREATE TABLE resources(  serial_id numeric NOT NULL, -- << Primary Key  related_id numeric, -- << Foreign Key  host_id int4,  created timestamptz DEFAULT now(),
  modified timestamptz,  valid_from timestamp,  valid_to timestamp,  schema_name varchar(32),  grid_loc varchar(32),  name varchar(32),  status varchar(16),  description text,  comments text,
  sort_order int2,  user_id int4 DEFAULT 0,  located text,  classification varchar(32),  sequence_id int4,)CREATE TABLE actions(  serial_id numeric NOT NULL, -- primary key  related_id numeric, -- foreign key on 
resources.serial_id  host_id int4,  created timestamptz DEFAULT now(),  modified timestamptz,  valid_from timestamp,  valid_to timestamp,  name varchar(32),  status varchar(16) DEFAULT 'Active'::character varying,
  description text,  comments text,  sort_order int2 DEFAULT 0,  user_id int4 DEFAULT 0, -- User_ID of the creator  located text,  classification varchar(32),  sequence_id int4,  in_box varchar(32),
  display_group varchar(2),)CREATE TABLE policies(  serial_id numeric NOT NULL, -- primary key  related_id numeric, -- foreign key on actions.serial_id  resource_id numeric, -- foreign key on 
resources.serial_id  owner_id numeric,  authority_id int4,  created timestamptz DEFAULT now(),  modified timestamptz,  valid_from timestamp,  valid_to timestamp,  status varchar(16) DEFAULT 'Active'::character varying,
  description text,  comments text,  classification varchar(32),  user_id int4,  sequence_id int4,  inheritance text,)CREATE TABLE permissions(  serial_id numeric NOT NULL, -- primary key
  related_id numeric, -- foreign key on policies.serial_id  user_id int4, -- foreign key on users.serial_id  owner_id int4,  authority_id int4,  resource_id int4,  created timestamptz DEFAULT now(),
  modified timestamptz,  valid_from timestamp,  valid_to timestamp,  name varchar(32),  acronym varchar(6),  status varchar(16) DEFAULT 'Active'::character varying,  inheritance text,  description text,
  comments text,  sort_order int2,  user_id int4 DEFAULT 0,  located text,  classification varchar(32),  sequence_id int4,)CREATE TABLE users(  serial_id numeric NOT NULL, -- primary key
  created timestamptz DEFAULT now(),  modified timestamptz,  valid_from timestamp,  valid_to timestamp,  name varchar(64) NOT NULL,  acronym varchar(6),  status varchar(16),  inheritance text,
  description text NOT NULL,  comments text NOT NULL,  sort_order int2 NOT NULL,  clearance varchar(32) NOT NULL,  administrator bool DEFAULT false,  user_id int4 DEFAULT 0,  next_serial_id int4 DEFAULT 1,
  classification varchar(32),)First of all, I must say it is very unfortunate that all the tables have the same name for their primary key column.  It made things confusing at first when reading through the tables (the foreign key names don't match the primary keys they map to).  It also makes it more likely you will do a join improperly.
Worse, your foriegn key names are very ambiguous.  The name related_id says nothing about what table it maps to.  That means you need documentation or the foreign key definitions (are you using foreign key constraints?) to tell what is going on.
Anyway... on to solving your problem.SELECT   permission.serial_id,   resource.name,   
actions.name,   actions.classification,   actions.display_groupFROM permissionINNER JOIN policies ON (
  policies.serial_id = permission.related_id)INNER JOIN actions ON (  actions.serial_id = policies.related_id)INNER JOIN resource ON (  -- This is tricky as policies maps to resources AND actions maps to resources
  -- so the real question is which one do you do?  I did both.  policies.resource_id = resources.serial_id  AND  actions.related_id = resources.serial_id)WHERE   permission.user_id = '11' AND
   policies.status = 'Active' AND   permission.status = 'Active'AND   actions.status = 'Active'AND   resource.status = 'Active';I always discourage listing more than one table in the FROM clause.  Use INNER and OUTER JOINs - it is much easier to debug and it is somewhat self documenting.  That way, when you or another developer look at this in the future, you understand right away how the tables are being put together.
==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com==

[SQL] viewing the description of tables from python DB-API

[Daniel Joo]

Hi all,

 

Is there a way to view the list of all tables from python
(or any other languages for that matter) DB-API?  What I’m looking for is
a command similar to the meta-command ‘\d’ that works with the psql
client.  

 

Thanks very much!

 

Dan

 

Re: [SQL] viewing the description of tables from python DB-API

[Alvaro Herrera]

Daniel Joo wrote:

> Is there a way to view the list of all tables from python (or any other
> languages for that matter) DB-API?  What I'm looking for is a command
> similar to the meta-command '\d' that works with the psql client.  

Try \d under psql -E sometime.  It'll give you the query it uses.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] viewing the description of tables from python DB-API

[Rodrigo De Leon]

On 8/1/06, Daniel Joo <[EMAIL PROTECTED]> wrote:

Hi all,



Is there a way to view the list of all tables from python (or any other
languages for that matter) DB-API?  What I'm looking for is a command
similar to the meta-command '\d' that works with the psql client.



Thanks very much!



Dan


There's also the INFORMATION_SCHEMA:

http://www.postgresql.org/docs/8.1/static/information-schema.html

Regards,

Rodrigo

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Joining a result set from four (4) tables

[John Tregea]

Hi Aaron,

Thanks very much, I really appreciate both the solution and the advice 
about naming conventions. Your script worked fine and gives me an 
example of what to do for other situations as well.


Because the logic structure of this software is in the front end 
application rather than the database there is a strong need to keep the 
naming of fields generic rather than specific, I am not using 
pre-defined foreign keys at all. If I was building the database with a 
purpose specific goal I would be doing what you say. I have found though 
that when I label elements at different levels of the back end for one 
purpose, they are less transportable in the long run. In this case the 
naming conventions are actually stored in another table and applied as 
aliases when needed. That way I can change the names and labels (for a 
new client or industry) while the underlying structure remains the same. 
I hope to increase interoperability in this way as well.


Thanks again for your help.

Kind regards

John T.



Aaron Bono wrote:


First of all, I must say it is very unfortunate that all the tables 
have the same name for their primary key column.  It made things 
confusing at first when reading through the tables (the foreign key 
names don't match the primary keys they map to).  It also makes it 
more likely you will do a join improperly.


Worse, your foriegn key names are very ambiguous.  The name related_id 
says nothing about what table it maps to.  That means you need 
documentation or the foreign key definitions (are you using foreign 
key constraints?) to tell what is going on.


Anyway... on to solving your problem.

SELECT
  permission.serial_id,
  resource.name ,
  actions.name ,
  actions.classification,
  actions.display_group
FROM permission
INNER JOIN policies ON (
  policies.serial_id = permission.related_id
)
INNER JOIN actions ON (
  actions.serial_id = policies.related_id
)
INNER JOIN resource ON (
  -- This is tricky as policies maps to resources AND actions maps to 
resources

  -- so the real question is which one do you do?  I did both.
  policies.resource_id = resources.serial_id
  AND
  actions.related_id = resources.serial_id
)
WHERE
  permission.user_id = '11' AND
  policies.status = 'Active' AND
  permission.status = 'Active'AND
  actions.status = 'Active'AND
  resource.status = 'Active'
;

I always discourage listing more than one table in the FROM clause.  
Use INNER and OUTER JOINs - it is much easier to debug and it is 
somewhat self documenting.  That way, when you or another developer 
look at this in the future, you understand right away how the tables 
are being put together.


==
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
== 


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match