Custom index structure and strange count problem
От | Carsten Kropf |
---|---|
Тема | Custom index structure and strange count problem |
Дата | |
Msg-id | 965BE573-CEE9-455E-9800-1617EAC89990@fh-hof.de обсуждение исходный текст |
Ответы |
Re: Custom index structure and strange count problem
|
Список | pgsql-hackers |
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 theindex 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 ------2137215121682207220822092210221122662296 (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 ------2137215121682207220822092210221122662296 (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 followingresult: 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 countthe 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 Carsten Kropf
В списке pgsql-hackers по дате отправления: