On 29/04/13 19:09, Igor Neyman wrote: > >> -----Original Message----- >> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- >> owner@xxxxxxxxxxxxxx] On Behalf Of Lutz Fischer >> Sent: Monday, April 29, 2013 1:52 PM >> To: pgsql-general@xxxxxxxxxxxxxx >> Subject: 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@xxxxxxxxxxxxxxxxxx >> +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@xxxxxxxxxxxxxxxxxx +44 131 6517057 The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
