Re: Windows query weird result - Mailing list pgsql-general

On 29/04/13 19:09, Igor Neyman wrote:
>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Lutz Fischer
>> Sent: Monday, April 29, 2013 1:52 PM
>> To: pgsql-general@postgresql.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@staffmail.ed.ac.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


--
Lutz Fischer
lfischer@staffmail.ed.ac.uk
+44 131 6517057


The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.