Re: Custom index structure and strange count problem

On Wed, Jun 9, 2010 at 4:35 AM, Carsten Kropf <ckropf2(at)fh-hof(dot)de> wrote:
> Hi *,
> during the last few months I've been building a new index structure as part of a research project.
> Everything seems to work properly, however I have some strange issues with the count sql command.
> I introduced some custom structures (mainly document and hybrid_query) with which my index access method is supposed to work.
> There is an operator "&&" which is supposed to use my index structure (what also works properly).
> The function that maps to the operator "&&" is called hybrid_index_query, which I use to compare my results given from the index with the real results that are supposed to appear in the final result set.
> Having described the outer circumstances (in a very short way), I will now show the strange stuff that happens:
>
> test=# select id from documents where hybrid_index_query(to_document(words, points), row('radio pleas news'::tsvector, '[(-90,-180),(90, 180)]')) order by id;
>  id
> ------
>  2137
>  2151
>  2168
>  2207
>  2208
>  2209
>  2210
>  2211
>  2266
>  2296
> (10 rows)
>
> This query takes a sequential scan and works properly (returning 10 rows).
>
> test=# select id from documents where to_document(words, points) && row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]') order by id;
>  id
> ------
>  2137
>  2151
>  2168
>  2207
>  2208
>  2209
>  2210
>  2211
>  2266
>  2296
> (10 rows)
>
> This query uses my index structure and returns the same result as in the sequential scan above.
> Until here, everything seems to work fine. However, if I issue the same queries using the count aggregate function in SQL, there are some odd results:
> test=# select count(*) from documents where hybrid_index_query(to_document(words, points), row('radio pleas news'::tsvector, '[(-90,-180),(90, 180)]'));
>  count
> -------
>    10
> (1 row)
>
> Using the sequential scan, still, everything seems fine.
> However, if I now do the index scan (my function will be called 11 times, returning false at the end), I get the following result:
> test=# select count(*) from documents where to_document(words, points) && row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]');
>  count
> -------
>     7
> (1 row)
>
> This seems strange, because the same query returned 10 rows (when I didn't use the aggregate). If I issue queries that count the id column, I receive the following:
> test=# select count(id) from documents where hybrid_index_query(to_document(words, points), row('radio pleas news'::tsvector, '[(-90,-180),(90, 180)]'));
>  count
> -------
>    10
> (1 row)
>
> test=# select count(id) from documents where to_document(words, points) && row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]');
>  count
> -------
>    10
> (1 row)
>
> These two queries do again return the same results.
> Thus, I don't know, what's wrong here, does anybody know about that behaviour, or is it my fault that the results are wrong, somehow?
> Thanks in advance

I am guessing this is a bug in your code - have you used EXPLAIN to
verify that the second-to-last of the above queries is really hitting
your code? If so, I'd recommend attaching with gdb and setting a
breakpoint wherever you return the tuples, and then poke around...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company