From: | David Kerr <dmk(at)mr-paradox(dot)net> |
---|---|
To: | Ellen Rothman <erothman(at)datalinedata(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Seq Scan vs Index on Identical Tables in Two Different Databases |
Date: | 2013-07-17 21:40:00 |
Message-ID: | 20130717214000.GA84074@mr-paradox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Jul 17, 2013 at 07:50:06PM +0000, Ellen Rothman wrote:
- I have the same table definition in two different databases on the same computer. When I explain a simple query in both of them, one database uses a sequence scan and the other uses an index scan. If I try to run the Seq Scan version without the where clause restricting the value of uniqueid, it uses all of the memory on my computer and never completes.
-
- How can I get the Seq Scan version to use an index scan?
-
- Explain results - good version:
- "GroupAggregate (cost=0.00..173.78 rows=1 width=15)"
- " -> Index Scan using pubcoop_ext_idx1 on pubcoop_ext (cost=0.00..173.77 rows=1 width=15)"
- " Index Cond: (uniqueid < '000000009'::bpchar)"
-
- Explain results - problem version:
- "HashAggregate (cost=13540397.84..13540398.51 rows=67 width=18)"
- " -> Seq Scan on pubcoop_ext (cost=0.00..13360259.50 rows=36027667 width=18)"
- " Filter: (uniqueid < '000000009'::bpchar)"
(Assuming that your postgresql.conf is the same across both systems and that
you've run vanilla analyze against each table... )
I ran into a similar problem before and it revolved around the somewhat random nature of
a vaccum analyze. To solve the problem i increased the statistics_target for the table
on the box that was performing poorly and ran analyze.
I believe that worked because basically the default_statistics_taget of 100 wasn't
catching enough info about that record range to make an index appealing to the optimizer
on the new box where the old box it was.
From | Date | Subject | |
---|---|---|---|
Next Message | Ellen Rothman | 2013-07-17 21:54:03 | Re: Seq Scan vs Index on Identical Tables in Two Different Databases |
Previous Message | David Johnston | 2013-07-17 21:14:45 | Re: Seq Scan vs Index on Identical Tables in Two Different Databases |