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 1qLNt9-002ymx-4S for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 13:03:43 +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 1qLNt7-009PNG-Pf for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 13:03:41 +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 1qLNt7-009PN5-F7 for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 13:03:41 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qLNt5-000u4l-03 for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 13:03:40 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-1b078b34df5so3493697fac.2 for ; Mon, 17 Jul 2023 06:03:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689599018; x=1692191018; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=W79tB/ScsVj5HbDbjQkmLPLUIvqKTC+z3X4Z1r/XMMs=; b=JHZUQFWgnfcnmAKeUPnyj1AM1nUL/eokYzGkvR3XJP5aJNUDV3rnPMkFfinRt5hakp ZzlA9HD/drfUVDA3r7U+kdvI+ZVPIsDqYSHUvOpPOQmMUeunsJlGLDVyxN5deC7jVWm1 O0r8N87vBCDCtcHqmsh+hAcMrv9LO4QFwmrZopjXbH0uoF+eMTE1dWg4Zh+mvTMs24vZ CKggXAACwOgXMfio6z4KYp2EefucejNArblSJ2FZUgNIdrxefp8ltBfyi82Z1U2lEMXB LD4x+iVHvtQoQYCtmoOtNJPw7rLtsdiVGGVgmnVtdEIXZQrRZvFkUe2hkMRIhv++tRPO 5m2A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689599018; x=1692191018; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=W79tB/ScsVj5HbDbjQkmLPLUIvqKTC+z3X4Z1r/XMMs=; b=SMw5Ezlmx/bh2lETRqsRwcY5kx7jIc6MRidmgcyFcFvZTelQ6lvs5B0hXTIHCFvA1u 0qgbq2a5DW4qfX17ob1NO+yKZE7o4AmrvoIYbAmkzW/vjL5yYIH2HHdazoFt1yGKYsXJ A9Mb4degj7tR7fjvP0iUEcfmq3Xi7uqZXbxVugyPxzauLWnU3FRdr6fBOxzFmh6lv1N1 QGSsaN7I88yjgvdERDFuuwmIG0gV+vNMkUTuBSqMVMLwL7REcNVu7ew9HsMdHq5m+nTS qZUCF/+IZmVkeb6/t/DfxNTdd+SuJquXvlUXsoXXsLxaANn7gQS2XKbNvKGkPXmuNQUT CRxQ== X-Gm-Message-State: ABy/qLZ9pOb1fVjiLbdTf9mB9yAWDWaiEn2MKHOiknZv5cxychs6LlNB bzFVtCaSC0OOtU+6xfKqJTlpQWN6Kds= X-Google-Smtp-Source: APBJJlFRdy8s84Bgc7dYZScQAS2wJ1LKDtDY3z7QP1a1ZGbYK00VAYaRJCeeN3copjLPeRdmLrah+A== X-Received: by 2002:a05:6808:21a3:b0:39a:a146:8bfe with SMTP id be35-20020a05680821a300b0039aa1468bfemr13901918oib.12.1689599017989; Mon, 17 Jul 2023 06:03:37 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:359d:e2cf:1438:36be? ([2600:1700:bd2c:b810:359d:e2cf:1438:36be]) by smtp.googlemail.com with ESMTPSA id v30-20020a25aba1000000b00c832ad2e2eesm3000645ybi.60.2023.07.17.06.03.37 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 17 Jul 2023 06:03:37 -0700 (PDT) Message-ID: <4f0ba00d-5450-a638-f181-cd906682c7fa@gmail.com> Date: Mon, 17 Jul 2023 08:03:36 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Query take a long time and use no index Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de> From: Ron In-Reply-To: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 7/17/23 04:13, basti wrote: [snip] > > The Indexes: > > volkszaehler=# SELECT tablename,indexname,indexdef FROM pg_indexes WHERE > tablename LIKE 'data%' ORDER BY tablename,indexname; > >  tablename |           indexname            | indexdef > -----------+--------------------------------+-------------------------------------------------------------------------------------------------- > >  data      | idx_16391_idx_adf3f36372f5a1aa | CREATE INDEX > idx_16391_idx_adf3f36372f5a1aa ON volkszaehler.data USING btree (channel_id) >  data      | idx_16391_primary              | CREATE UNIQUE INDEX > idx_16391_primary ON volkszaehler.data USING btree (channel_id, "timestamp") >  data      | idx_data_timestamp             | CREATE INDEX > idx_data_timestamp ON volkszaehler.data USING btree ("timestamp") > (3 rows) > > > The Query: > > volkszaehler=# explain analyze SELECT COUNT(DISTINCT DATE_TRUNC('day', > TIMESTAMP 'epoch' + timestamp * INTERVAL '1 millisecond')) FROM data WHERE > channel_id = 5 AND timestamp >= 0; > >                                                          QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------- > >  Aggregate  (cost=590793.68..590793.69 rows=1 width=8) (actual > time=15449.536..15449.539 rows=1 loops=1) >    ->  Seq Scan on data  (cost=0.00..382037.82 rows=16700469 width=8) > (actual time=247.092..3833.495 rows=16669429 loops=1) >          Filter: (("timestamp" >= 0) AND (channel_id = 5)) >          Rows Removed by Filter: 1215163 >  Planning Time: 0.374 ms >  JIT: >    Functions: 5 >    Options: Inlining true, Optimization true, Expressions true, Deforming > true >    Timing: Generation 1.201 ms, Inlining 158.833 ms, Optimization 59.816 > ms, Emission 28.472 ms, Total 248.322 ms >  Execution Time: 15451.093 ms > (10 rows) > > Round about 16 sec is too long, the frontend run in timeout or other erros. What fraction of the rows in the table meet the "WHERE channel_id = 5 AND timestamp >= 0" qualification? If it's high (and "high" can be a seemingly low value), then Postgresql will decide it's cheaper to sequentially scan the table. > > A simple count look like > > volkszaehler=# explain analyze SELECT count (channel_id) FROM data; > QUERY PLAN > --------------------------------------------------------------------------------------------------------------------------------------------- > >  Finalize Aggregate  (cost=208089.76..208089.77 rows=1 width=8) (actual > time=3514.293..3523.842 rows=1 loops=1) >    ->  Gather  (cost=208089.55..208089.76 rows=2 width=8) (actual > time=3514.247..3523.800 rows=3 loops=1) >          Workers Planned: 2 >          Workers Launched: 2 >          ->  Partial Aggregate  (cost=207089.55..207089.56 rows=1 width=8) > (actual time=3427.139..3427.141 rows=1 loops=3) >                ->  Parallel Seq Scan on data (cost=0.00..188457.44 > rows=7452844 width=8) (actual time=0.137..2121.695 rows=5962263 loops=3) >  Planning Time: 0.247 ms >  JIT: >    Functions: 11 >    Options: Inlining false, Optimization false, Expressions true, > Deforming true >    Timing: Generation 2.665 ms, Inlining 0.000 ms, Optimization 1.612 ms, > Emission 48.580 ms, Total 52.857 ms >  Execution Time: 3525.656 ms > (12 rows) > > Why are the indexes not used ? > Is it a Problem of the DATE_TRUNC ? > How can I optimize? > > Best regards > > > > > > > -- Born in Arizona, moved to Babylonia.