Thread: Seq Scan vs Index on Identical Tables in Two Different Databases

Ellen Rothman wrote
> I have the same table definition in two different databases on the same
> computer.

You really should prove this to us by running schema commands on the table
and providing results.

Also, version information has not been provided and you do not state whether
the databases are the same as well as tables.  And, do those tables have
identical data or just structure?


> When I explain a simple query in both of them, one database uses a
> sequence scan and the other uses an index scan.

Corrupt index maybe?  Or back to the first point maybe there isn't one.


> 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 are you running this and how are you defining "never completes"?

Can you run this but with a limit clause so your client (and the database)
does not try to display 3 millions rows of data?


> How can I get the Seq Scan version to use an index scan?

Re-Index (or drop/create even)

Also, you should always try to provide actual queries and not just explains.
Since you are getting "Aggregate" nodes you obviously aren't running a
simple "SELECT * FROM publcoop_ext [WHERE ...]".

Ideally you can also provide a self-contained test case. though your
scenario seems simple enough that either:

1) You didn't run analyze
2) Your table and/or index is corrupt
3) You do not actually have an index on the table even though you claim they
are the same







--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Seq-Scan-vs-Index-on-Identical-Tables-in-Two-Different-Databases-tp5764125p5764143.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

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
bothof them, one database uses a sequence scan and the other uses an index scan.  If I try to run the Seq Scan version
withoutthe 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.