Hi,

> I have these two queries.
> And I was comparing the speeds ,
> when I realised they were not returning the same value.
>
> Both plans are natural.
>
> select count(distinct ad.session) from accessdetails ad
> where ad.recorded > current_timestamp - 10

A COUNT(DISTINCT ad.session) is done with results from :

SELECT
ad.session
FROM
accessdetails ad
WHERE
ad.recorded > current_timestamp - 10

This means on a "filtered" resultset.

> select count(distinct ad.session) from accessdetails ad
> having max(ad.recorded) > current_timestamp - 10

A COUNT(DISTINCT ad.session) is done/shown with results from :

SELECT
ad.session
FROM
accessdetails ad

when this evaluates TRUE

(SELECT MAX(ad.session) FROM accessdetails ad) > (current_timestamp - 10)

> The first, which could be helped with an index, returns a smaller
> value than the second from the accessdetails, which is a large table
> of
> 1000s of simulatesd test users. The differance was 400 when the second
> returned 5000 counts.
>
> Anyone got an idea why they are different ?

Regards,
Arno Brinkman