From: David Johnston <polobo@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Seq Scan vs Index on Identical Tables in Two Different Databases
Date: Wed, 17 Jul 2013 14:14:45 -0700 (PDT)
Message-ID: <1374095685220-5764143.post@n5.nabble.com> (raw)
In-Reply-To: <d2c495ac125a4e94acd463a6981136f2@BLUPR08MB166.namprd08.prod.outlook.com>
References: <d2c495ac125a4e94acd463a6981136f2@BLUPR08MB166.namprd08.prod.outlook.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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-Datab...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: polobo@yahoo.com
Subject: Re: Seq Scan vs Index on Identical Tables in Two Different Databases
In-Reply-To: <1374095685220-5764143.post@n5.nabble.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