On 30/04/13 11:58, Lutz Fischer wrote: > 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 > > Seems like REINDEX INDEX rescored; did the trick. Any idea how to find out whether a index is corrupted? -- 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
