pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about CLUSTER
3+ messages / 3 participants
[nested] [flat]
* Question about CLUSTER
@ 2008-02-11 20:03 salman <salmanb@quietcaresystems.com>
2008-02-11 21:33 ` Re: [PERFORM] Question about CLUSTER Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: salman @ 2008-02-11 20:03 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-admin@postgresql.org; pgsql-performance
Hello,
I'm planning to cluster a few large tables in our database but I'm
unable to find any recommendations/documentation on best practices --
Mainly, whether it's better to use an index which has a higher idx_scan
value, a higher idx_tup_read value, or the higest idx_tup_fetch value.
I'm assuming that idx_tup_read would probably be the best choice, but
want to get other opinions before proceeding.
Can anyone point me to docs which explain this better?
-salman
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PERFORM] Question about CLUSTER
2008-02-11 20:03 Question about CLUSTER salman <salmanb@quietcaresystems.com>
@ 2008-02-11 21:33 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-12 02:42 ` Re: [PERFORM] Question about CLUSTER Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Scott Marlowe @ 2008-02-11 21:33 UTC (permalink / raw)
To: salman <salmanb@quietcaresystems.com>; +Cc: pgsql-novice@postgresql.org; pgsql-admin@postgresql.org; pgsql-performance
On Feb 11, 2008 2:03 PM, salman <salmanb@quietcaresystems.com> wrote:
> Hello,
>
> I'm planning to cluster a few large tables in our database but I'm
> unable to find any recommendations/documentation on best practices --
> Mainly, whether it's better to use an index which has a higher idx_scan
> value, a higher idx_tup_read value, or the higest idx_tup_fetch value.
>
> I'm assuming that idx_tup_read would probably be the best choice, but
> want to get other opinions before proceeding.
If you've got two indexes that are both being hit a lot, it might be
worth looking into their correlation, and if they get used a lot
together, look at creating an index on both.
But I'd guess that idx_tup_read would be a good bet.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PERFORM] Question about CLUSTER
2008-02-11 20:03 Question about CLUSTER salman <salmanb@quietcaresystems.com>
2008-02-11 21:33 ` Re: [PERFORM] Question about CLUSTER Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-02-12 02:42 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2008-02-12 02:42 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: salman <salmanb@quietcaresystems.com>; pgsql-novice@postgresql.org; pgsql-admin@postgresql.org; pgsql-performance
On Mon, Feb 11, 2008 at 03:33:37PM -0600, Scott Marlowe wrote:
> On Feb 11, 2008 2:03 PM, salman <salmanb@quietcaresystems.com> wrote:
> > I'm planning to cluster a few large tables in our database but I'm
> > unable to find any recommendations/documentation on best practices --
> > Mainly, whether it's better to use an index which has a higher idx_scan
> > value, a higher idx_tup_read value, or the higest idx_tup_fetch value.
> >
> > I'm assuming that idx_tup_read would probably be the best choice, but
> > want to get other opinions before proceeding.
>
> If you've got two indexes that are both being hit a lot, it might be
> worth looking into their correlation, and if they get used a lot
> together, look at creating an index on both.
>
> But I'd guess that idx_tup_read would be a good bet.
You might also consider the ratio idx_tup_read::float8 / idx_scan
to see which indexes access a lot of rows per scan.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-02-12 02:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-11 20:03 Question about CLUSTER salman <salmanb@quietcaresystems.com>
2008-02-11 21:33 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-12 02:42 ` Michael Fuhr <mike@fuhr.org>
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