gleu/sqlite_fdw

Need to mention that ANALYZE is needed

fschulze opened this issue · 5 comments

fschulze commented

I got the following error when I tried to wrap one of my DBs:
ERROR: SQL error during prepare: no such table: sqlite_stat1

After a while I figured out that I had to run ANALYZE on the DB, so sqlite_stat1 is created.

gleu commented

Actually, we shouldn't check this statistics table if it doesn't exist yet. And post a warning message so that the user knows something better can be done.

I'm working on it.

Thanks for your report.

fschulze commented

That would be even better. Besides that this fdw is working great for me, thanks for it.

gleu commented

Sorry that it took me so long to fix it. Here is how it works:

my_simple_test=# explain analyze select * from t1;
WARNING: The sqlite3 database has not been analyzed.
HINT: Run ANALYZE on table "t1", database "/home/guillaume/FDW/example/test3.db".

QUERY PLAN

Foreign Scan on t1 (cost=0.00..1000000.00 rows=1000000 width=4) (actual time=0.205..0.214 rows=1 loops=1)
sqlite plan: SCAN TABLE t1 (~1000000 rows)
Total runtime: 0.385 ms
(3 rows)

You've got a warning if the sqlite_stat1 table doesn't exist, and a hint to tell you what to do. In the meantime, it'll use an hardcoded value (1000000 as it seems to be the default value for sqlite3).

If it exists, it will work as usual:

my_simple_test=# explain analyze select * from t1;

QUERY PLAN

Foreign Scan on t1 (cost=0.00..1.00 rows=1 width=4) (actual time=0.147..0.152 rows=1 loops=1)
sqlite plan: SCAN TABLE t1 (~1 rows)
Total runtime: 0.257 ms
(3 rows)

Can you check that it works for you?

cbandy commented

I thought explain analyze actually runs the query to give exact costs, while explain uses statistics tables.

Are SQLite statistics needed to run any query?

gleu commented

"explain analyze" gives you estimation and exact values for costs and number of rows. "explain" only shows estimation values. They both use statistics informations.

sqlite statistics are not needed at all. We can rely on the hardcoded value. AFAICT, the PostgreSQL planner cannot do anything about it. But it's nice to know what the current statistics are on the sqlite side.