pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquestion about index
5+ messages / 4 participants
[nested] [flat]
* question about index
@ 2004-12-16 10:41 Jerome Alet <alet@librelogiciel.com>
0 siblings, 2 replies; 5+ messages in thread
From: Jerome Alet @ 2004-12-16 10:41 UTC (permalink / raw)
To: pgsql-sql
Hi,
For a future databas, I plan to have got a table with a text field
which can contain only three different values, say "VALUE1",
"VALUE2", and "VALUE3"
this table may have, over the course of one year, several million
rows for a size around 2 Gb or more.
I'd be interested in having :
SELECT count(*) AS nbvalue1 FROM mytable WHERE myfield='VALUE1';
SELECT count(*) AS nbvalue2 FROM mytable WHERE myfield='VALUE2';
SELECT count(*) AS nbvalue3 FROM mytable WHERE myfield='VALUE3';
be as fast as possible.
considering that almost 70% of the rows will be with 'VALUE1', 20%
will be with 'VALUE2' and 10% will be with 'VALUE3' on the average.
should I create an index to speedup the counts or not ?
any idea of the impact of running these three queries every 5 minutes
on say 10 000 000 rows ?
thanks in advance
Jerome Alet
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [despammed] question about index
@ 2004-12-16 11:01 Andreas Kretschmer <akretschmer@despammed.com>
parent: Jerome Alet <alet@librelogiciel.com>
1 sibling, 1 reply; 5+ messages in thread
From: Andreas Kretschmer @ 2004-12-16 11:01 UTC (permalink / raw)
To: pgsql-sql
am 16.12.2004, um 11:41:54 +0100 mailte Jerome Alet folgendes:
> Hi,
>
> For a future databas, I plan to have got a table with a text field
> which can contain only three different values, say "VALUE1",
> "VALUE2", and "VALUE3"
Why text-fields for this task? I would prefer a smallint for this with a
check-contraint to check the values.
> be as fast as possible.
>
> considering that almost 70% of the rows will be with 'VALUE1', 20%
> will be with 'VALUE2' and 10% will be with 'VALUE3' on the average.
>
> should I create an index to speedup the counts or not ?
Yes.
Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Tel. NL Heynitz: 035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: question about index
@ 2004-12-16 11:30 D'Arcy J.M. Cain <darcy@druid.net>
parent: Jerome Alet <alet@librelogiciel.com>
1 sibling, 0 replies; 5+ messages in thread
From: D'Arcy J.M. Cain @ 2004-12-16 11:30 UTC (permalink / raw)
To: Jerome Alet <alet@librelogiciel.com>; +Cc: pgsql-sql
On Thu, 16 Dec 2004 11:41:54 +0100
Jerome Alet <alet@librelogiciel.com> wrote:
> For a future databas, I plan to have got a table with a text field
> which can contain only three different values, say "VALUE1",
> "VALUE2", and "VALUE3"
Can it increase? That is, can a "VALUE4" be added half way through the
year?
> this table may have, over the course of one year, several million
> rows for a size around 2 Gb or more.
Doing a COUNT(*) on that many rows could get expensive but...
> I'd be interested in having :
>
> SELECT count(*) AS nbvalue1 FROM mytable WHERE
> myfield='VALUE1'; SELECT count(*) AS nbvalue2 FROM mytable
> WHERE myfield='VALUE2'; SELECT count(*) AS nbvalue3 FROM
> mytable WHERE myfield='VALUE3';
First of all, consider doing it in one statement so that you at least
are not running through it multiple times.
SELECT myfield, COUNT(*) FROM mytable GROUP BY myfield;
Second, consider rules or triggers to keep a separate table up to date
at all times so that you simply have to dump a three row table instead
of going through a huge table counting as you go. We did that in a
similar situation and it made a huge difference. We were doing SUM()
instead of COUNT() and we were calculating an extremely small percentage
of the table - average probably 3 or 4 rows out of 20 million on average
- but it still was worth our while to calculate the sum (balance) on
every transaction rather than calculate it every time. You need to
analyze your own data and usage but this may be a better solution for
you.
As usual, if not completely satisfied you get a full refund.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [despammed] question about index
@ 2004-12-16 14:10 Bruno Wolff III <bruno@wolff.to>
parent: Andreas Kretschmer <akretschmer@despammed.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2004-12-16 14:10 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@despammed.com>; +Cc: pgsql-sql
On Thu, Dec 16, 2004 at 12:01:39 +0100,
Andreas Kretschmer <akretschmer@despammed.com> wrote:
> >
> > considering that almost 70% of the rows will be with 'VALUE1', 20%
> > will be with 'VALUE2' and 10% will be with 'VALUE3' on the average.
> >
> > should I create an index to speedup the counts or not ?
>
> Yes.
Indexes will probably not help in this case. Even when looking at 10% of
the rows, a sequential scan is still going to be faster than an index
scan in most cases.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [despammed] question about index
@ 2004-12-16 14:15 Andreas Kretschmer <akretschmer@despammed.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: Andreas Kretschmer @ 2004-12-16 14:15 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas Kretschmer <akretschmer@despammed.com>
am 16.12.2004, um 8:10:25 -0600 mailte Bruno Wolff III folgendes:
> On Thu, Dec 16, 2004 at 12:01:39 +0100,
> Andreas Kretschmer <akretschmer@despammed.com> wrote:
> > >
> > > considering that almost 70% of the rows will be with 'VALUE1', 20%
> > > will be with 'VALUE2' and 10% will be with 'VALUE3' on the average.
> > >
> > > should I create an index to speedup the counts or not ?
> >
> > Yes.
>
> Indexes will probably not help in this case. Even when looking at 10% of
> the rows, a sequential scan is still going to be faster than an index
> scan in most cases.
Okay.
Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Tel. NL Heynitz: 035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-12-16 14:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-16 10:41 question about index Jerome Alet <alet@librelogiciel.com>
2004-12-16 11:01 ` Andreas Kretschmer <akretschmer@despammed.com>
2004-12-16 14:10 ` Bruno Wolff III <bruno@wolff.to>
2004-12-16 14:15 ` Andreas Kretschmer <akretschmer@despammed.com>
2004-12-16 11:30 ` D'Arcy J.M. Cain <darcy@druid.net>
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