Thread: 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.
> -----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
The problem is that I need not 3 rows but 1360000 rows. So "id=" is not really an option. Tried putting each condition in brackets - but that did not help either.
But you are right in that it looks like the NULL values are the problem. Leaving that condition out both windows and linux server return the same amount of values...
On 29/04/13 22:01, Aaron Abreu wrote:
But you are right in that it looks like the NULL values are the problem. Leaving that condition out both windows and linux server return the same amount of values...
On 29/04/13 22:01, Aaron Abreu wrote:
I would add each of your where fields to the select,then use all 3 id numbers in the id=I also run from windows server 2008, and sometimeshave trouble getting it to accept records with nulls.or, try putting EACH where condition enclosed inits own ( ) set.On Mon, Apr 29, 2013 at 12:52 PM, Lutz Fischer <lfischer@staffmail.ed.ac.uk> wrote: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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general--####Aaron Abreu, Systems Consultant
Bay District Schools, Panama City, FLOffice: (850) 767-4288
>>FOCUS Student system support>>IRIS phone alert system support
ABREUAL@bay.k12.fl.usThe information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Under Florida law, e-mail addresses are public records. If you do not want your e-mail address released in response to a public-records request, do not send electronic mail to this entity. Instead, contact this office by phone or in writing.
-- Lutz Fischer lfischer@staffmail.ed.ac.uk +44 131 6517057
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.
On 30/04/13 11:58, Lutz Fischer wrote:
> 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
>
>
Seems like REINDEX INDEX rescored; did the trick.
Any idea how to find out whether a index is corrupted?
--
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.
> > > > Seems like REINDEX INDEX rescored; did the trick. > Any idea how to find out whether a index is corrupted? > > -- > 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. The only way I know is to execute SELECT on the table that will cause full index scan. Regards, Igor Neyman
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Igor Neyman > Sent: Tuesday, April 30, 2013 9:30 AM > To: Lutz Fischer; pgsql-general@postgresql.org > Cc: Aaron Abreu > Subject: Re: [GENERAL] Windows query weird result > > > > > > > > Seems like REINDEX INDEX rescored; did the trick. > > Any idea how to find out whether a index is corrupted? > > > > -- > > 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. > > The only way I know is to execute SELECT on the table that will cause > full index scan. > > Regards, > Igor Neyman > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To > make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general As a side not, one more thing. Since you mentioned that corruption happened in Windows environment, I'd suggest that Postgres data directory should be excluded from being scanned by whatever anti-virus software you have runningon that system (I suspect there is one). Scanning PG data directory by anti-virus software could reduce performanceand also in "severe" cases - corruption. Regards, Igor Neyman
On 30/04/13 14:49, Igor Neyman wrote: > >> -----Original Message----- >> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- >> owner@postgresql.org] On Behalf Of Igor Neyman >> Sent: Tuesday, April 30, 2013 9:30 AM >> To: Lutz Fischer; pgsql-general@postgresql.org >> Cc: Aaron Abreu >> Subject: Re: [GENERAL] Windows query weird result >> >>> Seems like REINDEX INDEX rescored; did the trick. >>> Any idea how to find out whether a index is corrupted? >>> >>> -- >>> 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. >> The only way I know is to execute SELECT on the table that will cause >> full index scan. >> >> Regards, >> Igor Neyman >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To >> make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > As a side not, one more thing. > Since you mentioned that corruption happened in Windows environment, > I'd suggest that Postgres data directory should be excluded from being scanned by whatever anti-virus software you haverunning on that system (I suspect there is one). Scanning PG data directory by anti-virus software could reduce performanceand also in "severe" cases - corruption. > > Regards, > Igor Neyman > Thanks a lot. -- 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.