search

CartoDB / cartodb

Location Intelligence & Data Visualization tool
http://carto.com
BSD 3-Clause "New" or "Revised" License
2.75k stars 650 forks source link

Number of points in DATA tab is wrong #12193

Closed saleiva closed 7 years ago

saleiva commented 7 years ago

Steps to Reproduce

  1. Import this file: 20170524130000.export.CSV.zip
  2. Take a look at the DATA tab within the layer
  3. Perform a SELECT count(*) on the table view.

Current Result

It says 29, but it says that I have ~1900 points in the data tab

xavijam commented 7 years ago

Hum, I feel it is related to the Postgres stats, that is the way we take that number. Am I wrong @nobuti?

nobuti commented 7 years ago

Indeed.

matallo commented 7 years ago

I reported exactly this, I was told this figure comes from Postgres.

rafatower commented 7 years ago

Related: https://github.com/CartoDB/cartodb/issues/11746

At some point I think we removed the VACUUM ANALYZE from the import. It would be wise to keep just an ANALYZE right after having the table imported if pg stats is what we use to report the number of points.

rafatower commented 7 years ago

I'm afraid we're limited by postgres stats:

# analyze  rtorre.table_20170524130000_export;
NOTICE:  no non-null/empty features, unable to compute statistics
NOTICE:  no non-null/empty features, unable to compute statistics
NOTICE:  no non-null/empty features, unable to compute statistics
NOTICE:  no non-null/empty features, unable to compute statistics
ANALYZE
# explain select * from rtorre.table_20170524130000_export;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Seq Scan on table_20170524130000_export  (cost=0.00..2.09 rows=29 width=398)
(1 row)
rafatower commented 7 years ago

But there are indeed 29 rows in the table!

# select count(1) from rtorre.table_20170524130000_export;
 count 
-------
    29
(1 row)
rafatower commented 7 years ago

The provided zip file indeed only contains 29 rows, so I don't think the description matches the original issue you were experiencing:

$ wc -l 20170524130000.export.CSV
29 20170524130000.export.CSV
$ cat 20170524130000.export.CSV
658588345   20170524    201705  2017    2017.3945                                           EURMIL  EUROPE  EUR                 MIL         1   040 040 04  1   1.0 2   1   22.77777777777778   0                               4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588346   20170524    201705  2017    2017.3945                                           IGOWSTNAT   NATO    WST NAT             IGO         1   030 030 03  1   4.0 10110   5.37190082644628    0                               1   Serbia  RI  RI      43.75   20.75   RI  1   Serbia  RI  RI      43.75   20.75   RI  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588347   20170524    201705  2017    2017.3945                                           IGOWSTNAT   NATO    WST NAT             IGO         1   040 040 04  1   1.0 4   14  -1.40845070422536   0                               1   United States   US  US      38  -97 US  1   Afghanistan AF  AF      33  65  AF  20170524130000  Noor TV, Kabul/BBC Monitoring/(c) BBC
658588348   20170524    201705  2017    2017.3945                                           IGOWSTNAT   NATO    WST NAT             IGO         1   040 040 04  1   1.0 2   12  -1.40845070422536   0                               1   United States   US  US      38  -97 US  1   United States   US  US      38  -97 US  20170524130000  Noor TV, Kabul/BBC Monitoring/(c) BBC
658588349   20170524    201705  2017    2017.3945   EGY EGYPTIAN    EGY                             SDN SUDANESE    SDN                             1   120 120 12  3   -4.0    2   1   2   -1.93548387096774   4   Cairo, Al Qahirah, Egypt    EG  EG11    65350   30.05   31.25   -290692 4   Darfur, Gharb Darfur, Sudan SU  SU47    37140   13.5    23.5    -17505  4   Darfur, Gharb Darfur, Sudan SU  SU47    37140   13.5    23.5    -17505  20170524130000  Nile News TV, Cairo/BBC Monitoring/(c) BBC
658588350   20170524    201705  2017    2017.3945   EGY EGYPTIAN    EGY                             SDN SUDANESE    SDN                             1   120 120 12  3   -4.0    1   1   1   -1.93548387096774   4   Cairo, Al Qahirah, Egypt    EG  EG11    65350   30.05   31.25   -290692 4   Darfur, Gharb Darfur, Sudan SU  SU47    37140   13.5    23.5    -17505  4   Cairo, Al Qahirah, Egypt    EG  EG11    65350   30.05   31.25   -290692 20170524130000  Nile News TV, Cairo/BBC Monitoring/(c) BBC
658588351   20170524    201705  2017    2017.3945   EGYGOV  EGYPT   EGY                 GOV         EGY EGYPT   EGY                             1   112 112 11  3   -2.0    2   1   2-1.93548387096774  4   Darfur, Gharb Darfur, Sudan SU  SU47    37140   13.5    23.5    -17505  4   Cairo, Al Qahirah, Egypt    EG  EG11    65350   30.05   31.25   -290692 4   Cairo, Al Qahirah, Egypt    EG  EG11    65350   30.05   31.25   -290692 20170524130000  Nile News TV, Cairo/BBC Monitoring/(c) BBC
658588352   20170524    201705  2017    2017.3945   EGYGOV  EGYPTIAN    EGY                 GOV         EGY CAIRO   EGY                             1   112 112 11  3   -2.0    4   14  -1.93548387096774   4   Cairo, Al Qahirah, Egypt    EG  EG11    65350   30.05   31.25   -290692 4   Cairo, Al Qahirah, Egypt    EG  EG11    65350   30.05   31.25   -290692 4   Cairo, Al Qahirah, Egypt    EG  EG11    65350   30.05   31.25   -290692 20170524130000  Nile News TV, Cairo/BBC Monitoring/(c) BBC
658588353   20170524    201705  2017    2017.3945   EGYGOV  EGYPT   EGY                 GOV         SDNGOV  SUDAN   SDN                 GOV         1   120 120 12  3   -4.0    2   1   2-1.93548387096774  4   Darfur, Gharb Darfur, Sudan SU  SU47    37140   13.5    23.5    -17505  4   Darfur, Gharb Darfur, Sudan SU  SU47    37140   13.5    23.5    -17505  4   Darfur, Gharb Darfur, Sudan SU  SU47    37140   13.5    23.5    -17505  20170524130000  Nile News TV, Cairo/BBC Monitoring/(c) BBC
658588354   20170524    201705  2017    2017.3945   EGYGOV  EGYPTIAN    EGY                 GOV         SDNGOV  SUDANESE    SDN                 GOV         1   120 120 12  3   -4.0    4   1   4   -1.93548387096774   4   Cairo, Al Qahirah, Egypt    EG  EG11    65350   30.05   31.25   -290692 4   Darfur, Gharb Darfur, Sudan SU  SU47    37140   13.5    23.5    -17505  4   Darfur, Gharb Darfur, Sudan SU  SU47    37140   13.5    23.5    -17505  20170524130000  Nile News TV, Cairo/BBC Monitoring/(c) BBC
658588355   20170524    201705  2017    2017.3945   EURMIL  EUROPE  EUR                 MIL                                                 1   040 040 04  1   1.0 1   1   12.77777777777778   4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  0                               4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588356   20170524    201705  2017    2017.3945   EURMIL  EUROPE  EUR                 MIL                                                 1   040 040 04  1   1.0 1   1   12.77777777777778   4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  0                               1   Serbia  RI  RI      43.75   20.75   RI  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588357   20170524    201705  2017    2017.3945   EURMIL  EUROPE  EUR                 MIL         MIL ARMY CHIEF                      MIL         1   040 040 04  1   1.0 5   15  2.77777777777778    4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588358   20170524    201705  2017    2017.3945   GBR UNITED KINGDOM  GBR                             GOV REGIME                      GOV         1   075 075 07  2   7.0 8   18  -4.30463576158941   4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    20170524130000  BBC Monitoring/BBC Monitoring/(c) BBC
658588359   20170524    201705  2017    2017.3945   GBR UNITED KINGDOM  GBR                             GOV REGIME                      GOV         1   075 075 07  2   7.0 2   12  -4.30463576158941   4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    4   Barqa, Libya (general), Libya   LY  LY00    19091   31  22.5    -3163389    4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    20170524130000  BBC Monitoring/BBC Monitoring/(c) BBC
658588360   20170524    201705  2017    2017.3945   GBR UNITED KINGDOM  GBR                             LBY LIBYAN  LBY                             1   051 051 05  1   3.4 2   12  -4.30463576158941   4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    20170524130000  BBC Monitoring/BBC Monitoring/(c) BBC
658588361   20170524    201705  2017    2017.3945   GBR UNITED KINGDOM  GBR                             LBY LIBYAN  LBY                             1   051 051 05  1   3.4 2   12  -4.30463576158941   4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    4   Barqa, Libya (general), Libya   LY  LY00    19091   31  22.5    -3163389    4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    20170524130000  BBC Monitoring/BBC Monitoring/(c) BBC
658588362   20170524    201705  2017    2017.3945   LBY LIBYA   LBY                                                                     0   080 080 08  2   5.0 2   1   2-4.30463576158941  4   Barqa, Libya (general), Libya   LY  LY00    19091   31  22.5    -3163389    0                               4   Barqa, Libya (general), Libya   LY  LY00    19091   31  22.5    -3163389    20170524130000  BBC Monitoring/BBC Monitoring/(c) BBC
658588363   20170524    201705  2017    2017.3945   LBY LIBYAN  LBY                             RAD EXTREMIST                       RAD         0   111 111 11  3   -2.0    2   12  -4.30463576158941   4   Barqa, Libya (general), Libya   LY  LY00    19091   31  22.5    -3163389    4   Manchester, Manchester, United Kingdom  UK  UKI2    40118   53.5    -2.21667    -2602512    4   Barqa, Libya (general), Libya   LYLY00  19091   31  22.5    -3163389    20170524130000  BBC Monitoring/BBC Monitoring/(c) BBC
658588364   20170524    201705  2017    2017.3945   LEG PARLIAMENT                      LEG         UKR UKRAINE UKR                             1   173 173 17  4   -5.0    10110   -10.9634551495017   4   Kiev, Ukraine (general), Ukraine    UP  UP00    28554   50.4333 30.5167 -1044367    4   Kiev, Ukraine (general), Ukraine    UP  UP00    28554   50.4333 30.5167 -1044367    4   Kiev, Ukraine (general), Ukraine    UP  UP00    28554   50.4333 30.5167 -1044367    20170524130000  UNIAN news agency, Kiev/BBC Monitoring/(c) BBC
658588365   20170524    201705  2017    2017.3945   MIL ARMY CHIEF                      MIL         EURMIL  EUROPE  EUR                 MIL         1   040 040 04  1   1.0 1   11  2.77777777777778    4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588366   20170524    201705  2017    2017.3945   MIL ARMY CHIEF                      MIL         EURMIL  EUROPE  EUR                 MIL         1   040 040 04  1   1.0 1   11  2.77777777777778    4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  1   Serbia  RI  RI      43.75   20.75   RI  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588367   20170524    201705  2017    2017.3945   MIL ARMY CHIEF                      MIL         EURMIL  EUROPE  EUR                 MIL         1   040 040 04  1   1.0 3   13  2.77777777777778    1   Serbia  RI  RI      43.75   20.75   RI  1   Serbia  RI  RI      43.75   20.75   RI  1   Serbia  RI  RI      43.75   20.75   RI  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588368   20170524    201705  2017    2017.3945   QAT QATAR   QAT                                                                     1   012 012 01  1   -0.4    4   1   4-5.03597122302159  4   Doha, Ad Daw?ah, Qatar  QA  QA01    25307   25.2867 51.5333 -785169 0                               4   Doha, Ad Daw?ah, Qatar  QA  QA01    25307   25.2867 51.5333 -785169 20170524130000  Al-Jazeera TV, Doha/BBC Monitoring/(c) BBC
658588369   20170524    201705  2017    2017.3945   QAT DOHA    QAT                             QATMED  QATAR   QAT                 MED         1   173 173 17  4   -5.0    2   1   2-5.03597122302159  4   Doha, Ad Daw?ah, Qatar  QA  QA01    25307   25.2867 51.5333 -785169 4   Doha, Ad Daw?ah, Qatar  QA  QA01    25307   25.2867 51.5333 -785169 4   Doha, Ad Daw?ah, Qatar  QA  QA01    25307   25.2867 51.5333 -785169 20170524130000  Al-Jazeera TV, Doha/BBC Monitoring/(c) BBC
658588370   20170524    201705  2017    2017.3945   QAT DOHA    QAT                             QATMED  QATAR   QAT                 MED         1   173 173 17  4   -5.0    8   1   8-5.03597122302159  4   Doha, Ad Daw?ah, Qatar  QA  QA01    25307   25.2867 51.5333 -785169 1   Iran    IR  IR      32  53  IR  4   Doha, Ad Daw?ah, Qatar  QA  QA01    25307   25.2867 51.5333 -785169 20170524130000  Al-Jazeera TV, Doha/BBC Monitoring/(c) BBC
658588371   20170524    201705  2017    2017.3945   SCGSRB  SERBIA  SRB                                                                     1   120 120 12  3   -4.0    10  1   10  5.37190082644628    1   Serbia  RI  RI      43.75   20.75   RI  0                               1   Serbia  RI  RI      43.75   20.75   RI  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588372   20170524    201705  2017    2017.3945   USA THE US  USA                                                                     1   050 050 05  1   3.5 6   1   62.77777777777778   4   Belgrade, Serbia (general), RB  RB00    47629   44.8186 20.4681 -74897  0                               1   United States   US  US      38  -97 US  20170524130000  Radio B/BBC Monitoring/(c) BBC
658588373   20170524    201705  2017    2017.3945   USA THE US  USA                                                                     1   050 050 05  1   3.5 3   1   32.77777777777778   1   Serbia  RI  RI      43.75   20.75   RI  0                               1   Serbia  RI  RI      43.75   20.75   RI  20170524130000  Radio B/BBC Monitoring/(c) BBC

Are you able to reproduce this or a similar issue in some other way, @saleiva ?

saleiva commented 7 years ago

Nope, it happens quite randomly :(

saleiva commented 7 years ago

In general, it looks like sometimes the stats get somehow very wrong and keep like... cached.

rafatower commented 7 years ago

Mind that some things has changed since you posted it.

Since I cannot reproduce anything with this (the file provided contains 29 rows and the dataview reports 29 rows, or did I misread it?) I suggest to close this ticket and if the issue arises with more info to reproduce, reopen it.

If you have any better suggestion, I'm all ears.

saleiva commented 7 years ago

ok