From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | "support(at)mekong(dot)be" <support(at)mekong(dot)be>, Justin Pryzby <pryzby(at)telsasoft(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query slow for new participants |
Date: | 2019-02-27 07:58:17 |
Message-ID: | 38d1ac81dfb700d154290aa35f513320d9fd093e.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
support(at)mekong(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Fd Habash | 2019-02-27 14:47:13 | What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum |
Previous Message | support@mekong.be | 2019-02-26 21:39:05 | Re: Query slow for new participants |