[Pgpool-general] EXPLAIN statement fails on 'kind mismatch'

[asaf at lingnu.com]

>> >> > after this, everything remains the same.
>> >> > (EXPLAIN output, kind mismatch)
>> >
>> > Can you show me the vacuum analyze output on backend 0?
>> > --
>> > Tatsuo Ishii
>> > SRA OSS, Inc. Japan
>> >
>>
>> sorry, I did not save it,
>> would it help if i run the command again ?
>> (the problem does not exist at the moment)
>
> No, I don't think so. Apparently you now have almost same statistics
> data on backend 0. Maybe you hadn't run vacuum analyze on backend 0.
>
> Anyway, pgpool-II-2.3 now allow to load balance EXPLAIN, thus you
> would not see kind mismatch errors on 2.3. Note that this does not
> apply to EXPLAIN ANALYZE + DML by obvious reason.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>

continued,
I've enabled auto-vacuuming, but it doesn't seem to help, this
morning I had another mismatch on 'EXPLAIN' statement, on different table.
this time, on the secondary backend, situation before fix :
backend 0:
                                   QUERY PLAN
-----------------------------------------------------------------------------------
 Bitmap Heap Scan on core_req_res  (cost=18.30..1194.77 rows=781 width=1313)
   Recheck Cond: (core_req_res_id > 8000)
   ->  Bitmap Index Scan on core_req_res_pkey  (cost=0.00..18.11 rows=781
width=0)
         Index Cond: (core_req_res_id > 8000)
(4 rows)

backend 1:

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using core_req_res_pkey on core_req_res  (cost=0.00..8.27
rows=1 width=1318)
   Index Cond: (core_req_res_id > 8000)
(2 rows)


after running (on backend 1):
vacuumdb --verbose --analyze --table core_req_res spyqa

backend 1 returns same as backend 0, and everything is OK.

(maybe I should disable auto-vacuum and run this as cron job ?)

the vacuum output :

INFO:  scanned index "core_req_res_client_request_id_key" to remove 77 row
versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.05 sec.
INFO:  scanned index "core_req_res_pkey" to remove 77 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  scanned index "core_req_res_receipt_num_key" to remove 77 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  scanned index "idx_crr_client_req_id" to remove 77 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  scanned index "idx_crr_core_req_res_id" to remove 77 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  scanned index "idx_crr_req_type_id" to remove 77 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  scanned index "idx_crr_user_id" to remove 77 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "core_req_res": removed 77 row versions in 22 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "core_req_res_client_request_id_key" now contains 7794 row
versions in 32 pages
DETAIL:  77 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "core_req_res_pkey" now contains 7794 row versions in 24 pages
DETAIL:  77 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "core_req_res_receipt_num_key" now contains 7794 row versions
in 25 pages
DETAIL:  77 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_crr_client_req_id" now contains 7794 row versions in 33
pages
DETAIL:  77 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_crr_core_req_res_id" now contains 7794 row versions in
25 pages
DETAIL:  77 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_crr_req_type_id" now contains 7794 row versions in 24 pages
DETAIL:  77 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_crr_user_id" now contains 7794 row versions in 24 pages
DETAIL:  77 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "core_req_res": found 73 removable, 7794 nonremovable row versions
in 1284 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
197 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.12 sec.
INFO:  vacuuming "pg_toast.pg_toast_19124"
INFO:  index "pg_toast_19124_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_19124": found 0 removable, 0 nonremovable row versions in
0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "ycore.core_req_res"
INFO:  "core_req_res": scanned 1284 of 1284 pages, containing 7794 live
rows and 0 dead rows; 3000 rows in sample, 7794 estimated total rows