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 1qLKIX-002qT9-7c for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 09:13:41 +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 1qLKIV-007GNV-PL for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 09:13:39 +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 1qLKIV-007GNN-E9 for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 09:13:39 +0000 Received: from ikarus.unix-solution.de ([2a00:17d8:100:2::53f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLKIP-000sF6-MF for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 09:13:38 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=unix-solution.de; s=mx; h=Content-Transfer-Encoding:Content-Type:Subject: From:To:MIME-Version:Date:Message-ID:Cc:Content-Description:Content-ID: In-Reply-To:References:Resent-Cc:Resent-To; bh=tOKEIM7q3bDwuOpU32ANTB4RhwepExI3ekxmZ1OJWqY=; b=W9hqWCvIUZc2nKyj1GDIn49/0Q QWAdd5+oXrpsmX4fNfoFLtwROkKTjfD1a56VMUCuzBLihob35oPKK59sfLN0ZJkTfKzedpLt9eYeq zIp3VYTt6W5tOquZ3YohYoXRtQjYv3rzox73lVkfxAyeCmSfeXh3J9rEqHIINwK63hYk=; Received: from [2003:a:a27:b8fc:943d:daff:fe2f:dfd] by ikarus.unix-solution.de with esmtpsa (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1qLKIL-00C5qb-1f for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 11:13:29 +0200 Message-ID: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de> Date: Mon, 17 Jul 2023 11:13:23 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.13.0 To: pgsql-general@lists.postgresql.org Content-Language: de-DE, en-US From: basti Subject: Query take a long time and use no index Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-ACL-Warn: Max 0 failed recipients allowed List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hello, I use volkszaehler.org (a DIY Smartmeter) on postgres. The hardware is a Dual-core 2,4 GHz amd64-CPU and 6 GB RAM. The databse is approx. 1.5 GB on SSD my local config looks like: root@vz:~# cat /etc/postgresql/15/main/conf.d/local.conf # DB Version: 15 # OS Type: linux # Total Memory (RAM): 6 GB # CPUs num: 2 # Connections num: 20 # Data Storage: sdd max_connections = 20 shared_buffers = 2GB effective_cache_size = 4608MB maintenance_work_mem = 384MB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 0.9 effective_io_concurrency = 200 min_wal_size = 1GB max_wal_size = 4GB work_mem = 512MB temp_buffers = 512MB # log log_directory = '/var/log/postgresql/' # directory where log files are written, # can be absolute or relative to PGDATA log_filename = 'postgresql-15-main.log' #log_statement = 'all' #logging_collector = on log_temp_files = 0 session_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '3s' The Values: volkszaehler=# SELECT * FROM data limit 10; timestamp | channel_id | value ---------------+------------+------- 1666008690865 | 5 | 232 1666008691867 | 5 | 236 1666008692865 | 5 | 237 1666008693867 | 5 | 232 1666008694865 | 5 | 227 1666008695867 | 5 | 229 1666008698866 | 5 | 228 1666008699868 | 5 | 229 1666008700864 | 5 | 228 1666008701868 | 5 | 230 volkszaehler=# SELECT COUNT(value) FROM data; count ---------- 17884247 (1 row) 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. 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