Re: Windows query weird result

On 30/04/13 11:58, Lutz Fischer wrote:
> On 29/04/13 19:09, Igor Neyman wrote:
>>> -----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
>>
>>
> Thanks for the reply.
>
> The difference in result also exists without the ORDER BY clause - I
> just added it to be able to compare results
> The ID is the column is the primary key and has a btree index on it.
> Also "search_id" and "rescored" both have a btree index on them as well.
>
> ============================================================
> explain analyse returns the following on the windows server "SELECT id
> FROM spectrum_match WHERE (search_id in (788,694,693,685)) AND (rescored
> IS NOT NULL)"
> Bitmap Heap Scan on spectrum_match (cost=231940.63..304503.93
> rows=27173 width=8) (actual time=12060.510..15815.395 rows=127558 loops=1)
> Recheck Cond: ((rescored IS NOT NULL) AND (search_id = ANY
> ('{788,694,693,685}'::integer[])))
> -> BitmapAnd (cost=231940.63..231940.63 rows=27173 width=0) (actual
> time=12039.576..12039.576 rows=0 loops=1)
> -> Bitmap Index Scan on spectrum_match_rescored_idx
> (cost=0.00..2775.85 rows=164484 width=0) (actual time=77.921..77.921
> rows=129614 loops=1)
> Index Cond: (rescored IS NOT NULL)
> -> Bitmap Index Scan on spectrum_match_search_id_idx
> (cost=0.00..229150.95 rows=12737388 width=0) (actual
> time=11948.351..11948.351 rows=23102766 loops=1)
> Index Cond: (search_id = ANY ('{788,694,693,685}'::integer[]))
> Total runtime: 15858.428 ms
> ---------------------------------------------------------------------------------------------------------------------------------
> With the ORDER BY clause "SELECT id FROM spectrum_match WHERE (search_id
> in (788,694,693,685)) AND (rescored IS NOT NULL) ORDER BY ID":
> Sort (cost=306530.98..306598.91 rows=27175 width=8) (actual
> time=5315.929..5324.056 rows=127558 loops=1)
> Sort Key: id
> Sort Method: quicksort Memory: 9052kB
> -> Bitmap Heap Scan on spectrum_match (cost=231960.79..304529.54
> rows=27175 width=8) (actual time=4822.312..5237.992 rows=127558 loops=1)
> Recheck Cond: ((rescored IS NOT NULL) AND (search_id = ANY
> ('{788,694,693,685}'::integer[])))
> -> BitmapAnd (cost=231960.79..231960.79 rows=27175 width=0)
> (actual time=4816.345..4816.345 rows=0 loops=1)
> -> Bitmap Index Scan on spectrum_match_rescored_idx
> (cost=0.00..2775.96 rows=164498 width=0) (actual time=31.250..31.250
> rows=129614 loops=1)
> Index Cond: (rescored IS NOT NULL)
> -> Bitmap Index Scan on spectrum_match_search_id_idx
> (cost=0.00..229171.00 rows=12738462 width=0) (actual
> time=4772.154..4772.154 rows=23102766 loops=1)
> Index Cond: (search_id = ANY
> ('{788,694,693,685}'::integer[]))
> Total runtime: 5335.294 ms
>
> ============================================================
>
> On the linux server:
> With Order BY
> Sort (cost=267980.04..268024.89 rows=17942 width=8) (actual
> time=3389.839..3454.495 rows=136698 loops=1)
> Sort Key: id
> Sort Method: external merge Disk: 2392kB
> -> Bitmap Heap Scan on spectrum_match (cost=203180.29..266712.34
> rows=17942 width=8) (actual time=2656.571..3167.559 rows=136698 loops=1)
> Recheck Cond: ((rescored IS NOT NULL) AND (search_id = ANY
> ('{788,694,693,685}'::integer[])))
> -> BitmapAnd (cost=203180.29..203180.29 rows=17942 width=0)
> (actual time=2653.507..2653.507 rows=0 loops=1)
> -> Bitmap Index Scan on spectrum_match_rescored_idx
> (cost=0.00..2114.31 rows=113921 width=0) (actual time=29.996..29.996
> rows=138834 loops=1)
> Index Cond: (rescored IS NOT NULL)
> -> Bitmap Index Scan on spectrum_match_search_id_idx
> (cost=0.00..201056.76 rows=10984549 width=0) (actual
> time=2619.712..2619.712 rows=12087163 loops=1)
> Index Cond: (search_id = ANY
> ('{788,694,693,685}'::integer[]))
> Total runtime: 3470.326 ms
> ---------------------------------------------------------------------------------------------------------------------------------
> Without Order By
> Bitmap Heap Scan on spectrum_match (cost=203180.29..266712.34
> rows=17942 width=8) (actual time=2879.347..3380.787 rows=136698 loops=1)
> Recheck Cond: ((rescored IS NOT NULL) AND (search_id = ANY
> ('{788,694,693,685}'::integer[])))
> -> BitmapAnd (cost=203180.29..203180.29 rows=17942 width=0) (actual
> time=2876.299..2876.299 rows=0 loops=1)
> -> Bitmap Index Scan on spectrum_match_rescored_idx
> (cost=0.00..2114.31 rows=113921 width=0) (actual time=30.150..30.150
> rows=138834 loops=1)
> Index Cond: (rescored IS NOT NULL)
> -> Bitmap Index Scan on spectrum_match_search_id_idx
> (cost=0.00..201056.76 rows=10984549 width=0) (actual
> time=2842.342..2842.342 rows=12087163 loops=1)
> Index Cond: (search_id = ANY ('{788,694,693,685}'::integer[]))
> Total runtime: 3396.600 ms
>
> ===============================================================
>
>
> As a site note if I just run: SELECT id FROM spectrum_match WHERE
> search_id in (788,694,693,685)
> both Windows and Linux return 12085027 results (the table is rather
> large: estimated row count: 79 million).
>
> I am rebuilding the index on the "rescored" field at the moment. Will
> come back with the result ones it's finished.
>
> Lutz
>
>

Seems like REINDEX INDEX rescored; did the trick.
Any idea how to find out whether a index is corrupted?

--
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.