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@xxxxxxxxxxx
=# 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
