pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Witham <davidw@unidial.com.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Indexes and statistics
Date: Wed, 18 Feb 2004 17:23:27 +1100
Message-ID: <CFA248776934FD43847E740E43C346D199DC18@ozimelb03.ozicom.com> (raw)

Tom,

I'm running PostgreSQL 7.3.2 on Red Hat Linux 7.3 with 512Mb RAM.

The table definition is:

                    Table "public.cdr"
        Column         |         Type          | Modifiers 
-----------------------+-----------------------+-----------
 carrier_id            | integer               | not null
 file_id               | integer               | not null
 service_num           | character varying(10) | not null
 day                   | date                  | not null
 time                  | integer               | not null
 destination           | character varying(20) | not null
 duration              | integer               | not null
 charge_wholesale      | numeric(8,2)          | not null
 charge_band_id        | integer               | 
 charge_retail         | numeric(8,2)          | not null
 rate_plan_id          | integer               | not null
 item_code             | integer               | not null
 cust_id               | integer               | not null
 bill_id               | integer               | 
 prefix                | character varying(12) | 
 charge_wholesale_calc | numeric(8,2)          | 
Indexes: cdr_ix1 btree ("day"),
         cdr_ix2 btree (service_num),
         cdr_ix3 btree (cust_id),
         cdr_ix4 btree (bill_id),
         cdr_ix5 btree (carrier_id),
         cdr_ix6 btree (file_id)

Does this make it a "wide" table?

The data arrives ordered by service_num, day, time. This customer has one primary service_num that most of the calls are made from. Therefore each day a clump of CDRs will be loaded for that customer, interspersed with CDRs from all the other customers. Therefore the distribution of records for a service_num is clumpy but evenly distributed throughout the table. For a customer with a single primary number, this result applies to the customer as a whole. For a customer with many service_num's the result is a little more doubtful depending on whether their service_num's arrive sequentially or not. This would not necessarily be the case.

I hope this makes sense. Does it help any?

Thanks,
David

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, 18 February 2004 16:10
To: David Witham
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Indexes and statistics 


"David Witham" <davidw@unidial.com.au> writes:
> One of the customers is quite large (8.3% of the records):

Hmm.  Unless your rows are quite wide, a random sampling of 8.3% of the
table would be expected to visit every page of the table, probably
several times.  So the planner's cost estimates do not seem out of line
to me; an indexscan *should* be slow.  The first question to ask is why
the deviation from reality.  Are the rows for that customer ID likely to
be physically concentrated into a limited number of physical pages?
Do you have so much RAM that the whole table got swapped in, eliminating
the extra I/O that the planner is expecting?

			regards, tom lane



view thread (5+ messages)  latest in thread

Message-ID: <CFA248776934FD43847E740E43C346D199DC18@ozimelb03.ozicom.com>
Permalink:  ../CFA248776934FD43847E740E43C346D199DC18@ozimelb03.ozicom.com/
Also on:    postgresql.org/message-id/CFA248776934FD43847E740E43C346D199DC18@ozimelb03.ozicom.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: davidw@unidial.com.au, tgl@sss.pgh.pa.us
  Subject: Re: Indexes and statistics
  In-Reply-To: <CFA248776934FD43847E740E43C346D199DC18@ozimelb03.ozicom.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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