Create index optimize

SQL++
1

pls how to create optimize index for this query

SELECT aggregatedRes.qty, aggregatedRes.amt, aggregatedRes.dat, 
       aggregatedRes.sit, aggregatedRes.off, aggregatedRes.tk, aggregatedRes.datInt, aggregatedRes.crd, aggregatedRes.startDate, aggregatedRes.endDate 
FROM ( SELECT ARRAY_AGG(qty)[0] AS qty, ARRAY_AGG(amt)[0] AS amt, 
              dat, sit, off, tk, datInt, crd, 1672531200000 AS startDate,1689033600000 AS endDate 
       FROM ( SELECT ROUND ( SUM(content.qty), 8) as qty, content.dat, content.sit, 
                     content.off, content.tk, content.datInt, content.crd 
              FROM rxpReport 
              WHERE type = 'aD' AND content.a = 'TDCL' AND content.sD BETWEEN 1672531200000 AND 1689033600000 
              GROUP BY content.dat, content.sit, content.off, content.tk, content.datInt, content.crd 

             UNION 
           
              SELECT ROUND ( SUM(content.amt), 8) as amt, content.dat, content.sit, 
                           content.off, content.tk, content.datInt, content.crd 
             FROM rxpReport WHERE type = 'aD' AND content.a = 'TDCL' AND content.sD BETWEEN 1672531200000 AND 1689033600000 
            GROUP BY content.dat, content.sit, content.off, content.tk, content.datInt, content.crd) AS res 
       GROUP BY res.dat, res.sit, res.off, res.tk, res.datInt, res.crd) as aggregatedRes 
WHERE aggregatedRes.qty IS NOT NULL OR aggregatedRes.amt IS NOT NULL
2 
CREATE INDEX ix1 ON rxpReport(content.a, content.sD, content.dat, content.sit, content.off, content.tk, content.datInt, content.crd, content.amt, content.qty) WHERE type = 'aD';


SELECT content.dat, content.sit, content.off, content.tk, content.datInt, content.crd,
       qty, amt, 1672531200000 AS startDate,1689033600000 AS endDate
FROM rxpReport AS r
WHERE type = 'aD' AND content.a = 'TDCL' AND content.sD BETWEEN 1672531200000 AND 1689033600000
GROUP BY content.dat, content.sit, content.off, content.tk, content.datInt, content.crd
LETTING qty = ROUND ( SUM(content.qty), 8),
        amt = ROUND ( SUM(content.amt), 8)
HAVING qty IS NOT NULL OR amt IS NOT NULL;
1 Like
3

thks for reply so you optimize the query exact? and the ARRAY_AGG in qty and amt???

4

ARRAY_AGG(qty)[0] AS qty, ARRAY_AGG(amt)[0] AS amt,

It is exact. You can verify results.

Why do u need ARRAY_AGG()? My opinion it is not needed (You do UNION each produce sum of qty, amt and then again GROUP of ARRA_AGG()[0], this is same as without UNION do SUM of qty, amt at same time)

1 Like
5

yes it’s same results thks a lot
I’m newbie in couchbase

6

hello @vsr1
if now I have this query
SELECT aggregatedRes.qty, aggregatedRes.amt, aggregatedRes.startDate, aggregatedRes.endDate
FROM ( SELECT ARRAY_AGG(qty)[0] AS qty, ARRAY_AGG(amt)[0] AS amt, 1672876800000 AS startDate,1672963199000 AS endDate
FROM ( SELECT ROUND ( SUM(content.qty), 8) as qty
FROM rxpReport
WHERE type = ‘aD’ AND content.a = ‘FTEB’ AND content.sD BETWEEN 1672876800000 AND 1672963199000
UNION SELECT ROUND ( SUM(content.amt), 8) as amt
FROM rxpReport
WHERE type = ‘aD’ AND content.a = ‘FTEB’ AND content.sD BETWEEN 1672876800000 AND 1672963199000) AS res ) as aggregatedRes
WHERE aggregatedRes.qty IS NOT NULL OR aggregatedRes.amt IS NOT NULL
and want to do as you advise me
SELECT qty, amt, 1672704000000 AS startDate,1672790399000 AS endDate
FROM rxpReport
WHERE type = ‘aD’ AND content.a = ‘FTEB’ AND content.sD BETWEEN 1672704000000 AND 1672790399000
LETTING qty = ROUND ( SUM(content.qty), 8), amt = ROUND ( SUM(content.amt), 8)
HAVING qty IS NOT NULL OR amt IS NOT NULL
as you see it’s not work because missing Group By clause so I do resolve it? thks

7 
SELECT q.*
FROM (SELECT ROUND ( SUM(content.qty), 8) AS qty, ROUND ( SUM(content.amt), 8) AS amt, 1672704000000 AS startDate, 1672790399000 AS endDate
      FROM rxpReport AS r
      WHERE type = 'aD' AND content.a = 'FTEB' AND content.sD BETWEEN 1672704000000 AND 1672790399000) AS q
WHERE  qty IS NOT NULL OR amt IS NOT NULL;
1 Like
8

@vsr1 thks for reply, so it’s not possible to use the LETTING clause for this case? because I want to customize query to generate query from input’s values or Letting clause in for others query optimize time execution so want it for all query

9

HAVING/LETTING only allowed when GROUP BY present. SQL standard will not allow HAVING caluse for NON GROUP aggregates.
Non GROUP aggregate query must return 1 row by having can HAVING will filter that .
If you need uniform query

SELECT d.*
FROM (SELECT content.dat, content.sit, content.off, content.tk, content.datInt, content.crd,
            ROUND ( SUM(content.qty), 8) AS qty, ROUND ( SUM(content.amt), 8) AS amt, 
            1672531200000 AS startDate,1689033600000 AS endDate
      FROM rxpReport AS r
      WHERE type = 'aD' AND content.a = 'TDCL' AND content.sD BETWEEN 1672531200000 AND 1689033600000
      GROUP BY content.dat, content.sit, content.off, content.tk, content.datInt, content.crd) AS d
WHERE qty IS NOT NULL OR amt IS NOT NULL;
1 Like
10

hello @vsr1
this query generate exception MetaData : CouchbaseException when upgrate it in v7: UPDATE rxpData SET d.salesChannelPositioning = ARRAY_PUT(d.salesChannelPositioning, ‘Merci’) FOR d IN content.data END WHERE META().id IN [“productTable::PTA621774”] how can I resolved it?

11

What is the SQL error you’re seeing? - Can you run it via cbq or the UI directly? And with which exact version (SELECT ds_version()) ? - I’ve tried it locally and it runs without issue using a minimal document I’ve cobbled together:

cbq> insert into rxpData values("productTable::PTA621774",{"content":{"data":[{"salesChannelPositioning":[]}]}});
...
cbq> UPDATE rxpData SET d.salesChannelPositioning = ARRAY_PUT(d.salesChannelPositioning, 'Merci') FOR d IN content.data END WHERE META().id IN ["productTable::PTA621774"] ;
{
    "requestID": "8ece3701-74c5-4085-b09f-f4dd549cbaa5",
    "signature": null,
    "results": [
    ],
    "status": "success",
...
cbq> select * from rxpData;
{
    "requestID": "5d09e273-7900-4324-b532-cd949f3c0b30",
    "signature": {
        "*": "*"
    },
    "results": [
    {
        "rxpData": {
            "content": {
                "data": [
                    {
                        "salesChannelPositioning": [
                            "Merci"
                        ]
                    }
                ]
            }
        }
    }
    ],
    "status": "success",
1 Like
12

In addition to @dh query as you know document key use USE KEYS vs IN in WHERE clause avoid index scan

UPDATE rxpData  USE KEYS ["productTable::PTA621774"] 
 SET d.salesChannelPositioning = ARRAY_PUT(d.salesChannelPositioning, 'Merci') FOR d IN content.data END
1 Like
13

thks for reply finally used USE KEYS as @vsr1 and it’s worked… once again thks u both

14

Hello @vsr1 pls how to optimize this query
SELECT d.* FROM rxpData AS b UNNEST b.content.data AS d WHERE b.type = ‘siteTable’ AND b.content.code = ‘STA133725’ AND NOT d.isDeleted AND CONTAINS( LOWER(d.siteCode), ‘22216’) ORDER BY d.siteCode ASC LIMIT 50 OFFSET 0

.

15

[It would be best if you could post new questions as new topics.]

The only thing I can spot is if your siteCode filter is a purely numeric character string as in the example, the the LOWER() transformation could be dropped.

You’re after the filtered array field elements for a sub-set of documents ordering the elements across all candidate documents; the order has to be applied by Query outside of the array (i.e. after UNNESTing). This means that all results have to be fetched in order to be sorted before the first 50 can be returned. If in the unlikely event ordering isn’t important, obviously dropping this would limit how many documents have to be fetched.

Obviously index appropriately to fetch as few documents as possible. A partial index (index with a filter) may be beneficial if the type and/or content.code filters are intended to be constant and are reasonably selective.

The addition of

AND ANY t IN b.content.data SATISFIES NOT t.isDeleted AND CONTAINS( LOWER(t.siteCode), '22216') END

to the WHERE clause could open up additional indexing options and index filtering.

HTH.

1 Like
16

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.