From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Witham <davidw@unidial.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Indexes and statistics
Date: Wed, 18 Feb 2004 10:21:31 -0500
Message-ID: <23562.1077117691@sss.pgh.pa.us> (raw)
In-Reply-To: <CFA248776934FD43847E740E43C346D199DC18@ozimelb03.ozicom.com>
References: <CFA248776934FD43847E740E43C346D199DC18@ozimelb03.ozicom.com>
"David Witham" <davidw@unidial.com.au> writes:
> Does this make it a "wide" table?
Nope. A rough estimate is that your rows will be about 160 bytes wide,
which means you can fit about 50 per 8K page. So a query that needs to
select 8% of the table will *on average* need to hit about 4 rows per
page. In the absence of any data clumping this would certainly mean
that the scan would need to touch every page anyway, and thus that using
the index could provide no I/O savings. However,
> The data arrives ordered by service_num, day, time. This customer has
> one primary service_num that most of the calls are made from.
So you do have very strong clumping, which the planner is evidently
failing to account for properly. Could we see the pg_stats rows for
service_num and cust_id? I'm curious whether the ANALYZE stats picked
up the effect at all.
As far as actually solving the problem is concerned, you have a few
options. I wouldn't recommend turning off enable_seqscan globally,
but you could perhaps turn it off locally (just do a SET command) just
for this query. Another possibility, if you care a lot about the speed
of this particular type of query, is to make a partial index tuned to
the query:
create index my_idx on cdr (cust_id) WHERE bill_id IS NULL;
I gather from your previously shown results that "bill_id IS NULL"
covers only a small fraction of the table, so this index would be pretty
small and should look quite attractive to the planner.
regards, tom lane
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: tgl@sss.pgh.pa.us, davidw@unidial.com.au
Subject: Re: Indexes and statistics
In-Reply-To: <23562.1077117691@sss.pgh.pa.us>
* 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