Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qC3I6-0005WL-GY for pgsql-admin@arkaria.postgresql.org; Wed, 21 Jun 2023 19:14:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qC3I4-0005L7-LK for pgsql-admin@arkaria.postgresql.org; Wed, 21 Jun 2023 19:14:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qC3I4-0005Kx-8r for pgsql-admin@lists.postgresql.org; Wed, 21 Jun 2023 19:14:52 +0000 Received: from mail-yb1-xb34.google.com ([2607:f8b0:4864:20::b34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qC3I1-003sGt-FJ for pgsql-admin@lists.postgresql.org; Wed, 21 Jun 2023 19:14:51 +0000 Received: by mail-yb1-xb34.google.com with SMTP id 3f1490d57ef6-bfe97b3752bso1160104276.1 for ; Wed, 21 Jun 2023 12:14:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1687374887; x=1689966887; h=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=jep1bvDxn+J+89+kPRPRSWxGbsRDhXJAeZ49CiuuXJ0=; b=DKEWFqi0zFAhLNrb8L88Le50NN629cYmU3YsEMlhTY1fTfedCt2KjxLG7oDW6/RMdi 2wWw7FAKcDaVMnyoiEhqmRXYtQIHup6wp3NMQT3R+43qPC5vZPQ2i4h7ZfTHYN8W9b9g /0kCxFLXRujqELV6Z6bcCeCrVo4nxqpChyias7NmzX3kZiLfD7It0yohasrlKb0Rqt9u 98DTQrPleQzIfx4udWsV7jdhMlVt9bEYbKRcu94rrl27T7c8g2ZZXEspm6pmL2c8Mqn1 uupDudmq5eaX9PveuDbHGl2JLtKF1EpFMC5feGXiqdndPLmIzi5BRK01oyWJ2Oy67DS5 G6oQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687374887; x=1689966887; h=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=jep1bvDxn+J+89+kPRPRSWxGbsRDhXJAeZ49CiuuXJ0=; b=S8PA0mev1h1e8yxHuE/kX00QtFYQB1QulUpaocigckwsK/TRGEsr3pZ0V9k/cuY3UB g9/M54msEBs+wmnS0x1XvMRfFO4ovYycoP3+UIZYF2OjQXIknxZdi6opB/DZULFNsQ5j Qk4SuGMj/R+2wO6j82zEAdVYXEC2dqBmuEKXmZKyNYxeLe2dH8mEpe3TOTVAFmHvb1Tm 0i3SJtcku+0eYUQwZ0gP1qi5/Vv27b/nh6sF1ch6kxlMNILTG9Mnm6hoPrZ2fxzNGqKO IqVto0MJtes8Ezpl6gF6HIVEie9yFsMUXrkzV+8gRjnfu7ny0tivs4ijeWs6TyRY584a wdkQ== X-Gm-Message-State: AC+VfDz8wt8xhMRCE5AcpvhEnWF6TqTmOnhRXbyIMqy/fnAzy9Obl2bl yv2BEKnDoaa5T3toQ/YX8G+jOrmtXI8= X-Google-Smtp-Source: ACHHUZ7JsjHP+lgx8X7t7YWN6dWl7FuMLT+0IAVHTGhL8Dqbakjie0z7Kx/oSMHuebUWyxM7gQetiA== X-Received: by 2002:a25:cbd6:0:b0:bc8:ca4:15de with SMTP id b205-20020a25cbd6000000b00bc80ca415demr12272101ybg.3.1687374887040; Wed, 21 Jun 2023 12:14:47 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:909b:2990:23d5:71b6? ([2600:1700:bd2c:b810:909b:2990:23d5:71b6]) by smtp.googlemail.com with ESMTPSA id o16-20020a25d710000000b00be45a29d440sm1100106ybg.12.2023.06.21.12.14.46 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 21 Jun 2023 12:14:46 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------CvKrI230xT8yLQyQskQ4YxG0" Message-ID: <208a4de5-44a3-2ea6-5472-02887ec9a118@gmail.com> Date: Wed, 21 Jun 2023 14:14:46 -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: Poor performance due to parallel seq scan on indexed date field Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: <85548e4d-b7ce-e061-e43e-8a8e332dc4e5@jakobs.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------CvKrI230xT8yLQyQskQ4YxG0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Is there an index on synergy.pitches.game_date? It's no guarantee* that will speed up the query, but "index on WHERE clause fields" is the first thing to look for. * The problem with "over wide date ranges" is that the query optimizer might decide that the date range is so wide that it's just as cheap -- or cheaper -- to scan the whole table. On 6/21/23 13:39, Wells Oliver wrote: > It's just this. > > CREATE OR REPLACE VIEW vw_pitches AS > SELECT >     p.year, >     p.game_id, >     p.game_date, >     p.game_level, >     ... > from synergy.pitches as p > join alias.identity as idpitcher >     on p.pitcher_identity_id = idpitcher.identity_id > left join alias.identity as idcatcher >     on p.catcher_identity_id = idcatcher.identity_id > left join alias.identity as idbatter >     on p.batter_identity_id = idbatter.identity_id; > > The alias.identity.identity_id column is indexed. > > The main issue is SELECT COUNT(*) over wide date ranges, which is > something we'd like to do frequently. > > > On Wed, Jun 21, 2023 at 11:37 AM Holger Jakobs wrote: > > > Am 21.06.23 um 20:31 schrieb Wells Oliver: > > Dead simple date scan across a big-ish table (est. 23,153,666 rows) > > > > explain analyze select count(*) from vw_pitches where game_date >= > > '2022-06-21' and game_date <= '2023-06-21'; > > > > The view does do some joins but those don't seem to be the issue to me. > > > > Planner does: > > > > Finalize Aggregate  (cost=3596993.88..3596993.89 rows=1 width=8) > > (actual time=69980.491..69982.076 rows=1 loops=1) > >   ->  Gather  (cost=3596993.46..3596993.87 rows=4 width=8) (actual > > time=69979.137..69982.071 rows=5 loops=1) > >         Workers Planned: 4 > >         Workers Launched: 4 > >         ->  Partial Aggregate  (cost=3595993.46..3595993.47 rows=1 > > width=8) (actual time=69975.136..69975.137 rows=1 loops=5) > >               ->  Nested Loop  (cost=0.44..3591408.37 rows=1834034 > > width=0) (actual time=0.882..69875.934 rows=1458419 loops=5) > >                     ->  Parallel Seq Scan on pitches p > >  (cost=0.00..3537431.89 rows=1834217 width=12) (actual > > time=0.852..68914.256 rows=1458419 loops=5) > >                           Filter: ((game_date >= '2022-06-21'::date) > > AND (game_date <= '2023-06-21'::date)) > >                           Rows Removed by Filter: 3212310 > >                     ->  Memoize  (cost=0.44..0.47 rows=1 width=4) > > (actual time=0.000..0.000 rows=1 loops=7292095) > >                           Cache Key: p.pitcher_identity_id > >                           Cache Mode: logical > >                           Hits: 1438004  Misses: 21042  Evictions: 0 > >  Overflows: 0  Memory Usage: 2138kB > >                           Worker 0:  Hits: 1429638  Misses: 21010 > >  Evictions: 0  Overflows: 0  Memory Usage: 2134kB > >                           Worker 1:  Hits: 1456755  Misses: 21435 > >  Evictions: 0  Overflows: 0  Memory Usage: 2177kB > >                           Worker 2:  Hits: 1433557  Misses: 21201 > >  Evictions: 0  Overflows: 0  Memory Usage: 2154kB > >                           Worker 3:  Hits: 1428727  Misses: 20726 > >  Evictions: 0  Overflows: 0  Memory Usage: 2105kB > >                           ->  Index Only Scan using identity_pkey on > > identity idpitcher  (cost=0.43..0.46 rows=1 width=4) (actual > > time=0.007..0.007 rows=1 loops=105414) > >                                 Index Cond: (identity_id = > > p.pitcher_identity_id) > >                                 Heap Fetches: 83 > > Planning Time: 1.407 ms > > Execution Time: 69982.927 ms > > > > Is there something to be done here? Kind of a frequent style of query > > and quite slow. > > > Could you provide the definition of the view(s) down to the base tables? > > -- > Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 > > > > -- > Wells Oliver > wells.oliver@gmail.com -- Born in Arizona, moved to Babylonia. --------------CvKrI230xT8yLQyQskQ4YxG0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Is there an index on synergy.pitches.game_date?

It's no guarantee* that will speed up the query, but "index on WHERE clause fields" is the first thing to look for.

* The problem with "over wide date ranges" is that the query optimizer might decide that the date range is so wide that it's just as cheap -- or cheaper -- to scan the whole table.

On 6/21/23 13:39, Wells Oliver wrote:
It's just this.

CREATE OR REPLACE VIEW vw_pitches AS
SELECT
    p.year,
    p.game_id,
    p.game_date,
    p.game_level,
    ...
from synergy.pitches as p
join alias.identity as idpitcher
    on p.pitcher_identity_id = idpitcher.identity_id
left join alias.identity as idcatcher
    on p.catcher_identity_id = idcatcher.identity_id
left join alias.identity as idbatter
    on p.batter_identity_id = idbatter.identity_id;

The alias.identity.identity_id column is indexed.

The main issue is SELECT COUNT(*) over wide date ranges, which is something we'd like to do frequently.


On Wed, Jun 21, 2023 at 11:37 AM Holger Jakobs <holger@jakobs.com> wrote:

Am 21.06.23 um 20:31 schrieb Wells Oliver:
> Dead simple date scan across a big-ish table (est. 23,153,666 rows)
>
> explain analyze select count(*) from vw_pitches where game_date >=
> '2022-06-21' and game_date <= '2023-06-21';
>
> The view does do some joins but those don't seem to be the issue to me.
>
> Planner does:
>
> Finalize Aggregate  (cost=3596993.88..3596993.89 rows=1 width=8)
> (actual time=69980.491..69982.076 rows=1 loops=1)
>   ->  Gather  (cost=3596993.46..3596993.87 rows=4 width=8) (actual
> time=69979.137..69982.071 rows=5 loops=1)
>         Workers Planned: 4
>         Workers Launched: 4
>         ->  Partial Aggregate  (cost=3595993.46..3595993.47 rows=1
> width=8) (actual time=69975.136..69975.137 rows=1 loops=5)
>               ->  Nested Loop  (cost=0.44..3591408.37 rows=1834034
> width=0) (actual time=0.882..69875.934 rows=1458419 loops=5)
>                     ->  Parallel Seq Scan on pitches p
>  (cost=0.00..3537431.89 rows=1834217 width=12) (actual
> time=0.852..68914.256 rows=1458419 loops=5)
>                           Filter: ((game_date >= '2022-06-21'::date)
> AND (game_date <= '2023-06-21'::date))
>                           Rows Removed by Filter: 3212310
>                     ->  Memoize  (cost=0.44..0.47 rows=1 width=4)
> (actual time=0.000..0.000 rows=1 loops=7292095)
>                           Cache Key: p.pitcher_identity_id
>                           Cache Mode: logical
>                           Hits: 1438004  Misses: 21042  Evictions: 0
>  Overflows: 0  Memory Usage: 2138kB
>                           Worker 0:  Hits: 1429638  Misses: 21010
>  Evictions: 0  Overflows: 0  Memory Usage: 2134kB
>                           Worker 1:  Hits: 1456755  Misses: 21435
>  Evictions: 0  Overflows: 0  Memory Usage: 2177kB
>                           Worker 2:  Hits: 1433557  Misses: 21201
>  Evictions: 0  Overflows: 0  Memory Usage: 2154kB
>                           Worker 3:  Hits: 1428727  Misses: 20726
>  Evictions: 0  Overflows: 0  Memory Usage: 2105kB
>                           ->  Index Only Scan using identity_pkey on
> identity idpitcher  (cost=0.43..0.46 rows=1 width=4) (actual
> time=0.007..0.007 rows=1 loops=105414)
>                                 Index Cond: (identity_id =
> p.pitcher_identity_id)
>                                 Heap Fetches: 83
> Planning Time: 1.407 ms
> Execution Time: 69982.927 ms
>
> Is there something to be done here? Kind of a frequent style of query
> and quite slow.
>
Could you provide the definition of the view(s) down to the base tables?

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012



--

--
Born in Arizona, moved to Babylonia.
--------------CvKrI230xT8yLQyQskQ4YxG0--