pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSeq Scan vs Index on Identical Tables in Two Different Databases
5+ messages / 4 participants
[nested] [flat]
* Seq Scan vs Index on Identical Tables in Two Different Databases
@ 2013-07-17 19:50 Ellen Rothman <erothman@datalinedata.com>
2013-07-17 20:11 ` Re: Seq Scan vs Index on Identical Tables in Two Different Databases bricklen <bricklen@gmail.com>
2013-07-17 21:14 ` Re: Seq Scan vs Index on Identical Tables in Two Different Databases David Johnston <polobo@yahoo.com>
2013-07-17 21:40 ` Re: Seq Scan vs Index on Identical Tables in Two Different Databases David Kerr <dmk@mr-paradox.net>
0 siblings, 3 replies; 5+ messages in thread
From: Ellen Rothman @ 2013-07-17 19:50 UTC (permalink / raw)
To: pgsql-performance
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)"
Thanks,
Ellen
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Seq Scan vs Index on Identical Tables in Two Different Databases
2013-07-17 19:50 Seq Scan vs Index on Identical Tables in Two Different Databases Ellen Rothman <erothman@datalinedata.com>
@ 2013-07-17 20:11 ` bricklen <bricklen@gmail.com>
2013-07-17 21:54 ` Re: Seq Scan vs Index on Identical Tables in Two Different Databases Ellen Rothman <erothman@datalinedata.com>
2 siblings, 1 reply; 5+ messages in thread
From: bricklen @ 2013-07-17 20:11 UTC (permalink / raw)
To: Ellen Rothman <erothman@datalinedata.com>; +Cc: pgsql-performance
On Wed, Jul 17, 2013 at 12:50 PM, Ellen Rothman
<erothman@datalinedata.com>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?
>
Did you run "ANALYZE your-table-name" before trying the sequential scan
query?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Seq Scan vs Index on Identical Tables in Two Different Databases
2013-07-17 19:50 Seq Scan vs Index on Identical Tables in Two Different Databases Ellen Rothman <erothman@datalinedata.com>
2013-07-17 20:11 ` Re: Seq Scan vs Index on Identical Tables in Two Different Databases bricklen <bricklen@gmail.com>
@ 2013-07-17 21:54 ` Ellen Rothman <erothman@datalinedata.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ellen Rothman @ 2013-07-17 21:54 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-performance
I guess not. I usually vacuum with the analyze option box checked; I must have missed that this cycle.
It looks much better now.
Thanks!
From: bricklen [mailto:bricklen@gmail.com]
Sent: Wednesday, July 17, 2013 4:12 PM
To: Ellen Rothman
Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>
Subject: Re: [PERFORM] Seq Scan vs Index on Identical Tables in Two Different Databases
On Wed, Jul 17, 2013 at 12:50 PM, Ellen Rothman <erothman@datalinedata.com<mailto:erothman@datalinedata.com>> 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?
Did you run "ANALYZE your-table-name" before trying the sequential scan query?
________________________________
No virus found in this message.
Checked by AVG - www.avg.com<http://www.avg.com;
Version: 2013.0.3349 / Virus Database: 3204/6483 - Release Date: 07/11/13
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Seq Scan vs Index on Identical Tables in Two Different Databases
2013-07-17 19:50 Seq Scan vs Index on Identical Tables in Two Different Databases Ellen Rothman <erothman@datalinedata.com>
@ 2013-07-17 21:14 ` David Johnston <polobo@yahoo.com>
2 siblings, 0 replies; 5+ messages in thread
From: David Johnston @ 2013-07-17 21:14 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Seq Scan vs Index on Identical Tables in Two Different Databases
2013-07-17 19:50 Seq Scan vs Index on Identical Tables in Two Different Databases Ellen Rothman <erothman@datalinedata.com>
@ 2013-07-17 21:40 ` David Kerr <dmk@mr-paradox.net>
2 siblings, 0 replies; 5+ messages in thread
From: David Kerr @ 2013-07-17 21:40 UTC (permalink / raw)
To: Ellen Rothman <erothman@datalinedata.com>; +Cc: 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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-07-17 21:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-17 19:50 Seq Scan vs Index on Identical Tables in Two Different Databases Ellen Rothman <erothman@datalinedata.com>
2013-07-17 20:11 ` bricklen <bricklen@gmail.com>
2013-07-17 21:54 ` Ellen Rothman <erothman@datalinedata.com>
2013-07-17 21:14 ` David Johnston <polobo@yahoo.com>
2013-07-17 21:40 ` David Kerr <dmk@mr-paradox.net>
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