pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Query take a long time and use no index
Date: Mon, 17 Jul 2023 08:03:36 -0500
Message-ID: <4f0ba00d-5450-a638-f181-cd906682c7fa@gmail.com> (raw)
In-Reply-To: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de>
References: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de>

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.





view thread (7+ messages)  latest in thread

Message-ID: <4f0ba00d-5450-a638-f181-cd906682c7fa@gmail.com>
Permalink:  ../4f0ba00d-5450-a638-f181-cd906682c7fa@gmail.com/
Also on:    postgresql.org/message-id/4f0ba00d-5450-a638-f181-cd906682c7fa@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Query take a long time and use no index
  In-Reply-To: <4f0ba00d-5450-a638-f181-cd906682c7fa@gmail.com>
  References: <5f826baf-e345-48a8-5672-db07add3e6b2@unix-solution.de> <4f0ba00d-5450-a638-f181-cd906682c7fa@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox