From: Laurenz Albe <laurenz.albe@cybertec.at>
To: support@mekong.be <support@mekong.be>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query slow for new participants
Date: Wed, 27 Feb 2019 08:58:17 +0100
Message-ID: <38d1ac81dfb700d154290aa35f513320d9fd093e.camel@cybertec.at> (raw)
In-Reply-To: <CALJ2KGUnuO6HEWM9pwpXbF4NF-jLHmZSub5YsYD_A=GfHMU6Eg@mail.gmail.com>
References: <1551091278468-0.post@n3.nabble.com>
<20190225161607.GQ28750@telsasoft.com>
<CALJ2KGUnuO6HEWM9pwpXbF4NF-jLHmZSub5YsYD_A=GfHMU6Eg@mail.gmail.com>
support@mekong.be wrote:
> EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN
> "Index Scan using ix_companyarticledb_company on companyarticledb (cost=0.57..2.80 rows=1 width=193) (actual time=1011.335..1011.454 rows=1 loops=1)"
> " Index Cond: (companyid = 77)"
> " Filter: (articleid = 7869071)"
> " Rows Removed by Filter: 2674361"
> " Buffers: shared hit=30287"
> "Planning time: 0.220 ms"
> "Execution time: 1011.502 ms"
Your problem are the "Rows Removed by Filter: 2674361".
The first thing I would try is:
ALTER TABLE public.companyarticledb
ALTER companyid SET STATISTICS 1000;
ALTER TABLE public.companyarticledb
ALTER articleid SET STATISTICS 1000;
ANALYZE public.companyarticledb;
Then PostgreSQL has a better idea which condition is selective.
You can set STATISTICS up to 10000, but don't forget that high values
make ANALYZE and planning slower.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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: laurenz.albe@cybertec.at, support@mekong.be, pryzby@telsasoft.com
Subject: Re: Query slow for new participants
In-Reply-To: <38d1ac81dfb700d154290aa35f513320d9fd093e.camel@cybertec.at>
* 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