COUNT condition: INDEX Speed-up improvements

cbq> select * from system:indexes;
{
    "requestID": "213ad2ed-d02b-4d9a-b0d3-acd7293c6408",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "indexes": {
                "condition": "(`type` = \"bcn_scan\")",
                "datastore_id": "http://127.0.0.1:8091",
                "id": "d37e2d41290035c3",
                "index_key": [
                    "substr(`t`, 0, 10)",
                    "`did`"
                ],
                "keyspace_id": "sync_gateway",
                "name": "bcn_scan-date-did",
                "namespace_id": "default",
                "state": "online",
                "using": "gsi"
            }
        },
        {
            "indexes": {
                "datastore_id": "http://127.0.0.1:8091",
                "id": "582e2bb37cdebf18",
                "index_key": [],
                "is_primary": true,
                "keyspace_id": "sync_gateway",
                "name": "#primary",
                "namespace_id": "default",
                "state": "online",
                "using": "gsi"
            }
        },
        {
            "indexes": {
                "datastore_id": "http://127.0.0.1:8091",
                "id": "a8c8c25786449f1",
                "index_key": [],
                "is_primary": true,
                "keyspace_id": "playground",
                "name": "#primary",
                "namespace_id": "default",
                "state": "online",
                "using": "gsi"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "142.234564ms",
        "executionTime": "142.180587ms",
        "resultCount": 3,
        "resultSize": 1343
    }
}

Result is, data came through; however:

"status": "success",
"metrics": {
    "elapsedTime": "1m59.133538996s",
    "executionTime": "1m59.133494919s",
    "resultCount": 215,
    "resultSize": 58884,
    "sortCount": 215
}

Merging from:

I don’t know if \set -max_parallelism 8; is “saved” on the server or for the season, but it seemed after setting it, two different cbq instances “shared” it. Could you tell me which is the case?

Also, these settings fail: (First time I’ve seen so much red on a result!)

cbq> \set -pipeline_batch 256k;
cbq> \set -pipeline_cap 256k;

    "errors": [
        {
            "code": 1065,
            "msg": "Unrecognized parameter in request: pipeline_batch"
        }
    ],

4x CPU server, both with \set -max_parallelism 8; and \set -max_parallelism 4; seems well-saturated when querying (with htop reporting minor io-wait CPU time [<3%])

Query format didn’t seem to affect running times too much;

SELECT did, ARRAY_AGG(DISTINCT SUBSTR(t, 0, 10)) AS dates, {
	"full" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
	"null" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
	"sums" : COUNT(*)
} AS entries
FROM sync_gateway
WHERE type="bcn_scan" AND SUBSTR(t, 0, 10) BETWEEN "2016-11-01" AND "2016-12-15"
GROUP BY did
ORDER BY did ASC;

"metrics": {
    "elapsedTime": "56.951166373s",
    "executionTime": "56.951118663s",
    "resultCount": 8,
    "resultSize": 8317,
    "sortCount": 8
}

and

SELECT RAW
    {
        date :
        ARRAY_SORT( ARRAY_AGG( counts ) )
    }
FROM (
    SELECT
        SUBSTR(t, 0, 10) AS date,
        {
            did : {
                "foull_entries" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
                "total_entries" : COUNT(*),
                "nulld_entries" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(bcn), 0))
            }
        } AS counts
    FROM sync_gateway
    WHERE
        type="bcn_scan"
        AND SUBSTR(t, 0, 10) BETWEEN "2016-11-01" AND "2016-12-15"
        AND did IS NOT NULL
    GROUP BY SUBSTR(t, 0, 10), did
) AS s
GROUP BY date
ORDER BY date;


"metrics": {
    "elapsedTime": "58.590618023s",
    "executionTime": "58.590573467s",
    "resultCount": 45,
    "resultSize": 56924,
    "sortCount": 45
}

Seem to yield the same results more or less (~1.5 sec difference). Unless you meant something different with:

to which, I am open to suggestions if you have to propose something even faster (within the N1QL confines of course).


However, COUNT(*) functions seem to perform differently. I am just writing them for the people that may visit the topic.

N.B.!: You have to \set max_parallelism # to see the ‘small’ differences. Default max_parallelism (I think 1) gives major differences between the 1st and the 2nd/3rd places (whereas in-between 2nd/3rd place, unoptimized, there is no difference).

"full" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
"null" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
"sums" : COUNT(*)

"metrics": {
    "elapsedTime": "56.951166373s",
    "executionTime": "56.951118663s",
    "resultCount": 8,
    "resultSize": 8317,
    "sortCount": 8
}

and

"full" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
"null" : COUNT(CASE WHEN ARRAY_LENGTH(bcn)==0 THEN 1 ELSE NULL END),
"sums" : COUNT(*)

"metrics": {
    "elapsedTime": "1m7.820849181s",
    "executionTime": "1m7.820799484s",
    "resultCount": 8,
    "resultSize": 8317,
    "sortCount": 8
}

and

"full" : ARRAY_SUM(ARRAY_AGG(TONUMBER(ARRAY_LENGTH(bcn)<>0))), 
"null" : ARRAY_SUM(ARRAY_AGG(TONUMBER(ARRAY_LENGTH(bcn)==0))),
"sums" : COUNT(*)

"metrics": {
    "elapsedTime": "1m8.286570522s",
    "executionTime": "1m8.286494713s",
    "resultCount": 8,
    "resultSize": 8317,
    "sortCount": 8
}

My apologies. Of those settings, only max_parallelism is currently supported in the GA version you are running.

If there is no significant speed up, that is probably where things stand. You cannot use GROUP BY with index order.

What is “index order”?

Index order is an optimization whereby the ORDER BY is implemented by preserving the order of the items as they are returned by the index, rather than implementing the ORDER BY by sorting the items.

1 Like

I tried to make a more complex query

SELECT bcn, did, type, t, battstat, charge FROM sync_gateway
WHERE type="bcn_scan" AND NOT (
	(
		SUBSTR(t, 0, 10) BETWEEN "2015-01-03" AND "2015-04-30" OR
		SUBSTR(t, 0, 10) BETWEEN "2016-05-15" AND "2016-08-15" OR
		SUBSTR(t, 0, 10) BETWEEN "2016-09-01" AND "2016-06-30" OR
		SUBSTR(t, 0, 10) BETWEEN "2016-01-15" AND "2016-06-26" OR
		SUBSTR(t, 0, 10) BETWEEN "2016-11-02" AND "2016-12-13" 
	) AND (
		did="63AF154DBDAF245176753E36EB0AE78F" OR
		did="9D5E0987B0A26F08697407C5EFD83E50" OR
		did="5486C01B2EE5A30FBD342A6D49933DF9" OR
		did="DA2D16DA2AB37D61A706F0C9BD808E00" OR
		did="066889F99301F4B0C6BE94F87FE582AC" OR
		did="D19C9727FDCBE0764FDFCBFCE5893135"
	) OR
	(
		SUBSTR(t, 0, 10) BETWEEN "2016-03-06" AND "2016-01-14" OR
		SUBSTR(t, 0, 10) BETWEEN "2016-06-18" AND "2016-08-30" OR
		SUBSTR(t, 0, 10) BETWEEN "2016-09-15" AND "2016-01-26" OR
		SUBSTR(t, 0, 10) BETWEEN "2016-11-02" AND "2016-12-13"
	) AND (
		did="DF0EA83DA465D7A34FC8BDCF8BD54B1D" OR
		did="64CE31AB85A61002EC4A5DF7A609A1C6"
	)
);

However, index

CREATE INDEX `bcn_scan-date-did` ON sync_gateway(SUBSTR(t, 0, 10), did) WHERE type='bcn_scan';

Does not kick in. Is there a problem using NOT and/or having multiple predicates (with mixed ANDs/ORs)?

For now, use a UNION ALL instead of an OR. This is a limitation which has been fixed for upcoming releases.

Also, use

did IN [ ... ]

instead of OR.

1 Like

When you say

You mean get rid of “every” OR?
In my mind, to arrive to a close logic equivalent, I need to INTERSECT instead of UNION. And I guess I cannot even write ALL, because I plan to make a DELETE out of this

You can use UNION instead of UNION ALL, just for the outermost ORs.

For the inner ORs, try converting them to IN if possible. After that, see if your index gets picked up.

Also try the Spock developer build for Jan or Feb. One of those will have the fix for OR.

N.B.: for clarity purposes I mininimized the query

EXPLAIN SELECT query.did, ARRAY_AGG(DISTINCT SUBSTR(query.t, 0, 10)) AS days, {
    "sums" : COUNT(*),
    "null" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(query.bcn), 0)),
    "full" : COUNT(NULLIF(ARRAY_LENGTH(query.bcn), 0))
} AS entries
FROM (
    SELECT did, SUBSTR(t, 0, 10), bcn
    FROM sync_gateway
    WHERE type="bcn_scan" AND NOT (
        SUBSTR(t, 0, 10) IN ["2015-01-03", "2015-01-04", "2015-01-05",
        "2015-01-06", "2015-01-07", "2015-01-08", "2015-01-09", "2015-01-10",
        "2015-01-11", "2015-01-12", "2015-01-13", "2015-01-14", "2015-01-15",
        "2015-01-16", "2015-01-17", "2015-01-18", "2015-01-19", "2015-01-20",
        "2015-01-21", "2015-01-22", "2015-01-23", "2015-01-24", "2015-01-25",
        "2015-01-26", "2015-01-27", "2015-01-28", "2015-01-29", "2015-01-30",
        "2015-01-31", "2015-02-01", "2015-02-02", "2015-02-03", "2015-02-04",
        "2015-02-05", "2015-02-06", "2015-02-07", "2015-02-08", "2015-02-09",
        "2015-02-10", "2015-02-11", "2015-02-12", "2015-02-13", "2015-02-14",
        "2015-02-15", "2015-02-16", "2015-02-17", "2015-02-18", "2015-02-19",
        "2015-02-20", "2015-02-21", "2015-02-22", "2015-02-23", "2015-02-24",
        "2015-02-25", "2015-02-26", "2015-02-27", "2015-02-28", "2015-03-01",
        "2015-03-02", "2015-03-03", "2015-03-04", "2015-03-05", "2015-03-06",
        "2015-03-07", "2015-03-08", "2015-03-09", "2015-03-10", "2015-03-11",
        "2015-03-12", "2015-03-13", "2015-03-14", "2015-03-15", "2015-03-16",
        "2015-03-17", "2015-03-18", "2015-03-19", "2015-03-20", "2015-03-21",
        "2015-03-22", "2015-03-23", "2015-03-24", "2015-03-25", "2015-03-26",
        "2015-03-27", "2015-03-28", "2015-03-29", "2015-03-30", "2015-03-31",
        "2015-04-01", "2015-04-02", "2015-04-03", "2015-04-04", "2015-04-05",
        "2015-04-06", "2015-04-07", "2015-04-08", "2015-04-09", "2015-04-10",
        "2015-04-11", "2015-04-12", "2015-04-13", "2015-04-14", "2015-04-15",
        "2015-04-16", "2015-04-17", "2015-04-18", "2015-04-19", "2015-04-20",
        "2015-04-21", "2015-04-22", "2015-04-23", "2015-04-24", "2015-04-25",
        "2015-04-26", "2015-04-27", "2015-04-28", "2015-04-29", "2015-04-30"]
    AND did IN [
        "63AF154DBDAF245176753E36EB0AE78F", "9D5E0987B0A26F08697407C5EFD83E50",
        "5486C01B2EE5A30FBD342A6D49933DF9", "DA2D16DA2AB37D61A706F0C9BD808E00",
        "066889F99301F4B0C6BE94F87FE582AC", "D19C9727FDCBE0764FDFCBFCE5893135"]
    )

    UNION

    SELECT did, SUBSTR(t, 0, 10), bcn
    FROM sync_gateway
    WHERE type="bcn_scan" AND NOT (
        SUBSTR(t, 0, 10) IN ["2015-06-18", "2015-06-19", "2015-06-20",
        "2015-06-21", "2015-06-22", "2015-06-23", "2015-06-24", "2015-06-25",
        "2015-06-26", "2015-06-27", "2015-06-28", "2015-06-29", "2015-06-30"]
    AND did IN [
        "DF0EA83DA465D7A34FC8BDCF8BD54B1D", "64CE31AB85A61002EC4A5DF7A609A1C6"]
    )
) AS query
GROUP BY did
ORDER BY did ASC;

Connected to : http://localhost:8091/. Type Ctrl-D or \QUIT to exit.

Path to history file for the shell : /root/.cbq_history

{
    "requestID": "e073b551-6569-4fe4-9980-c14ae763d122",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Sequence",
                                "~children": [
                                    {
                                        "#operator": "UnionAll",
                                        "children": [
                                            {
                                                "#operator": "Sequence",
                                                "~children": [
                                                    {
                                                        "#operator": "PrimaryScan",
                                                        "index": "#primary",
                                                        "keyspace": "sync_gateway",
                                                        "namespace": "default",
                                                        "using": "gsi"
                                                    },
                                                    {
                                                        "#operator": "Fetch",
                                                        "keyspace": "sync_gateway",
                                                        "namespace": "default"
                                                    },
                                                    {
                                                        "#operator": "Parallel",
                                                        "~child": {
                                                            "#operator": "Sequence",
                                                            "~children": [
                                                                {
                                                                    "#operator": "Filter",
                                                                    "condition": "(((`sync_gateway`.`type`) = \"bcn_scan\") and (not ((substr((`sync_gateway`.`t`), 0, 10) in [\"2015-01-03\", \"2015-01-04\", \"2015-01-05\", \"2015-01-06\", \"2015-01-07\", \"2015-01-08\", \"2015-01-09\", \"2015-01-10\", \"2015-01-11\", \"2015-01-12\", \"2015-01-13\", \"2015-01-14\", \"2015-01-15\", \"2015-01-16\", \"2015-01-17\", \"2015-01-18\", \"2015-01-19\", \"2015-01-20\", \"2015-01-21\", \"2015-01-22\", \"2015-01-23\", \"2015-01-24\", \"2015-01-25\", \"2015-01-26\", \"2015-01-27\", \"2015-01-28\", \"2015-01-29\", \"2015-01-30\", \"2015-01-31\", \"2015-02-01\", \"2015-02-02\", \"2015-02-03\", \"2015-02-04\", \"2015-02-05\", \"2015-02-06\", \"2015-02-07\", \"2015-02-08\", \"2015-02-09\", \"2015-02-10\", \"2015-02-11\", \"2015-02-12\", \"2015-02-13\", \"2015-02-14\", \"2015-02-15\", \"2015-02-16\", \"2015-02-17\", \"2015-02-18\", \"2015-02-19\", \"2015-02-20\", \"2015-02-21\", \"2015-02-22\", \"2015-02-23\", \"2015-02-24\", \"2015-02-25\", \"2015-02-26\", \"2015-02-27\", \"2015-02-28\", \"2015-03-01\", \"2015-03-02\", \"2015-03-03\", \"2015-03-04\", \"2015-03-05\", \"2015-03-06\", \"2015-03-07\", \"2015-03-08\", \"2015-03-09\", \"2015-03-10\", \"2015-03-11\", \"2015-03-12\", \"2015-03-13\", \"2015-03-14\", \"2015-03-15\", \"2015-03-16\", \"2015-03-17\", \"2015-03-18\", \"2015-03-19\", \"2015-03-20\", \"2015-03-21\", \"2015-03-22\", \"2015-03-23\", \"2015-03-24\", \"2015-03-25\", \"2015-03-26\", \"2015-03-27\", \"2015-03-28\", \"2015-03-29\", \"2015-03-30\", \"2015-03-31\", \"2015-04-01\", \"2015-04-02\", \"2015-04-03\", \"2015-04-04\", \"2015-04-05\", \"2015-04-06\", \"2015-04-07\", \"2015-04-08\", \"2015-04-09\", \"2015-04-10\", \"2015-04-11\", \"2015-04-12\", \"2015-04-13\", \"2015-04-14\", \"2015-04-15\", \"2015-04-16\", \"2015-04-17\", \"2015-04-18\", \"2015-04-19\", \"2015-04-20\", \"2015-04-21\", \"2015-04-22\", \"2015-04-23\", \"2015-04-24\", \"2015-04-25\", \"2015-04-26\", \"2015-04-27\", \"2015-04-28\", \"2015-04-29\", \"2015-04-30\"]) and ((`sync_gateway`.`did`) in [\"63AF154DBDAF245176753E36EB0AE78F\", \"9D5E0987B0A26F08697407C5EFD83E50\", \"5486C01B2EE5A30FBD342A6D49933DF9\", \"DA2D16DA2AB37D61A706F0C9BD808E00\", \"066889F99301F4B0C6BE94F87FE582AC\", \"D19C9727FDCBE0764FDFCBFCE5893135\"]))))"
                                                                },
                                                                {
                                                                    "#operator": "InitialProject",
                                                                    "result_terms": [
                                                                        {
                                                                            "expr": "(`sync_gateway`.`did`)"
                                                                        },
                                                                        {
                                                                            "expr": "substr((`sync_gateway`.`t`), 0, 10)"
                                                                        },
                                                                        {
                                                                            "expr": "(`sync_gateway`.`bcn`)"
                                                                        }
                                                                    ]
                                                                },
                                                                {
                                                                    "#operator": "Distinct"
                                                                },
                                                                {
                                                                    "#operator": "FinalProject"
                                                                }
                                                            ]
                                                        }
                                                    },
                                                    {
                                                        "#operator": "Distinct"
                                                    }
                                                ]
                                            },
                                            {
                                                "#operator": "Sequence",
                                                "~children": [
                                                    {
                                                        "#operator": "PrimaryScan",
                                                        "index": "#primary",
                                                        "keyspace": "sync_gateway",
                                                        "namespace": "default",
                                                        "using": "gsi"
                                                    },
                                                    {
                                                        "#operator": "Fetch",
                                                        "keyspace": "sync_gateway",
                                                        "namespace": "default"
                                                    },
                                                    {
                                                        "#operator": "Parallel",
                                                        "~child": {
                                                            "#operator": "Sequence",
                                                            "~children": [
                                                                {
                                                                    "#operator": "Filter",
                                                                    "condition": "(((`sync_gateway`.`type`) = \"bcn_scan\") and (not ((substr((`sync_gateway`.`t`), 0, 10) in [\"2015-06-18\", \"2015-06-19\", \"2015-06-20\", \"2015-06-21\", \"2015-06-22\", \"2015-06-23\", \"2015-06-24\", \"2015-06-25\", \"2015-06-26\", \"2015-06-27\", \"2015-06-28\", \"2015-06-29\", \"2015-06-30\"]) and ((`sync_gateway`.`did`) in [\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\", \"64CE31AB85A61002EC4A5DF7A609A1C6\"]))))"
                                                                },
                                                                {
                                                                    "#operator": "InitialProject",
                                                                    "result_terms": [
                                                                        {
                                                                            "expr": "(`sync_gateway`.`did`)"
                                                                        },
                                                                        {
                                                                            "expr": "substr((`sync_gateway`.`t`), 0, 10)"
                                                                        },
                                                                        {
                                                                            "expr": "(`sync_gateway`.`bcn`)"
                                                                        }
                                                                    ]
                                                                },
                                                                {
                                                                    "#operator": "Distinct"
                                                                },
                                                                {
                                                                    "#operator": "FinalProject"
                                                                }
                                                            ]
                                                        }
                                                    },
                                                    {
                                                        "#operator": "Distinct"
                                                    }
                                                ]
                                            }
                                        ]
                                    },
                                    {
                                        "#operator": "Distinct"
                                    }
                                ]
                            },
                            {
                                "#operator": "Alias",
                                "as": "query"
                            },
                            {
                                "#operator": "Parallel",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "InitialGroup",
                                            "aggregates": [
                                                "array_agg(distinct substr((`query`.`t`), 0, 10))",
                                                "count(*)",
                                                "count(nullif(array_length((`query`.`bcn`)), 0))"
                                            ],
                                            "group_keys": [
                                                "(`query`.`did`)"
                                            ]
                                        }
                                    ]
                                }
                            },
                            {
                                "#operator": "IntermediateGroup",
                                "aggregates": [
                                    "array_agg(distinct substr((`query`.`t`), 0, 10))",
                                    "count(*)",
                                    "count(nullif(array_length((`query`.`bcn`)), 0))"
                                ],
                                "group_keys": [
                                    "(`query`.`did`)"
                                ]
                            },
                            {
                                "#operator": "FinalGroup",
                                "aggregates": [
                                    "array_agg(distinct substr((`query`.`t`), 0, 10))",
                                    "count(*)",
                                    "count(nullif(array_length((`query`.`bcn`)), 0))"
                                ],
                                "group_keys": [
                                    "(`query`.`did`)"
                                ]
                            },
                            {
                                "#operator": "Parallel",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "InitialProject",
                                            "result_terms": [
                                                {
                                                    "expr": "(`query`.`did`)"
                                                },
                                                {
                                                    "as": "days",
                                                    "expr": "array_agg(distinct substr((`query`.`t`), 0, 10))"
                                                },
                                                {
                                                    "as": "entries",
                                                    "expr": "{\"\\\"full\\\"\": count(nullif(array_length((`query`.`bcn`)), 0)), \"\\\"null\\\"\": (count(*) - count(nullif(array_length((`query`.`bcn`)), 0))), \"\\\"sums\\\"\": count(*)}"
                                                }
                                            ]
                                        }
                                    ]
                                }
                            }
                        ]
                    },
                    {
                        "#operator": "Order",
                        "sort_terms": [
                            {
                                "expr": "(`query`.`did`)"
                            }
                        ]
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            },
            "text": "SELECT query.did, ARRAY_AGG(DISTINCT SUBSTR(query.t, 0, 10)) AS days, {\n    \"sums\" : COUNT(*),\n    \"null\" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(query.bcn), 0)),\n    \"full\" : COUNT(NULLIF(ARRAY_LENGTH(query.bcn), 0))\n} AS entries\nFROM (\n    SELECT did, SUBSTR(t, 0, 10), bcn\n    FROM sync_gateway\n    WHERE type=\"bcn_scan\" AND NOT (\n        SUBSTR(t, 0, 10) IN [\"2015-01-03\", \"2015-01-04\", \"2015-01-05\",\n        \"2015-01-06\", \"2015-01-07\", \"2015-01-08\", \"2015-01-09\", \"2015-01-10\",\n        \"2015-01-11\", \"2015-01-12\", \"2015-01-13\", \"2015-01-14\", \"2015-01-15\",\n        \"2015-01-16\", \"2015-01-17\", \"2015-01-18\", \"2015-01-19\", \"2015-01-20\",\n        \"2015-01-21\", \"2015-01-22\", \"2015-01-23\", \"2015-01-24\", \"2015-01-25\",\n        \"2015-01-26\", \"2015-01-27\", \"2015-01-28\", \"2015-01-29\", \"2015-01-30\",\n        \"2015-01-31\", \"2015-02-01\", \"2015-02-02\", \"2015-02-03\", \"2015-02-04\",\n        \"2015-02-05\", \"2015-02-06\", \"2015-02-07\", \"2015-02-08\", \"2015-02-09\",\n        \"2015-02-10\", \"2015-02-11\", \"2015-02-12\", \"2015-02-13\", \"2015-02-14\",\n        \"2015-02-15\", \"2015-02-16\", \"2015-02-17\", \"2015-02-18\", \"2015-02-19\",\n        \"2015-02-20\", \"2015-02-21\", \"2015-02-22\", \"2015-02-23\", \"2015-02-24\",\n        \"2015-02-25\", \"2015-02-26\", \"2015-02-27\", \"2015-02-28\", \"2015-03-01\",\n        \"2015-03-02\", \"2015-03-03\", \"2015-03-04\", \"2015-03-05\", \"2015-03-06\",\n        \"2015-03-07\", \"2015-03-08\", \"2015-03-09\", \"2015-03-10\", \"2015-03-11\",\n        \"2015-03-12\", \"2015-03-13\", \"2015-03-14\", \"2015-03-15\", \"2015-03-16\",\n        \"2015-03-17\", \"2015-03-18\", \"2015-03-19\", \"2015-03-20\", \"2015-03-21\",\n        \"2015-03-22\", \"2015-03-23\", \"2015-03-24\", \"2015-03-25\", \"2015-03-26\",\n        \"2015-03-27\", \"2015-03-28\", \"2015-03-29\", \"2015-03-30\", \"2015-03-31\",\n        \"2015-04-01\", \"2015-04-02\", \"2015-04-03\", \"2015-04-04\", \"2015-04-05\",\n        \"2015-04-06\", \"2015-04-07\", \"2015-04-08\", \"2015-04-09\", \"2015-04-10\",\n        \"2015-04-11\", \"2015-04-12\", \"2015-04-13\", \"2015-04-14\", \"2015-04-15\",\n        \"2015-04-16\", \"2015-04-17\", \"2015-04-18\", \"2015-04-19\", \"2015-04-20\",\n        \"2015-04-21\", \"2015-04-22\", \"2015-04-23\", \"2015-04-24\", \"2015-04-25\",\n        \"2015-04-26\", \"2015-04-27\", \"2015-04-28\", \"2015-04-29\", \"2015-04-30\"]\n    AND did IN [\n        \"63AF154DBDAF245176753E36EB0AE78F\", \"9D5E0987B0A26F08697407C5EFD83E50\",\n        \"5486C01B2EE5A30FBD342A6D49933DF9\", \"DA2D16DA2AB37D61A706F0C9BD808E00\",\n        \"066889F99301F4B0C6BE94F87FE582AC\", \"D19C9727FDCBE0764FDFCBFCE5893135\"]\n    )\n\n    UNION\n\n    SELECT did, SUBSTR(t, 0, 10), bcn\n    FROM sync_gateway\n    WHERE type=\"bcn_scan\" AND NOT (\n        SUBSTR(t, 0, 10) IN [\"2015-06-18\", \"2015-06-19\", \"2015-06-20\",\n        \"2015-06-21\", \"2015-06-22\", \"2015-06-23\", \"2015-06-24\", \"2015-06-25\",\n        \"2015-06-26\", \"2015-06-27\", \"2015-06-28\", \"2015-06-29\", \"2015-06-30\"]\n    AND did IN [\n        \"DF0EA83DA465D7A34FC8BDCF8BD54B1D\", \"64CE31AB85A61002EC4A5DF7A609A1C6\"]\n    )\n) AS query\nGROUP BY did\nORDER BY did ASC;"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "47.290783ms",
        "executionTime": "47.206988ms",
        "resultCount": 1,
        "resultSize": 18601
    }
}

However, this query is perfectly fine for the index:

SELECT did, ARRAY_AGG(DISTINCT SUBSTR(t, 0, 10)) AS days, {
    "sums" : COUNT(*),
    "null" : COUNT(*) - COUNT(NULLIF(ARRAY_LENGTH(bcn), 0)),
    "full" : COUNT(NULLIF(ARRAY_LENGTH(bcn), 0))
} AS entries
FROM sync_gateway
WHERE type="bcn_scan" AND
SUBSTR(t, 0, 10) IN ["2015-06-18", "2015-06-19", "2015-06-20",
    "2015-06-21", "2015-06-22", "2015-06-23", "2015-06-24", "2015-06-25",
    "2015-06-26", "2015-06-27", "2015-06-28", "2015-06-29", "2015-06-30"]
AND did IN ["DF0EA83DA465D7A34FC8BDCF8BD54B1D"]
GROUP BY did
ORDER BY did ASC;

As this is as well:

EXPLAIN SELECT * FROM sync_gateway WHERE type="bcn_scan" AND (
SUBSTR(t, 0, 10) BETWEEN "2015-06-18" AND "2015-06-30" OR
SUBSTR(t, 0, 10) BETWEEN "2015-03-01" AND "2015-03-31") AND
did="DF0EA83DA465D7A34FC8BDCF8BD54B1D";


 Connected to : http://localhost:8091/. Type Ctrl-D or \QUIT to exit.

 Path to history file for the shell : /root/.cbq_history 
{
    "requestID": "a0b5e067-fc10-46de-b89b-68736034381d",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "DistinctScan",
                        "scan": {
                            "#operator": "IndexScan",
                            "index": "bcn_scan-date-did",
                            "index_id": "d37e2d41290035c3",
                            "keyspace": "sync_gateway",
                            "namespace": "default",
                            "spans": [
                                {
                                    "Range": {
                                        "High": [
                                            "\"2015-06-30\"",
                                            "\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\""
                                        ],
                                        "Inclusion": 3,
                                        "Low": [
                                            "\"2015-06-18\"",
                                            "\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\""
                                        ]
                                    }
                                },
                                {
                                    "Range": {
                                        "High": [
                                            "\"2015-03-31\"",
                                            "\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\""
                                        ],
                                        "Inclusion": 3,
                                        "Low": [
                                            "\"2015-03-01\"",
                                            "\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\""
                                        ]
                                    }
                                }
                            ],
                            "using": "gsi"
                        }
                    },
                    {
                        "#operator": "Fetch",
                        "keyspace": "sync_gateway",
                        "namespace": "default"
                    },
                    {
                        "#operator": "Parallel",
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "Filter",
                                    "condition": "((((`sync_gateway`.`type`) = \"bcn_scan\") and ((substr((`sync_gateway`.`t`), 0, 10) between \"2015-06-18\" and \"2015-06-30\") or (substr((`sync_gateway`.`t`), 0, 10) between \"2015-03-01\" and \"2015-03-31\"))) and ((`sync_gateway`.`did`) = \"DF0EA83DA465D7A34FC8BDCF8BD54B1D\"))"
                                },
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "self",
                                            "star": true
                                        }
                                    ]
                                },
                                {
                                    "#operator": "FinalProject"
                                }
                            ]
                        }
                    }
                ]
            },
            "text": "SELECT * FROM sync_gateway WHERE type=\"bcn_scan\" AND (\nSUBSTR(t, 0, 10) BETWEEN \"2015-06-18\" AND \"2015-06-30\" OR\nSUBSTR(t, 0, 10) BETWEEN \"2015-03-01\" AND \"2015-03-31\") AND\ndid=\"DF0EA83DA465D7A34FC8BDCF8BD54B1D\";"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "26.685666ms",
        "executionTime": "26.595777ms",
        "resultCount": 1,
        "resultSize": 3799
    }
}

I use docker which I am not quite familiar with, so I am not sure how to create a container with a dev build.
And I am running real limited on resources, so, it’s not an option to build a scrap build on a different “physical computer”

Can you change

NOT ( x IN … )

to

X NOT IN …

After that, what are we looking at? Is the index working as desired? The printout is long, so I haven’t had a chance to look at it. If it shows IndexScans and no PrimaryScans, you are doing ok.

According to De Morgans Law:

WHERE type="..." AND NOT (SUBSTR(t, 0, 10) IN [...] AND did IN [...])

WHERE type="..." AND (SUBSTR(t, 0, 10) NOT IN [...] OR did NOT IN [...])

So I will still have an OR to handle. (Two if you think I also have one extra UNIONed query)

I’ve seen this:

I am not sure how to JOIN, especially since I am joining documents with themselves.
I guess I will wait some docker-released version or just run without index

it’s ok, As image you post here, Table_A and Table_B can be the same bucket.

Hi @sntentos,

Back to you. Please try the suggestions here If you have a specific question after that, please post just the narrow specific question on a new thread…

1 Like