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

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 ?
Process that populate them isnt the same but data is coming from
database not from user input filed.

Any ideas ?


HS