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 1qLMH9-002vHs-BB for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 11:20:23 +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 1qLMH7-008OJ2-C9 for pgsql-general@arkaria.postgresql.org; Mon, 17 Jul 2023 11:20:21 +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 1qLMH6-008OIu-TD for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 11:20:21 +0000 Received: from mail-wm1-x32f.google.com ([2a00:1450:4864:20::32f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qLMH2-000tI7-Un for pgsql-general@lists.postgresql.org; Mon, 17 Jul 2023 11:20:18 +0000 Received: by mail-wm1-x32f.google.com with SMTP id 5b1f17b1804b1-3fbf1b82d9cso39132425e9.2 for ; Mon, 17 Jul 2023 04:20:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1689592815; x=1692184815; 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=X4sR8wSgWm7mw8tJdwpSFK5cXEqquffeQ3/S0w7twDk=; b=Wl4RivFqG1llCoBQY/kZzgLZx/g5DJKWifUzbodaIogbhBWo1g8aDJxEN2TZUhIF/a UXGnoNLoRmmo/fLNToOAfFmhoclQXCKLCVNjI9tRST/VOba4SraPv6EPJNKKa5a466js zLV3derPa9lan0oYIpIUP7+U6Egogh9RDdB1UKUQ05LlNaiXdi2wNZlOFpHkR7/nCOW5 cUoNh3YCktKO5eX5zRarDndrRk3R+ym1DcUFvZsZrK4YEiTspNoJQP9dJvrrc7CYXYiL vC54j3UZM2x0m77/dF50fO6o+QpHEcCdzwcQUcQiqZeZT7FSFSuEL+4EjWL4zIZAfYwG DpaA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689592815; x=1692184815; 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=X4sR8wSgWm7mw8tJdwpSFK5cXEqquffeQ3/S0w7twDk=; b=ShOzNxqZOkc/XjGRJJNqYsqCr8T7jjAzVHKr2eNhI1cJlCbpUsT/Gf658sfxTpy4Zf HpQiUJbcZ1Jc02lUYlPr6zJMcdziBo7sED5TIg5KC/hbFRToo56XAnlJqgx74bn2/YDR F7XM32TrUZ5PN+NbcsjlIRMWw08XfhC4dFACsS+nxhP+kG+Tyxvo9ra5+qkwQPVt40ZK KJAwjiurnsej5liiq8AJE/8hRuXfhX5IQvfbOYzQ67zrlVDccPyz5Eeo6Xd924uKO+RJ gG3MMDuIlWhpS14XI5NFNlx37Gsuo84j59YSFNo+AaegPxoXOR9C1FXfSwZcLvPaZaIg uFJQ== X-Gm-Message-State: ABy/qLZz265cCDn1hJyNpTuGysA6cpAaXhqIuHw0uqEkMNxtcl/jIxBo y2EQ4HClXdCWtkgGTUO7oGSC4Q== X-Google-Smtp-Source: APBJJlGziNZ+95mSU358QA7Njb09m2Ss4hqcV7xrRiLMtSChbhFXaBvr53h2qXL6+MJCmmvSy48usg== X-Received: by 2002:a7b:cd95:0:b0:3f7:3699:c294 with SMTP id y21-20020a7bcd95000000b003f73699c294mr9363974wmj.29.1689592815179; Mon, 17 Jul 2023 04:20:15 -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 f2-20020a7bc8c2000000b003fbaade072dsm7691859wml.23.2023.07.17.04.20.14 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 17 Jul 2023 04:20:14 -0700 (PDT) Message-ID: Date: Mon, 17 Jul 2023 13:20:13 +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 To: basti , pgsql-general@lists.postgresql.org References: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de> From: Tomas Vondra In-Reply-To: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 7/17/23 11:13, basti wrote: > 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 ? It's a common misconception that indexes necessarily improve performance and that slow queries are slow because of missing indexes. In practice indexes are most useful for selective queries, i.e. queries that scan only a fraction of the table - say, a couple percent. But this query scans ~17M rows from the table, and my guess is that's a significant part of the table. In which case the index is useless. Also, notice the seqscan took ~4s. Even if that drops to ~1s with an index, the overall query will still take 12s. > Is it a Problem of the DATE_TRUNC ? My guess is it's more about the DISTINCT, which forces a sort before the aggregate function. Which also means the query can't be parallel. You can try simplifying the query step by step - remove the DISTINCT first, then the DATE_TRUNC. > How can I optimize? > 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. Another option is to precalculate stuff, and do queries on that. That is, you're not going to add data with old timestamps, so you can pre-aggregate that. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company