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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
