Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLMIU-002vKO-Qy for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 11:21:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qLMIT-008REO-ER for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 11:21:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLMIT-008RBX-4M for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 11:21:45 +0000 Received: from mail-wm1-x331.google.com ([2a00:1450:4864:20::331]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qLMIO-000tIr-1I for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 11:21:43 +0000 Received: by mail-wm1-x331.google.com with SMTP id 5b1f17b1804b1-3fbf1b82d9cso39143845e9.2 for ; Mon, 17 Jul 2023 04:21:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1689592898; x=1692184898; h=content-transfer-encoding:in-reply-to:references:to:from :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=L3xSznwqqxU0waIzbmBieFkpI7Nr7gSqfGsNgKUutT4=; b=hj0CDt2tuOwsiP/AE/CUyw5uNyVlrIyOGWcZ7wZ+DqQ3M3rK79C+G1dMzE+QnaMRzc 2cdgb3IFafH6IMWYEPHwRzfX4oN0mSPPTKd6disupbKzimpp0/k/ifXx0xVCs3SeVFdN 8HBzI8Q1KPy8qoXj59lsPr6tA8hRo/W53Qc1c/dUaXZk3wps6/0vADbhqhUMCHsTOcyA z7wmjOGnhlzdGjDxZuLmCNI+Yap6A/tl364VKGiadtfPUd8OMV4+dcAfx+9DnUqd6ASN rvlBFJ3jc+9MA0sPQMJL0aEhGqgsOs8Su6obRELegvN03gIMxJpK9PPuu1UO44jS7EIK Nutg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689592898; x=1692184898; h=content-transfer-encoding:in-reply-to:references:to:from :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=L3xSznwqqxU0waIzbmBieFkpI7Nr7gSqfGsNgKUutT4=; b=czXhar28vLygeWKmPTwKMbhsPq588JW47dWz5k83JXBjPv8OF7VBV1b9sEIyiHGXqr b0IZTujm5OfGxF5Pug0CDbMlsh3Lvd+imRQWM+i1xXJ46WQmOSDQcQEbOTAUfFxAMqed OOD5Q160SQJvXcHasbwxJLqz3ndMHG2hIt+gNiWfUX2+MGk5uMA9jAifTwOnnHKp68Ou 35ZoNX1onIn3UjT7tkoSox7q3LZb066de2OTonEZ9enzSstqOzu5s3hnsZGGcyPvBS17 aH8MxG5Tj4GJQNaYMbg/kZ8UmamAQ+vYjRTGb+TMpV5A9aJp1AOuG0+L83YnxNtqUH1i v1eg== X-Gm-Message-State: ABy/qLZG5pBHwqk5xm+CzgdQsF0JBEvozmZLlCzr5IzuH2Fbfut4FyNJ aZa++3wxdZibQrXSldQp+zfOXa09khkgfRpfhD0= X-Google-Smtp-Source: APBJJlEfPNYk1F5to/G1yZWcfKothFOKrE9bkYYSL1aketxipTH6e5NhVJ8ourezI9tJ6eh7qxhCQg== X-Received: by 2002:a05:600c:21cb:b0:3fc:3f31:422f with SMTP id x11-20020a05600c21cb00b003fc3f31422fmr6780194wmj.3.1689592898494; Mon, 17 Jul 2023 04:21:38 -0700 (PDT) Received: from [10.137.0.17] (ip-86-49-228-220.bb.vodafone.cz. [86.49.228.220]) by smtp.gmail.com with ESMTPSA id v3-20020a05600c470300b003f7f475c3bcsm14901450wmo.1.2023.07.17.04.21.37 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 17 Jul 2023 04:21:38 -0700 (PDT) Message-ID: <7599651d-7269-ec5b-b968-46795a76c375@enterprisedb.com> Date: Mon, 17 Jul 2023 13:21:36 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Subject: Re: Query take a long time and use no index Content-Language: en-US From: Tomas Vondra To: basti , pgsql-general@lists.postgresql.org References: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de> In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 7/17/23 13:20, Tomas Vondra wrote: > ... > > It's always going to be slow with the COUNT(DISTINCT), I'm afraid. > > Not sure how much you can modify the query / database, and how accurate > results you need. If you're OK with estimates, you can try postgres-hll > extension [2] which estimates count(distinct). For exact reaults, I > wrote count_distinct extension [2] that uses hashtable. Might be worth a > try, I guess. > Damn, I forgot to add the links: [1] https://github.com/citusdata/postgresql-hll [2] https://github.com/tvondra/count_distinct regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company