pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
When NOT to index small tables?
2+ messages / 2 participants
[nested] [flat]

* When NOT to index small tables?
@ 2003-08-20 10:01 alvis@piladzi-2.biz
  2003-08-20 13:36 ` Re: When NOT to index small tables? Bruno Wolff III <bruno@wolff.to>
  0 siblings, 1 reply; 2+ messages in thread

From: alvis@piladzi-2.biz @ 2003-08-20 10:01 UTC (permalink / raw)
  To: pgsql-performance

I have found many reasons not to index small tables (see .
But I still have questions.

1. How small is small enough?
2. am I supposed to drop primary key index on so called 'label tables'
knowing I am using this pk in join clause?
3. Is it good to drop index on small table If I know I have created VIEW and
joined large table and the small one
and I have where condition on that particular colum?
Ilustrative example:
table small
{
id -- primary key
colum_for_where_condition -- index or not to index?
}
table large
{
...
fk_for_small_table -- indexed
...
}
create view as select .....
inner join on common_column ..
I have migrated from mysql .
I have found that complex view expression takes about 10 sec using ordinary
planner,
but genetic alg takes about 5 sec. Strange?
I can give details if someone is interested.

Regards,
Alvis





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

* Re: When NOT to index small tables?
  2003-08-20 10:01 When NOT to index small tables? alvis@piladzi-2.biz
@ 2003-08-20 13:36 ` Bruno Wolff III <bruno@wolff.to>
  0 siblings, 0 replies; 2+ messages in thread

From: Bruno Wolff III @ 2003-08-20 13:36 UTC (permalink / raw)
  To: alvis@piladzi-2.biz; +Cc: pgsql-performance

On Wed, Aug 20, 2003 at 13:01:16 +0300,
  alvis@piladzi-2.biz wrote:
> I have found many reasons not to index small tables (see .
> But I still have questions.
> 
> 1. How small is small enough?

Unless you think maintaining the indexes is a significant overhead, you
shouldn't worry about it as the planner will choose whether or not to
use them according to what it thinks is faster.

> 2. am I supposed to drop primary key index on so called 'label tables'
> knowing I am using this pk in join clause?

I don't think you want to drop any indexes that are used to enforce
constraints.

> 3. Is it good to drop index on small table If I know I have created VIEW and
> joined large table and the small one
> and I have where condition on that particular colum?

If you have some other reason for creating the index, you probably don't
want to drop it to try to speed up queries. If there isn't some reason you
have for keeping the index, then you might want to drop it if it isn't
helping your queries as maintaining the index will slow down queries
that modify the table.



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


end of thread, other threads:[~2003-08-20 13:36 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-20 10:01 When NOT to index small tables? alvis@piladzi-2.biz
2003-08-20 13:36 ` Bruno Wolff III <bruno@wolff.to>

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