Re: Unable to match same value in field. - Mailing list pgsql-general

On 03/11/2016 12:19 AM, Condor wrote:
> On 10-03-2016 15:37, Adrian Klaver wrote:
>> On 03/10/2016 01:09 AM, Condor wrote:
>>>
>>> Hello,
>>>
>>> I using postgresql 9.5.1 and I have problem to match value in one field.
>>> Both tables are text:
>>>
>>> =# \d list_cards_tbl;
>>>
>>>    Column   |  Type   |                          Modifiers
>>> -----------+---------+--------------------------------------------------------------
>>>
>>>
>>>   recid     | integer | not null default
>>> nextval('list_cards_tbl_recid_seq'::regclass)
>>>   imsi      | text    |
>>> Indexes:
>>>      "imsi_list_cards_tbl" btree (imsi)
>>>
>>>
>>> =# \d list_newcard_tbl;
>>>     Column   |  Type   |                           Modifiers
>>> ------------+---------+---------------------------------------------------------------
>>>
>>>
>>>   recid      | integer | not null default
>>> nextval('list_newcard_tbl_recid_seq'::regclass)
>>>   serial     | text    |
>>>   imsi       | text    |
>>> Indexes:
>>>      "list_newcard_tbl_pkey" PRIMARY KEY, btree (recid)
>>>      "list_newcard_ser_idx" btree (serial)
>>>
>>>
>>>
>>> =# select imsi, md5(imsi), bit_length(imsi) from list_newcards_tbl where
>>> imsi = '284110000123315';
>>>        imsi       |               md5                | bit_length
>>> -----------------+----------------------------------+------------
>>>   284110000123315 | b438e984c97483bb942eaaed5c0147f3 |        120
>>> (1 row)
>>>
>>>
>>>
>>> So far so good, value of table list_newcard_tbl is fine, problem is in
>>> table list_cards_tbl
>>>
>>> =# select imsi from list_cards_tbl where imsi = '284110000123315';
>>>   imsi
>>> ------
>>> (0 rows)
>>>
>>> No value, lets change to LIKE
>>>
>>> =# select imsi, md5(imsi), bit_length(imsi) from list_cards_tbl where
>>> imsi like '284110000123315%';
>>>        imsi       |               md5                | bit_length
>>> -----------------+----------------------------------+------------
>>>   284110000123315 | b438e984c97483bb942eaaed5c0147f3 |        120
>>> (1 row)
>>>
>>>
>>> Both have the same MD5 sum, also bit length.
>>>
>>> With EXPLAIN:
>>>
>>> =# explain analyse select imsi from list_cards_tbl where imsi =
>>> '284110000123315';
>>>                                                                QUERY
>>> PLAN
>>>
--------------------------------------------------------------------------------------------------------------------------------------
>>>
>>>
>>>   Index Only Scan using imsi_list_card_tbl on list_cards_tbl
>>> (cost=0.28..4.30 rows=1 width=16) (actual time=0.021..0.021 rows=0
>>> loops=1)
>>>     Index Cond: (imsi = '284110000123315'::text)
>>>     Heap Fetches: 0
>>>   Planning time: 0.080 ms
>>>   Execution time: 0.045 ms
>>> (5 rows)
>>>
>>> I see only index scan, so I do:
>>>
>>> =# reindex table list_cards_tbl;
>>> REINDEX
>>> =# vacuum list_cards_tbl;
>>> VACUUM
>>> =# select imsi, md5(imsi), bit_length(imsi) from list_cards_tbl where
>>> imsi = '284110000123315';
>>>   imsi | md5 | bit_length
>>> ------+-----+------------
>>> (0 rows)
>>>
>>>
>>> Still cant find value.
>>>
>>
>> So is the above the only value that is hidden?
>>
>> What happens if for a session you do?:
>>
>> SET enable_indexonlyscan=OFF;
>>
>> Basically a variation of Karsten's idea
>>
>> Is the same process populating both tables?
>>
>> Where is the data coming from?
>>
>> Lastly, what happens if you populate the field in list_cards_tbl with
>> the data from list_newcards_tbl?
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
>
> =# SET enable_indexonlyscan=OFF;
> SET
> =# select imsi, md5(imsi), bit_length(imsi) from list_cards_tbl where
> imsi = '284110000123315';
>   imsi | md5 | bit_length
> ------+-----+------------
> (0 rows)
>
>
> =# explain analyse select imsi, md5(imsi), bit_length(imsi) from
> list_cards_tbl where imsi = '284110000123315';
>                                                             QUERY PLAN
>
---------------------------------------------------------------------------------------------------------------------------------
>
>   Index Scan using imsi_list_cards_tbl on list_cards_tbl
> (cost=0.28..8.30 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=1)
>     Index Cond: (imsi = '284110000123315'::text)
>   Planning time: 0.106 ms
>   Execution time: 0.040 ms
> (4 rows)
>
>
> Same result.
>
>   =# SET enable_indexscan = off;
> SET
> =# select imsi, md5(imsi), bit_length(imsi) from list_cards_tbl where
> imsi = '284110000123315';
>   imsi | md5 | bit_length
> ------+-----+------------
> (0 rows)
>
> =# explain analyse select imsi, md5(imsi), bit_length(imsi) from
> list_cards_tbl where imsi = '284110000123315';
>                                                          QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------
>
>   Bitmap Heap Scan on list_cards_tbl  (cost=4.29..8.31 rows=1 width=16)
> (actual time=0.016..0.016 rows=0 loops=1)
>     Recheck Cond: (imsi = '284110000123315'::text)
>     ->  Bitmap Index Scan on imsi_list_cards_tbl  (cost=0.00..4.29
> rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=1)
>           Index Cond: (imsi = '284110000123315'::text)
>   Planning time: 0.109 ms
>   Execution time: 0.046 ms
> (6 rows)
>
>
> Finally.
>
>
> =# SET enable_bitmapscan = off
> SET
> =# select imsi, md5(imsi), bit_length(imsi) from list_cards_tbl where
> imsi = '284110000123315';
>        imsi       |               md5                | bit_length
> -----------------+----------------------------------+------------
>   284110000123315 | b438e984c97483bb942eaaed5c0147f3 |        120
> (1 row)
>
> =# explain analyse select imsi, md5(imsi), bit_length(imsi) from
> list_cards_tbl where imsi = '284110000123315';
>                                                 QUERY PLAN
> --------------------------------------------------------------------------------------------------------
>
>   Seq Scan on list_cards_tbl  (cost=0.00..78.08 rows=1 width=16) (actual
> time=0.053..0.502 rows=1 loops=1)
>     Filter: (imsi = '284110000123315'::text)
>     Rows Removed by Filter: 2485
>   Planning time: 0.127 ms
>   Execution time: 0.533 ms
> (5 rows)
>
>
>
> I will drop index and will create them again but after rebuild I think
> if there are mistakes after rebuild they should be fixed ?

Yes that seems to be confirmed here:
http://www.postgresql.org/docs/9.5/interactive/sql-reindex.html
"REINDEX is similar to a drop and recreate of the index in that the
index contents are rebuilt from scratch. ... "

> Process that populate them isnt the same but data is coming from
> database not from user input filed.
>
> Any ideas ?

Not at the moment, but some unanswered questions:

Is '284110000123315' the only value you are having issues with?

What happens if you populate the field in list_cards_tbl with
the data from list_newcards_tbl?

>
>
> HS
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com