diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dump_regrobid_pdf.sql | 15 | ||||
| -rw-r--r-- | sql/dump_ungrobid_pdf.sql | 15 | ||||
| -rw-r--r-- | sql/random_queries.md | 55 | ||||
| -rw-r--r-- | sql/table_sizes.md | 11 |
4 files changed, 96 insertions, 0 deletions
diff --git a/sql/dump_regrobid_pdf.sql b/sql/dump_regrobid_pdf.sql new file mode 100644 index 0000000..b846834 --- /dev/null +++ b/sql/dump_regrobid_pdf.sql @@ -0,0 +1,15 @@ + +-- Run like: +-- psql sandcrawler < dump_regrobid_pdf.sql | sort -S 4G | uniq -w 40 | cut -f2 > dump_regrobid_pdf.2019-11-12.json + +BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; + +COPY ( + SELECT cdx.sha1hex, row_to_json(cdx) FROM cdx + WHERE cdx.mimetype = 'application/pdf' + AND EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex AND grobid.grobid_version IS NULL) +) +TO STDOUT +WITH NULL ''; + +ROLLBACK; diff --git a/sql/dump_ungrobid_pdf.sql b/sql/dump_ungrobid_pdf.sql new file mode 100644 index 0000000..3e6d782 --- /dev/null +++ b/sql/dump_ungrobid_pdf.sql @@ -0,0 +1,15 @@ + +-- Run like: +-- psql sandcrawler < dump_ungrobid_pdf.sql | sort -S 4G | uniq -w 40 | cut -f2 > dump_ungrobid_pdf.2019-09-23.json + +BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; + +COPY ( + SELECT cdx.sha1hex, row_to_json(cdx) FROM cdx + WHERE cdx.mimetype = 'application/pdf' + AND NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex) +) +TO STDOUT +WITH NULL ''; + +ROLLBACK; diff --git a/sql/random_queries.md b/sql/random_queries.md new file mode 100644 index 0000000..6ae651a --- /dev/null +++ b/sql/random_queries.md @@ -0,0 +1,55 @@ + +Basic stats (2019-09-23): + + SELECT COUNT(*) FROM cdx WHERE NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex); + => 28,023,760 + => Time: 253897.213 ms (04:13.897) + + SELECT COUNT(DISTINCT sha1hex) FROM cdx WHERE NOT EXISTS (SELECT grobid.sha1hex FROM grobid WHERE cdx.sha1hex = grobid.sha1hex); + => 22,816,087 + => Time: 287097.944 ms (04:47.098) + + SELECT COUNT(*) FROM grobid. + => 56,196,992 + + SELECT COUNT(DISTINCT sha1hex) FROM cdx; + => 64,348,277 + => Time: 572383.931 ms (09:32.384) + + SELECT COUNT(*) FROM cdx; + => 74,796,777 + + SELECT mimetype, COUNT(*) FROM cdx GROUP BY mimetype ORDER BY COUNT(*) DESC; + => Time: 189067.335 ms (03:09.067) + + mimetype | count + ------------------------+---------- + application/pdf | 51049905 + text/html | 24841846 + text/xml | 524682 + application/postscript | 81009 + (4 rows) + +Time: 189067.335 ms (03:09.067) + + SELECT status_code, COUNT(*) FROM grobid GROUP BY status_code ORDER BY count(*) DESC; + + status_code | count + -------------+---------- + 200 | 56196992 + + compare with older sandcrawler/output-prod/2019-05-28-1920.35-statuscodecount: + + 200 49567139 + 400 3464503 + 409 691917 + 500 247028 + 503 123 + + SELECT row_to_json(cdx) FROM cdx LIMIT 5; + + SELECT row_to_json(r) FROM ( + SELECT url, datetime FROM cdx + ) r + LIMIT 5; + diff --git a/sql/table_sizes.md b/sql/table_sizes.md new file mode 100644 index 0000000..3596b2b --- /dev/null +++ b/sql/table_sizes.md @@ -0,0 +1,11 @@ + +## September 2019 + + table_name | table_size | indexes_size | total_size + --------------------------------------------------------------+------------+--------------+------------ + "public"."cdx" | 31 GB | 27 GB | 58 GB + "public"."file_meta" | 13 GB | 6500 MB | 19 GB + "public"."shadow" | 8303 MB | 9216 MB | 17 GB + "public"."grobid" | 4994 MB | 6678 MB | 11 GB + "public"."fatcat_file" | 5206 MB | 2094 MB | 7300 MB + "public"."petabox" | 403 MB | 594 MB | 997 MB |