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.