pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Cleaning up indexes
3+ messages / 3 participants
[nested] [flat]

* Cleaning up indexes
@ 2004-09-23 22:16 Martin Foster <martin@ethereal-realms.org>
  2004-09-26 01:28 ` Re: Cleaning up indexes Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Martin Foster @ 2004-09-23 22:16 UTC (permalink / raw)
  To: pgsql-performance

My database was converted from MySQL a while back and has maintained all 
of the indexes which were previously used.   Tt the time however, there 
were limitations on the way PostgreSQL handled the indexes compared to 
MySQL.

Meaning that under MySQL, it would make use of a multi-column index even 
if the rows within did not match.    When the conversion was made more 
indexes were created overall to correct this and proceed with the 
conversion.

Now the time has come to clean up the used indexes.   Essentially, I 
want to know if there is a way in which to determine which indexes are 
being used and which are not.   This will allow me to drop off the 
unneeded ones and reduce database load as a result.

And have things changed as to allow for mismatched multi-column indexes 
in version 7.4.x or even the upcoming 8.0.x?

	Martin Foster
	martin@ethereal-realms.org



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Cleaning up indexes
  2004-09-23 22:16 Cleaning up indexes Martin Foster <martin@ethereal-realms.org>
@ 2004-09-26 01:28 ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Bruce Momjian @ 2004-09-26 01:28 UTC (permalink / raw)
  To: Martin Foster <martin@ethereal-realms.org>; +Cc: pgsql-performance

Martin Foster wrote:
> My database was converted from MySQL a while back and has maintained all 
> of the indexes which were previously used.   Tt the time however, there 
> were limitations on the way PostgreSQL handled the indexes compared to 
> MySQL.
> 
> Meaning that under MySQL, it would make use of a multi-column index even 
> if the rows within did not match.    When the conversion was made more 
> indexes were created overall to correct this and proceed with the 
> conversion.
> 
> Now the time has come to clean up the used indexes.   Essentially, I 
> want to know if there is a way in which to determine which indexes are 
> being used and which are not.   This will allow me to drop off the 
> unneeded ones and reduce database load as a result.

Just for clarification, PostgreSQL will use an a,b,c index for a, (a,b),
and (a,b,c), but not for (a,c).  Are you saying MySQL uses the index for
(a,c)?  This item is on our TODO list:
	
	* Use index to restrict rows returned by multi-key index when used with
	  non-consecutive keys to reduce heap accesses
	
	  For an index on col1,col2,col3, and a WHERE clause of col1 = 5 and
	  col3 = 9, spin though the index checking for col1 and col3 matches,
	  rather than just col1
	
> And have things changed as to allow for mismatched multi-column indexes 
> in version 7.4.x or even the upcoming 8.0.x?

As someone already pointed out, the pg_stat* tables will show you what
indexes are used.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Cleaning up indexes
@ 2004-09-23 23:05 Gregory S. Williamson <gsw@globexplorer.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Gregory S. Williamson @ 2004-09-23 23:05 UTC (permalink / raw)
  To: Martin Foster <martin@ethereal-realms.org>; pgsql-performance


If you have set up the postgres instance to write stats, the tables pg_stat_user_indexes, pg_statio_all_indexes and so (use the \dS option at the psql prompt to see these system tables); also check the pg_stat_user_tables table and similar beasts for information on total access, etc. Between these you can get a good idea of what indexes are not being used, and from the sequentail scan info on tables perhaps some idea of what may need some indexes.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC 

-----Original Message-----
From:	Martin Foster [mailto:martin@ethereal-realms.org]
Sent:	Thu 9/23/2004 3:16 PM
To:	pgsql-performance@postgresql.org
Cc:	
Subject:	[PERFORM] Cleaning up indexes
My database was converted from MySQL a while back and has maintained all 
of the indexes which were previously used.   Tt the time however, there 
were limitations on the way PostgreSQL handled the indexes compared to 
MySQL.

Meaning that under MySQL, it would make use of a multi-column index even 
if the rows within did not match.    When the conversion was made more 
indexes were created overall to correct this and proceed with the 
conversion.

Now the time has come to clean up the used indexes.   Essentially, I 
want to know if there is a way in which to determine which indexes are 
being used and which are not.   This will allow me to drop off the 
unneeded ones and reduce database load as a result.

And have things changed as to allow for mismatched multi-column indexes 
in version 7.4.x or even the upcoming 8.0.x?

	Martin Foster
	martin@ethereal-realms.org

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-09-26 01:28 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-23 22:16 Cleaning up indexes Martin Foster <martin@ethereal-realms.org>
2004-09-26 01:28 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-09-23 23:05 Re: Cleaning up indexes Gregory S. Williamson <gsw@globexplorer.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox