Another "why am I not using the indicies?"
Here is all the information about the system and the steps I took. If you
can tell me or point me in the right direction I would appreciate it. There
are 624306 records in this table, so it only takes a few seconds, but this
is going to grow to 5M rows/month when this works properly.
Clinton James
#select version();
version
----------------------------------------------------------------------
PostgreSQL 7.1rc4 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
#CREATE TABLE ld (
billmonth CHAR(4),
cust_num CHAR(8),
location CHAR(4),
date TIMESTAMP,
orignum CHAR(10),
destnum CHAR(13),
destcity CHAR(10),
deststate CHAR(2),
duration NUMERIC(8,1),
callcode CHAR(9),
cost NUMERIC(10,4)
);
#copy ld from '/tmp/ld0104.txt';
#create index ld_custnum_idx ON ld(cust_num);
#create index ld_orignum_idx ON ld(orignum);
#vacuum analyze;
# explain select * from ld where cust_num = '10102899';
NOTICE: QUERY PLAN:
Seq Scan on ld (cost=0.00..20810.83 rows=8989 width=128)
"Clinton James" <cjames@callone.net> writes:
# explain select * from ld where cust_num = '10102899';
NOTICE: QUERY PLAN:
Seq Scan on ld (cost=0.00..20810.83 rows=8989 width=128)
The reason it's not using an indexscan is that it thinks it's going to
find 8989 matches (and if that were true, a seqscan probably *is* the
right plan). You must have some extremely-often-repeated entries in
that column; what are they, and can you get rid of them?
regards, tom lane
"Clinton James" <cjames@callone.net> writes:
True, using a different cust_num, it is possible there may be a couple of
cust_num where that would be true. The majority will not have even near
that many. In this case there are only 13 matching records. Is VACUUMDB
ANALYZE using the worst case (the cust_num with the greatest number of rows)
senario for this?
Yes, because that's as far as the statistics go at the moment :-(.
The frequency of the most common value is basically all the info the
planner has about the data distribution, and so a highly skewed
distribution leads to bad plan choices.
I am presently working on better statistics ... to appear in 7.2 ...
regards, tom lane
Import Notes
Reply to msg id not found: LDEHLKIELLEDPDLPHDILGEBLCHAA.cjames@callone.netReference msg id not found: LDEHLKIELLEDPDLPHDILGEBLCHAA.cjames@callone.net | Resolved by subject fallback