Texture queries and scalability

[willeppy]

Intro

This post contains all the queries that are executed for a simple demo dataset with different kinds of attributes.

The dataset has 3 tables:

• main_table -- contains id (pk), text (text), topic (categorical), quantMeasure (number), umap_x, umap_y
• words_table -- contains id (fk), word (categorical)
• authors_table -- contains id (fk), author (categorical)

1 -- Initial load

Table metadata fetching from Texture

1. Summarize each table -- 1 query per table

SUMMARIZE "main_table"
SUMMARIZE "words_table"
SUMMARIZE "authors_table"

2. Count distinct for each column -- 1 query per table

SELECT COUNT(DISTINCT "text") as "text",COUNT(DISTINCT "topic") as "topic",COUNT(DISTINCT "quantMeasure") as "quantMeasure" FROM "main_table"
SELECT COUNT(DISTINCT "word") as "word" FROM "words_table"
SELECT COUNT(DISTINCT "author") as "author" FROM "authors_table"

Mosaic clients

1. A bunch of describe queries that I'm not sure where they come from?

DESCRIBE SELECT "text" AS "col0", "topic" AS "col1", "quantMeasure" AS "col2", "id" AS "col3", "umap_x" AS "col4", "umap_y" AS "col5" FROM "main_table" AS "source"
DESCRIBE SELECT COUNT(_)::INTEGER AS "column" FROM "main_table" AS "source" GROUP BY ALL
DESCRIBE SELECT COUNT(_)::INTEGER AS "column" FROM "words_table" AS "source" GROUP BY ALL
DESCRIBE SELECT "word" AS "column" FROM "words_table" AS "source"
DESCRIBE SELECT COUNT(\*)::INTEGER AS "column" FROM "authors_table" AS "source" GROUP BY ALL
DESCRIBE SELECT "author" AS "column" FROM "authors_table" AS "source"

2. For categorical charts -- get the count of the current data to get remaining rows

This happens within the categorical chart component

SELECT COUNT(DISTINCT CASE WHEN "topic" IS NULL THEN 'null' ELSE "topic" END) AS "count" FROM "main_table"
SELECT COUNT(DISTINCT CASE WHEN "word" IS NULL THEN 'null' ELSE "word" END) AS "count" FROM "words_table"
SELECT COUNT(DISTINCT CASE WHEN "author" IS NULL THEN 'null' ELSE "author" END) AS "count" FROM "authors_table"

2. Mosaic visualization queries

-- quantMeasure - binned histogram
SELECT MIN("quantMeasure") AS "min", MAX("quantMeasure") AS "max" FROM "main_table"
SELECT 0 + 5::DOUBLE _ (FLOOR("quantMeasure" / 5::DOUBLE)) AS "x1", 0 + 5::DOUBLE _ (1 + FLOOR("quantMeasure" / 5::DOUBLE)) AS "x2", COUNT(_)::INTEGER AS "y" FROM "main_table" AS "source" GROUP BY "x1", "x2"

-- topic - categorical chart
SELECT COUNT(_)::INTEGER AS "x", "topic" FROM "main_table" AS "source" GROUP BY "topic" ORDER BY "topic"
SELECT "topic", COUNT(_)::INTEGER AS "text" FROM "main_table" AS "source" GROUP BY "topic"

-- word - categorical chart
SELECT COUNT(_)::INTEGER AS "x", "word" FROM "words_table" AS "source" GROUP BY "word" ORDER BY "word"
SELECT "word", COUNT(_)::INTEGER AS "text" FROM "words_table" AS "source" GROUP BY "word"

-- author - categorical chart
SELECT COUNT(_)::INTEGER AS "x", "author" FROM "authors_table" AS "source" GROUP BY "author" ORDER BY "author"
SELECT "author", COUNT(_)::INTEGER AS "text" FROM "authors_table" AS "source" GROUP BY "author"

-- Scatter projection
SELECT "umap_x", "umap_y" FROM "main_table" AS "source"

-- table view
SELECT "text", "topic", "quantMeasure", "id" FROM "main_table" AS "source" ORDER BY "id" LIMIT 50 OFFSET 0
SELECT "words_table"._ FROM "words_table" JOIN (SELECT "id" FROM "main_table" AS "source" ORDER BY "id" LIMIT 50 OFFSET 0) AS main_table ON "words_table"."id" = main_table."id"
SELECT "authors_table".\* FROM "authors_table" JOIN (SELECT "id" FROM "main_table" AS "source" ORDER BY "id" LIMIT 50 OFFSET 0) AS main_table ON "authors_table"."id" = main_table."id"

2 -- Filter on a main table attribute

Here I filter the topic attribute, which is on the main table.

CACHING?

• This seemingly gets cached my mosaic so if I make same selection multiple times, no new queries are executed.

1. Texture data profile queries

SELECT COUNT(*)::INTEGER AS "count" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1')
SELECT "id" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1')

2. Mosaic visualization queries

-- quantMeasure - binned histogram
SELECT 0 + 5::DOUBLE * (FLOOR("quantMeasure" / 5::DOUBLE)) AS "x1", 0 + 5::DOUBLE * (1 + FLOOR("quantMeasure" / 5::DOUBLE)) AS "x2", COUNT(*)::INTEGER AS "y" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1') GROUP BY "x1", "x2"

-- topic - categorical chart --- not sure why this has so many queries??
SELECT COUNT(*)::INTEGER AS "x", "topic", ("topic" IS NOT DISTINCT FROM 'topic_1') AS "__" FROM "main_table" AS "source" GROUP BY "topic" ORDER BY "topic"
SELECT COUNT(*)::INTEGER AS "x", "topic" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1') GROUP BY "topic" ORDER BY "topic"
SELECT "topic", COUNT(*)::INTEGER AS "text" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1') GROUP BY "topic"
SELECT COUNT(*)::INTEGER AS "x", "topic", ("topic" IS NOT DISTINCT FROM 'topic_1') AS "__" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1') GROUP BY "topic" ORDER BY "topic"

-- word - categorical chart -- does subquery to filter to ids from main table
SELECT COUNT(*)::INTEGER AS "x", "word" FROM "words_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "main_table" WHERE "source"."id" = "main_table"."id" AND ("topic" IS NOT DISTINCT FROM 'topic_1'))) GROUP BY "word" ORDER BY "word"
SELECT "word", COUNT(*)::INTEGER AS "text" FROM "words_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "main_table" WHERE "source"."id" = "main_table"."id" AND ("topic" IS NOT DISTINCT FROM 'topic_1'))) GROUP BY "word"

-- author - categorical chart -- does subquery to filter to ids from main table
SELECT COUNT(*)::INTEGER AS "x", "author" FROM "authors_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "main_table" WHERE "source"."id" = "main_table"."id" AND ("topic" IS NOT DISTINCT FROM 'topic_1'))) GROUP BY "author" ORDER BY "author"
SELECT "author", COUNT(*)::INTEGER AS "text" FROM "authors_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "main_table" WHERE "source"."id" = "main_table"."id" AND ("topic" IS NOT DISTINCT FROM 'topic_1'))) GROUP BY "author"

-- Scatter projection
SELECT "umap_x", "umap_y" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1')

-- table view
SELECT "text", "topic", "quantMeasure", "id" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1') ORDER BY "id" LIMIT 50 OFFSET 0
SELECT "words_table".* FROM "words_table" JOIN (SELECT "id" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1') ORDER BY "id" LIMIT 50 OFFSET 0) AS main_table ON "words_table"."id" = main_table."id"
SELECT "authors_table".* FROM "authors_table" JOIN (SELECT "id" FROM "main_table" AS "source" WHERE ("topic" IS NOT DISTINCT FROM 'topic_1') ORDER BY "id" LIMIT 50 OFFSET 0) AS main_table ON "authors_table"."id" = main_table."id"

3 -- Filter on a words table attribute

In this case, I filter the word attribute which comes from a separate table (words_table).

CACHING?

• Yes -- if I make the same selection multiple times, no new queries are executed.

1. Texture data profile queries

SELECT COUNT(*)::INTEGER AS "count" FROM "main_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A')))
SELECT "id" FROM "main_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A')))

2. Mosaic visualization queries

-- quantMeasure - binned histogram -- does subquery to filter to ids from words_table
SELECT 0 + 5::DOUBLE * (FLOOR("quantMeasure" / 5::DOUBLE)) AS "x1", 0 + 5::DOUBLE * (1 + FLOOR("quantMeasure" / 5::DOUBLE)) AS "x2", COUNT(*)::INTEGER AS "y" FROM "main_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A'))) GROUP BY "x1", "x2"

-- topic - categorical chart -- does subquery to filter to ids from words_table
SELECT COUNT(*)::INTEGER AS "x", "topic" FROM "main_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A'))) GROUP BY "topic" ORDER BY "topic"
SELECT "topic", COUNT(*)::INTEGER AS "text" FROM "main_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A'))) GROUP BY "topic"

-- word - categorical chart -- normal categorical filter
SELECT COUNT(*)::INTEGER AS "x", "word", ("word" IS NOT DISTINCT FROM 'A') AS "__" FROM "words_table" AS "source" GROUP BY "word" ORDER BY "word"
SELECT COUNT(*)::INTEGER AS "x", "word" FROM "words_table" AS "source" WHERE ("word" IS NOT DISTINCT FROM 'A') GROUP BY "word" ORDER BY "word"
SELECT "word", COUNT(*)::INTEGER AS "text" FROM "words_table" AS "source" WHERE ("word" IS NOT DISTINCT FROM 'A') GROUP BY "word"
SELECT COUNT(*)::INTEGER AS "x", "word", ("word" IS NOT DISTINCT FROM 'A') AS "__" FROM "words_table" AS "source" WHERE ("word" IS NOT DISTINCT FROM 'A') GROUP BY "word" ORDER BY "word"

-- author - categorical chart -- does subquery to filter to ids from words_table
SELECT COUNT(*)::INTEGER AS "x", "author" FROM "authors_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A'))) GROUP BY "author" ORDER BY "author"
SELECT "author", COUNT(*)::INTEGER AS "text" FROM "authors_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A'))) GROUP BY "author"

-- Scatter projection -- does subquery to filter to ids from words_table
SELECT "umap_x", "umap_y" FROM "main_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A')))

-- table view -- does subquery to filter to ids from words_table
SELECT "text", "topic", "quantMeasure", "id" FROM "main_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A'))) ORDER BY "id" LIMIT 50 OFFSET 0
SELECT "words_table".* FROM "words_table" JOIN (SELECT "id" FROM "main_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A'))) ORDER BY "id" LIMIT 50 OFFSET 0) AS main_table ON "words_table"."id" = main_table."id"
SELECT "authors_table".* FROM "authors_table" JOIN (SELECT "id" FROM "main_table" AS "source" WHERE (EXISTS (SELECT 1 FROM "words_table" WHERE "source"."id" = "words_table"."id" AND ("word" IS NOT DISTINCT FROM 'A'))) ORDER BY "id" LIMIT 50 OFFSET 0) AS main_table ON "authors_table"."id" = main_table."id"