PostgreSQL 9.1.6 on linux
-------- Original Message --------
Subject: Re: Very slow inner join query Unacceptable latency.
From: Jaime Casanova <jaime@xxxxxxxxxxxxxxx>
Date: Tue, May 21, 2013 2:59 pm
To: Freddie Burgess <fburgess@xxxxxxxxxxxxxxx>
Cc: psql performance list <pgsql-performance@xxxxxxxxxxxxxx>, Postgres
General <pgsql-general@xxxxxxxxxxxxxx>
On Tue, May 21, 2013 at 4:53 PM, <fburgess@xxxxxxxxxxxxxxx> wrote:
> The SARS_ACTS table currently has 37,115,515 rows
>
> we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree
> (sars_run_id)
> we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIMARY
> KEY (id )
>
> serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join
> SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1_.ALGORITHM='SMAT';
can you please show us an EXPLAIN ANALYZE of this query (not only
EXPLAIN). please paste it in a file and attach it so it doesn't get
reformatted by the mail client.
what version of postgres is this?
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157
The SARS_ACTS table currently has 37,115,515 rows
I re-added the index on algorithm: idx_sars_acts_run_algorithm ON SARS_ACTS_RUN (algorithm)
serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1_.ALGORITHM='SMAT';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3983424.05..3983424.06 rows=1 width=0) (actual time=1358298.003..1358298.004 rows=1 loops=1)
-> Hash Join (cost=44.93..3983415.81 rows=3297 width=0) (actual time=2593.768..1358041.205 rows 1481710 loops=1)
Hash Cond: (this_.SARS_RUN_ID=tr1_.ID)
-> Seq Scan om sars_acts this_ (cost=0.00..3844241.84 rows=37092284 width=8) (actual time=0.026..1348954.763 rows=37461345 loops=1)
-> Hash (cost=37.68..37.68 rows=580 width=8) (actual time=435.655..435.655 rows=441 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 18kB
-> Index Scan using idx_mars_track_run_algorithm on SARS_ACTS_RUN tr1_ (cost=0.00..37.68 rows=580 width=8) (actual time=10.580..435.273 rows 441 loops=1)
Index Cond: ((algorithm)::text = 'SMAT'::text)
Total runtime: 1358298.664 ms <- 22.6383 minutes
(9 rows)
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
