Re: Windows query weird result

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Lutz Fischer
> Sent: Monday, April 29, 2013 1:52 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Windows query weird result
>
> Hi,
>
> had a bit of weird result for a query:
> SELECT id FROM spectrum_match WHERE search_id in (788,694,693,685) AND
> rescored IS NOT NULL and dynamic_rank = true ORDER BY ID;
>
> returns (among some 127K other lines):
> ...
> 32694548
> 32694860
> ...
>
> But if I change the query to:
> SELECT id FROM spectrum_match WHERE search_id in (788,694,693,685) AND
> rescored IS NOT NULL and dynamic_rank = true and id= 32694801; I get
>
> 32694801
>
> which is omitted from the previous result.
>
> The database is running under windows (I know that's bad - but we had
> reasons...).
> It only became apparent after we made a copy of the database and run it
> under Linux (Debian wheezy).
> There the first query returned 136k lines and this id was the first
> difference.
>
> Does anybody has an idea what is going on?
>
> It's postgresql 9.2.1 running under a windows 2008 R2 server
>
>
> Lutz
>
> --
> Lutz Fischer
> lfischer(at)staffmail(dot)ed(dot)ac(dot)uk
> +44 131 6517057
>
>
> The University of Edinburgh is a charitable body, registered in
> Scotland, with registration number SC005336.
>
>

" ORDER BY ID" - do you have an index in this column (ID)?
Is it being used? What "explain analyze" says?
M.b. index is corrupt. Try to rebuild it and see if this fixes the problem.

Regards,
Igor Neyman