phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sean Huo <s...@crunchyroll.com>
Subject Re: counting returns different results
Date Tue, 10 Jun 2014 23:58:46 GMT
Forget about the above two queries, it seems that phoenix count() function
has a real problem dealing with large data size.

this 30 day query returns a count of 36076

select count(1) from table t where device ='A' and date >
to_date('2014-05-09','yyyy-MM-dd') and date <
to_date('2014-06-09','yyyy-MM-dd');

while the following query for a smaller day range returns  the correct
1303743

select count(1) from table t where device ='A' and date >
to_date('2014-06-01','yyyy-MM-dd') and date <
to_date('2014-06-09','yyyy-MM-dd');

The table has about 70 million rows per day.


On Tue, Jun 10, 2014 at 2:43 PM, Sean Huo <sean@crunchyroll.com> wrote:

> The two queries seems to yield the same results for relatively small query
> size, but diverge when the query size is really big (30*70 millions rows)
>
>
> On Tue, Jun 10, 2014 at 2:07 PM, Gabriel Reid <gabriel.reid@gmail.com>
> wrote:
>
>> Hi Sean,
>>
>> That doesn't sound right -- any idea which of the queries (if either)
>> is returning the correct results?
>>
>> Any chance you could try this out on a small test table and then post
>> the relevant DDL and literal queries here?
>>
>> - Gabriel
>>
>>
>> On Tue, Jun 10, 2014 at 10:30 PM, Sean Huo <sean@crunchyroll.com> wrote:
>> > These two queries returns vastly different results:
>> >
>> > query1:
>> > select count(1) from t where date > 'certain day' and device ='A';
>> >
>> > query2:
>> >
>> > select device, count(1) from t where date >'certain day' and device ='A'
>> > group by device;
>> >
>> > assuming table t has the  composite primary key of
>> > date timestamp, and device varchar.
>> >
>> > What gives?
>> >
>>
>
>

Mime
View raw message