Message-ID: <38d1ac81dfb700d154290aa35f513320d9fd093e.camel@cybertec.at>
Subject: Re: Query slow for new participants
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "support@mekong.be" <support@mekong.be>, Justin Pryzby
	 <pryzby@telsasoft.com>
Cc: pgsql-performance@postgresql.org
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>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
MIME-Version: 1.0
Date: Wed, 27 Feb 2019 08:58:17 +0100
User-Agent: Evolution 3.30.5 (3.30.5-1.fc29) 
Content-Transfer-Encoding: 7bit
Precedence: bulk

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