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

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