From: vinny <vinny@xs4all.nl>
To: Marco Renzi <renzi.mrc@gmail.com>
Cc: Phillip Couto <phillip@couto.in>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-performance-owner@postgresql.org
Subject: Re: Query with no result set, really really slow adding ORBDER BY / LIMIT clause
Date: Thu, 20 Apr 2017 13:54:47 +0200
Message-ID: <1ea5beeda23ccbb4f204c949d4806dcd@xs4all.nl> (raw)
In-Reply-To: <CAHmtSMbYBrZThdbynZaUPw+towXMEeXYbOQdpXEHjEgM0GHonw@mail.gmail.com>
References: <CAHmtSMa_UnK_8q5-Hc2BCWk9TEWXhexmcn=ZxXkrfsWuJvje6Q@mail.gmail.com>
<0100015b8afa09e3-cffb70ac-bf88-4823-ae8d-d5586fe1ed72-000000@email.amazonses.com>
<CAHmtSMbYBrZThdbynZaUPw+towXMEeXYbOQdpXEHjEgM0GHonw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 2017-04-20 13:16, Marco Renzi wrote:
> Thanks Philip, yes i tried, but that is not solving, still slow. Take
> a look at the log.
>
> --------------------------------------------------------------------------------------------------------------------------
> Limit (cost=3.46..106.87 rows=10 width=4) (actual
> time=396555.327..396555.327 rows=0 loops=1)
> -> Nested Loop (cost=3.46..214781.07 rows=20770 width=4) (actual
> time=396555.326..396555.326 rows=0 loops=1)
> Join Filter: (tipofase.id [1] = fase.tipofase)
> -> Index Scan Backward using test_prova_2 on fase
> (cost=0.43..192654.24 rows=1474700 width=8) (actual
> time=1.147..395710.190 rows=1475146 loops=1)
> -> Materialize (cost=3.03..6.34 rows=1 width=8) (actual
> time=0.000..0.000 rows=0 loops=1475146)
> -> Hash Semi Join (cost=3.03..6.33 rows=1 width=8)
> (actual time=0.081..0.081 rows=0 loops=1)
> Hash Cond: (tipofase.id [1] = tipofase_1.id [2])
> -> Seq Scan on tipofase (cost=0.00..3.02
> rows=102 width=4) (actual time=0.003..0.003 rows=1 loops=1)
> -> Hash (cost=3.02..3.02 rows=1 width=4) (actual
> time=0.064..0.064 rows=0 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 0kB
> -> Seq Scan on tipofase tipofase_1
> (cost=0.00..3.02 rows=1 width=4) (actual time=0.063..0.063 rows=0
> loops=1)
> Filter: agendafrontoffice
> Rows Removed by Filter: 102
> Planning time: 1.254 ms
> Execution time: 396555.499 ms
>
> --------------------------------------------------------------------------------------------------------------------------
>
> THE ONLY WAY TO SPEEDUP I FOUND IS THIS ONE
>
> SELECT fase.id [3]
> FROM tipofase
> JOIN fase
> ON (fase.tipofase = (SELECT tipofase.id [1] FROM tipofase
> WHERE tipofase.agendafrontoffice = true))
>
> ORDER BY fase.id [3] DESC limit 10 offset 0
>
> --------------------------------------------------------------------------------------------------------------------------
>
> Limit (cost=3.45..3.58 rows=10 width=4) (actual time=0.082..0.082
> rows=0 loops=1)
> InitPlan 1 (returns $0)
> -> Seq Scan on tipofase tipofase_1 (cost=0.00..3.02 rows=1
> width=4) (actual time=0.072..0.072 rows=0 loops=1)
> Filter: agendafrontoffice
> Rows Removed by Filter: 102
> -> Nested Loop (cost=0.43..27080.93 rows=2118540 width=4) (actual
> time=0.081..0.081 rows=0 loops=1)
> -> Index Only Scan Backward using fase_test_prova_4 on fase
> (cost=0.43..595.90 rows=20770 width=4) (actual time=0.080..0.080
> rows=0 loops=1)
> Index Cond: (tipofase = $0)
> Heap Fetches: 0
> -> Materialize (cost=0.00..3.53 rows=102 width=0) (never
> executed)
> -> Seq Scan on tipofase (cost=0.00..3.02 rows=102
> width=0) (never executed)
> Planning time: 0.471 ms
> Execution time: 0.150 ms
>
> --------------------------------------------------------------------------------------------------------------------------
>
> Anyone knows?
> I'm a bit worried about performance in my web app beacause sometimes
> filters are written dinamically at the end, and i would like to avoid
> these problems.
>
What was it that Philip suggested? I can't find his reply in the list
and you didn't quote it...
Did you try reversing the order of the tables, so join fase to tipofase,
instead of tipofase to fase.
Also, did you try a partial index on tipofase.id where
tipofase.agendafrontoffice = true?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: vinny@xs4all.nl, renzi.mrc@gmail.com, phillip@couto.in, pgsql-performance-owner@postgresql.org
Subject: Re: Query with no result set, really really slow adding ORBDER BY / LIMIT clause
In-Reply-To: <1ea5beeda23ccbb4f204c949d4806dcd@xs4all.nl>
* 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