count distinct optimisation
Hi,
I have a simple query which reads:
select count(distinct col_a) from tab_a;
with no predicates. The column 'col_a' is the first column in a 3 column
composite index. The table is fragmented (using round robin fragmentation)
over 7 disks, the index is on an 8th disk. Statistics have been collected as
recommended in the performance manual (as amended by the release notes) for
for our version of IDS (7.30 UC7). The table has approx 30 million rows.
Each fragment has about 400,000 pages, the index uses about 420,000 pages.
Running the query on a 4 cpu HP-UX K series server, with PDQPRIORITY=25, the
query ignores the index and takes about 35 minutes. It uses a significant
amount of temporary dbspace - about 2 Gbytes(we have 8 temp dbspaces, one on
each disk - these disks are different to the those used by the data
dbspaces). I guess the query extracts the column values, then sorts them,
then removes duplicates.
Watching the query via xtree scanning the data takes about 6 minutes, the
sorting and duplicate removal takes the other 30, or so, minutes.
Even using optimiser directives (yuk!) I can't get the query to extract the
data from the index leaf pages. I realise the data can't be scanned in
parallel, but the index is only slightly larger than one data fragment.
Surely, by accessing the data this way, the column values I want are in
sequence, so no sorting should be required, except maybe within a page (I
can't remember of index values are kept in strict sequence within a page)?
Since I'm only after a count of distinct values, the whole thing should be
runnable within a tiny amount of memory, not 2 Gbytes of temp dbspace.
Any suggestions?
Martyn Hodgson
martyn.hodgson@zurich.co.uk
No guess about that (where are the optimizer specialists ?)
I would suggest the following way:
select "your_column" from "your_table"
insert into temp "key_table" with no log;
select count(distinct "your_column")
from "key_table"
That should read from index only and fill a temp
table with no logging, which will reduce space required.
Hth,
Chris
> Since I'm only after a count of distinct values, the whole thing should be
> runnable within a tiny amount of memory, not 2 Gbytes of temp dbspace.
>
> Any suggestions?
>
> Martyn Hodgson
> martyn.hodgson@zurich.co.uk
The only thought I have is really an aside. If you do not see multiple
sort threads then you can try setting the PSORT parameters which will
improve the speed of the sort/filter operation tremendously:
PSORT_NPROCS=20
PSORT_DBTEMP=filesystem_A:filesystem_B:filesystem_C
export PSORT_DBTEMP PSORT_NPROCS
The docs say that PSORT can only allocate up to 10 sort threads but I have
seen up to 40 myself, so as my once good friend used to say: "I'll see it
when I believe it!". Set PSORT_DBTEMP to AT LEAST three filesystem (NOT
dbspaces!) which each have enough space for at least 1/nth of the data
being sorted as they are filled evenly and if any one hits zero free the
whole query will fail. Some claim 4 filesystems makes some improvement, I
doubt you gain speed from more than that but two is definitely slower than
three or four filesystems due to the way Informix performs merging of the
sort-work files. You could get this down under 10 seconds this way.
Art S. Kagel
Martyn Hodgson wrote:
>
> Hi,
>
> I have a simple query which reads:
>
> select count(distinct col_a) from tab_a;>
> with no predicates. The column 'col_a' is the first column in a 3 column
> composite index. The table is fragmented (using round robin fragmentation)
> over 7 disks, the index is on an 8th disk. Statistics have been collected as
> recommended in the performance manual (as amended by the release notes) for
> for our version of IDS (7.30 UC7). The table has approx 30 million rows.
> Each fragment has about 400,000 pages, the index uses about 420,000 pages.
>
> Running the query on a 4 cpu HP-UX K series server, with PDQPRIORITY=25, the
> query ignores the index and takes about 35 minutes. It uses a significant
> amount of temporary dbspace - about 2 Gbytes(we have 8 temp dbspaces, one on
> each disk - these disks are different to the those used by the data
> dbspaces). I guess the query extracts the column values, then sorts them,
> then removes duplicates.
>
> Watching the query via xtree scanning the data takes about 6 minutes, the
> sorting and duplicate removal takes the other 30, or so, minutes.
>
> Even using optimiser directives (yuk!) I can't get the query to extract the
> data from the index leaf pages. I realise the data can't be scanned in
> parallel, but the index is only slightly larger than one data fragment.
> Surely, by accessing the data this way, the column values I want are in
> sequence, so no sorting should be required, except maybe within a page (I
> can't remember of index values are kept in strict sequence within a page)?
> Since I'm only after a count of distinct values, the whole thing should be
> runnable within a tiny amount of memory, not 2 Gbytes of temp dbspace.
>
> Any suggestions?
>
> Martyn Hodgson
> martyn.hodgson@zurich.co.uk
In article <7umeis$mp8$1@news.xmission.com>, Martyn Hodgson <martyn.hodg
son@eaglestar.co.uk> writes
>
>Hi,
>
>I have a simple query which reads:
>
>select count(distinct col_a) from tab_a;>
>with no predicates. The column 'col_a' is the first column in a 3 column
>composite index. The table is fragmented (using round robin fragmentation)
>over 7 disks, the index is on an 8th disk. Statistics have been collected as
>recommended in the performance manual (as amended by the release notes) for
>for our version of IDS (7.30 UC7). The table has approx 30 million rows.
>Each fragment has about 400,000 pages, the index uses about 420,000 pages.
>
>Running the query on a 4 cpu HP-UX K series server, with PDQPRIORITY=25, the
>query ignores the index and takes about 35 minutes. It uses a significant
>amount of temporary dbspace - about 2 Gbytes(we have 8 temp dbspaces, one on
>each disk - these disks are different to the those used by the data
>dbspaces). I guess the query extracts the column values, then sorts them,
>then removes duplicates.
>
>Watching the query via xtree scanning the data takes about 6 minutes, the
>sorting and duplicate removal takes the other 30, or so, minutes.
>
>Even using optimiser directives (yuk!) I can't get the query to extract the
>data from the index leaf pages. I realise the data can't be scanned in
>parallel, but the index is only slightly larger than one data fragment.
>Surely, by accessing the data this way, the column values I want are in
>sequence, so no sorting should be required, except maybe within a page (I
>can't remember of index values are kept in strict sequence within a page)?
>Since I'm only after a count of distinct values, the whole thing should be
>runnable within a tiny amount of memory, not 2 Gbytes of temp dbspace.
>
>Any suggestions?
>
...
>our version of IDS (7.30 UC7)
Try IDS 7.31.UC3-1, the latest.
Try creating a single column index on the column and try again.
>Martyn Hodgson
>martyn.hodgson@zurich.co.uk
>
--
David Williams