From: Shaun Thomas <sthomas@optionshouse.com>
To: Vincenzo Melandri <vmelandri@imolinfo.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: Seq scan on 10million record table.. why?
Date: Tue, 30 Oct 2012 08:53:00 -0500
Message-ID: <508FDBBC.2010807@optionshouse.com> (raw)
In-Reply-To: <CAHSd9GfstpNZHaW+o34S2joPUL03QYoH8s_dMrhMm-f0B2REVw@mail.gmail.com>
References: <CAHSd9GfstpNZHaW+o34S2joPUL03QYoH8s_dMrhMm-f0B2REVw@mail.gmail.com>
On 10/30/2012 07:15 AM, Vincenzo Melandri wrote:
> Merge Join (cost=2604203.98..2774528.51 rows=129904 width=20)
> Merge Cond: (((( big_table.key1)::numeric) =
> data_sequences_table.key1) AND ((( big_table.key2)::numeric) =
> data_sequences_table.key2))
> -> Sort (cost=2602495.47..2635975.81 rows=13392135 width=20)
> Sort Key: ((big_table.key1)::numeric), ((big_table.key2)::numeric)
> -> Seq Scan on big_table (cost=0.00..467919.35 rows=13392135 width=20)
> -> Sort (cost=1708.51..1709.48 rows=388 width=32)
> Sort Key: data_sequences_table.key1, data_sequences_table.key2
> -> Seq Scan on data_sequences_table (cost=0.00..1691.83
> rows=388 width=32)
> Filter: (import_id = 1351592072::numeric)
As always, we need to see an EXPLAIN ANALYZE, not just an EXPLAIN. We
also need to know the version of PostgreSQL and your server settings.
Please refer to this:
http://wiki.postgresql.org/wiki/Slow_Query_Questions
I see a lot of NUMERIC conversions in there, which suggests you're using
NUMERIC for your keys. That's not really recommended practice, but also
suggests the possibility that all your types are not the same. So it
would be very helpful to also see the actual CREATE TABLE, and CREATE
INDEX statements for those tables.
We can't help you with this limited information. Sorry.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
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: sthomas@optionshouse.com, vmelandri@imolinfo.it
Subject: Re: Seq scan on 10million record table.. why?
In-Reply-To: <508FDBBC.2010807@optionshouse.com>
* 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