sql-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
mailto:eroth...@datalinedata.com>> wrote:
I have the same table definition in two different databases on the same
comp
On Wed, Jul 17, 2013 at 07:50:06PM +, 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
vers
On Wed, Jul 17, 2013 at 12:50 PM, 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
On 10/31/2012 05:55 AM, Vincenzo Melandri wrote:
on People:
CREATE UNIQUE INDEX people_pkey ON people USING btree (key1, key2)
CREATE INDEX people_pkey_hash_loc ON people USING hash (key1);
CREATE INDEX people_pkey_hash_id ON people USING hash (key2);
I can't say why it would ignore the first
I may (or may not) have found the solution: a reindex on the 3 tables fixed
the query plan. Now I can plan to reindex only the involved indexes at the
start of the data import procedure.
On Wed, Oct 31, 2012 at 11:55 AM, Vincenzo Melandri
wrote:
> Hi all :)
>
> I'm here again.
> This time I'll pr
> 1) Make all types the same
> 2) If you are using some narrow type for big_table (say, int2) to save
> space, you can force narrowing conversion, e.g. "b.key1=ds.key1::int2". Note
> that if ds.key1 has any values that don't fit into int2, you will have
> problems. And of course, use your type used
On 10/30/2012 07:15 AM, Vincenzo Melandri wrote:
Merge Join (cost=2604203.98..2774528.51 rows=129904 width=20)
Merge Cond: big_table.key1)::numeric) =
data_sequences_table.key1) AND ((( big_table.key2)::numeric) =
data_sequences_table.key2))
-> Sort (cost=2602495.47..2635975.81 row
Hi Vincenzo,
On Tue, 30 Oct 2012 13:15:10 +0100, Vincenzo Melandri
wrote:
I have indexes on both the key on the big table and the import_id on
the sequence table.
Forgive my quick answer, but it might be that the data you are
retrieving is scattered throughout the whole table, and the index
Gary Warner writes:
> Recently my database stopped respecting one of my indexes, which took a query
> that should run in "subsecond response time" and turning it into something
> that with small data sets runs in the 7-10 minute range and with large data
> sets runs in the 30 minute - eternity
Can you post your non-default postgresql.conf settings? (I'd hazard a
guess that you have effective_cache_size set to the default 128MB).
Best wishes
Mark
On 24/11/11 11:24, Gary Warner wrote:
Very Fast Version:
Recently my database stopped respecting one of my indexes, which took a query th
On Wed, Nov 23, 2011 at 7:24 PM, Gary Warner wrote:
> See that "Seq Scan on link_url"? We can't figure out why that is there! We
> should be scanning for a matching "urlid" and we have an index on "urlid"?
>
> When this is happening in a "two table" version of this problem, we can get
> tempor
Nassib Nassar wrote:
> In this example it looks to me like the planner is choosing a Seq
> Scan resulting in 18x running time compared to running it with
> enable_seqscan = 'off'.
I would try these settings:
random_page_cost = 2
cpu_tuple_cost = 0.02
Based on your estimated cost versus ac
On 06/22/2011 02:12 PM, Scott Marlowe wrote:
Given that many folks still run < 9.0 in production, the wiki page
should really have a version of that function for older versions,
whether it's long or not.
I updated the page already to be clear about what versions of PostgreSQL
it works on,
On 06/22/2011 01:12 PM, Scott Marlowe wrote:
Given that many folks still run< 9.0 in production, the wiki page
should really have a version of that function for older versions,
whether it's long or not.
This version does work on anything 8.3 and above. I just lamented on 9.0
because we decid
On Wed, Jun 22, 2011 at 7:12 AM, Shaun Thomas wrote:
> On 06/22/2011 04:55 AM, Marti Raudsepp wrote:
>
>> With Jim Nasby's idea to use regclass instead of relation names, the
>> function is now half its length and probably more reliable. There's no
>> need to touch pg_class directly at all.
>
> Sa
On 06/22/2011 05:55 AM, Marti Raudsepp wrote:
Now I created a wiki snippet page for this handy
feature here:
https://wiki.postgresql.org/wiki/Efficient_min/max_over_partitioned_table
I just tweaked this a bit to document the version compatibility issues
around it and make it easier to foll
On 06/22/2011 04:55 AM, Marti Raudsepp wrote:
With Jim Nasby's idea to use regclass instead of relation names, the
function is now half its length and probably more reliable. There's no
need to touch pg_class directly at all.
Sadly until we upgrade to EDB 9.0, I have to use my function. :) EDB
On Thu, Jun 16, 2011 at 21:36, Shaun Thomas wrote:
> You can call that instead of max, and it'll be much faster. You can create
> an analog for min if you need it. So for this, you'd call:
Cool, I've needed this function sometimes but never bothered enough to
write it myself. Now I created a wiki
On 06/17/2011 03:31 PM, Jim Nasby wrote:
c_parent_oid CONSTANT oid := (p_parent_schema || '.' ||
p_parent_table )::regclass;
Well isn't *that* a handy bit of magic. How did I not know about that?
Thanks!
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312
On Jun 16, 2011, at 1:36 PM, Shaun Thomas wrote:
> /**
> * Return the Maximum INT Value for a Partitioned Table Column
> *
> * @param string Name of Schema of the base partition table.
> * @param string Name of the base partition table.
> * @param string Name of column to search.
> */
> CREATE O
On 06/17/2011 08:43 AM, Shaun Thomas wrote:
It's a bit of a hack, but it's worked fine for us while we wait for
the planner to catch up. :)
Right. In situations where people can modify their application to
redirect MIN/MAX() calls over to directly query the individual
partitions, that's a gr
Yes, confirmed that the problem is in the partitioned table.
Shaun, that solution is brilliant.
Thank you,
Svetlin Manavski
On Thu, Jun 16, 2011 at 7:36 PM, Shaun Thomas wrote:
> On 06/16/2011 12:25 PM, Magnus Hagander wrote:
>
> PostgreSQL 9.0 is unable to use an index scan to find min/max on
On 06/17/2011 06:22 AM, Svetlin Manavski wrote:
Shaun, that solution is brilliant.
Don't thank me. I actually got the basic idea from a post here a couple
years ago. The only difference is I formalized it somewhat and put it in
our utility schema, where I put lots of other random useful stor
On 06/16/2011 12:25 PM, Magnus Hagander wrote:
PostgreSQL 9.0 is unable to use an index scan to find min/max on a
partitioned table. 9.1, however, can do that.
Unfortunately this is true. You can fake it this way though:
/**
* Return the Maximum INT Value for a Partitioned Table Column
*
* @p
On Thu, Jun 16, 2011 at 15:55, Svetlin Manavski
wrote:
> Hi everybody,
>
> I am running PostgreSQL 9.0 which performs well in most of the cases. I
> would skip all the parameters if these are not necessary.
> I need to frequently (every min) get the max value of the primary key column
> on some ta
On 2011-06-16 15:55, Svetlin Manavski wrote:
Hi everybody,
I am running PostgreSQL 9.0 which performs well in most of the cases. I
would skip all the parameters if these are not necessary.
I need to frequently (every min) get the max value of the primary key column
on some tables, like this cas
Greg Smith writes:
> Karl Larsson wrote:
>> When I make a subquery Postgres don't care about my indexes and makes
>> a seq scan instead of a index scan. Why?
> Data set is just too small for it to matter. Watch what happens if I
> continue from what you posted with much bigger tables:
> ...
> T
On Thu, Dec 17, 2009 at 6:17 PM, Karl Larsson wrote:
>> Best bet is to post the real problem, not a semi-representational made
>> up one. Unless the made up "test case" is truly representative and
>> recreates the failure pretty much the same was as the original.
>
> I agree at some level but I
> Best bet is to post the real problem, not a semi-representational made
> up one. Unless the made up "test case" is truly representative and
> recreates the failure pretty much the same was as the original.
I agree at some level but I generally believe other people won't read
a big mail like th
On Thu, Dec 17, 2009 at 6:10 PM, Karl Larsson wrote:
> On Fri, Dec 18, 2009 at 1:10 AM, Greg Smith wrote:
>>
>> Karl Larsson wrote:
>>>
>>> When I make a subquery Postgres don't care about my indexes and makes
>>> a seq scan instead of a index scan. Why?
>>
>> Data set is just too small for it to
On Fri, Dec 18, 2009 at 1:10 AM, Greg Smith wrote:
> Karl Larsson wrote:
>
>> When I make a subquery Postgres don't care about my indexes and makes
>> a seq scan instead of a index scan. Why?
>>
> Data set is just too small for it to matter. Watch what happens if I
> continue from what you poste
On Thu, Dec 17, 2009 at 4:46 PM, Karl Larsson wrote:
>
>
> On Fri, Dec 18, 2009 at 12:26 AM, Scott Marlowe
> wrote:
>>
>> On Thu, Dec 17, 2009 at 4:22 PM, Karl Larsson
>> wrote:
>> > Hello.
>> >
>> > I have a problem I don't understand. I hope it's a simple problem and
>> > I'm
>> > just stupid.
Karl Larsson wrote:
When I make a subquery Postgres don't care about my indexes and makes
a seq scan instead of a index scan. Why?
Data set is just too small for it to matter. Watch what happens if I
continue from what you posted with much bigger tables:
postgres=# truncate table table_one;
T
On Fri, Dec 18, 2009 at 12:26 AM, Scott Marlowe wrote:
> On Thu, Dec 17, 2009 at 4:22 PM, Karl Larsson
> wrote:
> > Hello.
> >
> > I have a problem I don't understand. I hope it's a simple problem and I'm
> > just stupid.
> >
> > When I make a subquery Postgres don't care about my indexes and mak
Karl Larsson wrote:
> When I make a subquery Postgres don't care about my indexes and
> makes a seq scan instead of a index scan. Why?
> Total runtime: 0.133 ms
Because it thinks that it's faster that way with the particular data
you now have in your tables. With more data, it might think
On Thu, Dec 17, 2009 at 4:22 PM, Karl Larsson wrote:
> Hello.
>
> I have a problem I don't understand. I hope it's a simple problem and I'm
> just stupid.
>
> When I make a subquery Postgres don't care about my indexes and makes
> a seq scan instead of a index scan. Why?
PostgreSQL uses an intell
An index-scan makes only sense if rid contains considerable more than
300 rows.
I'm sorry, I meant using index to get the row.
Andrus.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/p
am Sun, dem 23.11.2008, um 6:20:08 +0200 mailte Andrus folgendes:
> Gregory,
>
> > I would suggest running ANALYZE
> >idtellUued at some point before the problematic query.
>
> Thank you.
> After adding analyze all is OK.
> Is analyze command required in 8.3 also ?
Yes.
Andreas
--
Andreas
Andrus <[EMAIL PROTECTED]> schrieb:
> There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int.
> Instead of using single key index, 8.1.4 scans over whole rid table.
> Sometimes idtelluued can contain more than single row so replacing join
> with equality is not possible.
>
> How
Andrus <[EMAIL PROTECTED]> schrieb:
> There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int.
> Instead of using single key index, 8.1.4 scans over whole rid table.
> Sometimes idtelluued can contain more than single row so replacing join
> with equality is not possible.
>
> How
Gregory,
I would suggest running ANALYZE
idtellUued at some point before the problematic query.
Thank you.
After adding analyze all is OK.
Is analyze command required in 8.3 also ?
Or is it better better to specify some hint at create temp table time since
I know the number of rows before r
Gregory Stark <[EMAIL PROTECTED]> writes:
> "Andrus" <[EMAIL PROTECTED]> writes:
>> There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int.
>> Instead of using single key index, 8.1.4 scans over whole rid table.
>> Sometimes idtelluued can contain more than single row so replacing
"Andrus" <[EMAIL PROTECTED]> writes:
> There are indexes on rid(dokumnr) and dok(dokumnr) and dokumnr is int.
> Instead of using single key index, 8.1.4 scans over whole rid table.
> Sometimes idtelluued can contain more than single row so replacing join with
> equality is not possible.
>
> How
- why am I still getting a seq scan ?
You'll seq scan tmp1 obviously, and also the other table since you fetch a
very large part of it in the process.
It's the only way to do this query since there is no WHERE to restrict the
number of rows and the DISTINCT applies on columns from both tab
kevin kempter escribió:
Hi List;
I have a large tble (playback_device) with 6million rows in it. The
aff_id_tmp1 table has 600,000 rows.
I also have this query:
select distinct
tmp1.affiliate_id,
tmp1.name,
tmp1.description,
tmp1.create_dt,
tmp1.playback_device_id,
pf.segment_id
from
aff_id_t
On Thu, Apr 17, 2008 at 11:24 AM, kevin kempter
<[EMAIL PROTECTED]> wrote:
> Hi List;
>
> I have a large tble (playback_device) with 6million rows in it. The
> aff_id_tmp1 table has 600,000 rows.
> - why am I still getting a seq scan ?
>
You're selecting almost all the rows in the product of aff
On Jun 1, 2007, at 11:48 , Tyler Durden wrote:
I'm having some problems in performance in a simple select count(id)
from
Unrestricted count() (i.e., no WHERE clause) will perform a
sequential scan. If you're looking for faster ways to store table row
count information, please search t
Tyler Durden wrote:
Hi,
I'm having some problems in performance in a simple select count(id)
from I have 700 000 records in one table, and when I do:
# explain select (id) from table_name;
-[ RECORD 1
]
QUERY PLAN | Seq Scan on
(For those not knowing - it's ReadFile/WriteFile where you pass an array
of "this many bytes to this address" as parameters)
Isn't that like the BSD writev()/readv() that Linux supports also? Is
that something we should be using on Unix if it is supported by the OS?
Nope, readv()/writev() read/w
Merlin Moncure wrote:
readv and writev are in the single unix spec...and yes ...
On some systems they might just be implemented as a loop inside the
library, or even as a macro.
You sure?
Requirements like this:
http://www.opengroup.org/onlinepubs/007908799/xsh/write.html
"Write requests of {PIPE
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> Is there a reason why readv/writev have not been considered in the past?
Lack of portability, and lack of obvious usefulness that would justify
dealing with the lack of portability.
I don't think there's any value in trying to write ordinary buffers
> Magnus Hagander wrote:
> > I don't think that's correct either. Scatter/Gather I/O is used to
SQL
> > Server can issue reads for several blocks from disks into it's own
> > buffer cache with a single syscall even if these buffers are not
> > sequential. It did make significant performance improve
>> I don't think that's correct either. Scatter/Gather I/O is
>used to SQL
>> Server can issue reads for several blocks from disks into it's own
>> buffer cache with a single syscall even if these buffers are not
>> sequential. It did make significant performance improvements
>when they
>> added
Magnus Hagander wrote:
> I don't think that's correct either. Scatter/Gather I/O is used to SQL
> Server can issue reads for several blocks from disks into it's own
> buffer cache with a single syscall even if these buffers are not
> sequential. It did make significant performance improvements when
Josh Berkus wrote:
Now you can see why other DBMSs don't use the OS disk cache. ...
...as long as we use the OS disk cache, we can't
eliminate checkpoint spikes, at least on Linux.
Wouldn't the VM settings like the ones under /proc/sys/vm
and/or the commit=XXX mount option if using ext3 be a go
PFC <[EMAIL PROTECTED]> writes:
> You can also read 'internal not yet developed postgres cache manager'
> instead of OS if you don't feel like talking kernel developers into
> implementing this thing.
It exists already, it's called aio.
But there are a *lot* of details you skipped over.
In the 'wishful hand waving' department :
read index -> determine (tuple id,page) to hit in table -> for each of
these, tell the OS 'I'm gonna need these' via a NON BLOCKING call. Non
blocking because you feed the information to the OS as you read the index,
streaming it.
Meanwhile, th
In the last exciting episode, [EMAIL PROTECTED] ("Merlin Moncure") wrote:
>> It seems inevitable that Postgres will eventually eliminate that
>> redundant layer of buffering. Since mmap is not workable, that
>> means using O_DIRECT to read table and index data.
>
> What about going the other way an
>Josh Berkus wrote:
>> Now you can see why other DBMSs don't use the OS disk cache. There's
>> other
>> issues as well; for example, as long as we use the OS disk cache, we
>can't
>> eliminate checkpoint spikes, at least on Linux. No matter what we do
>with
>> the bgwriter, fsyncing the OS disk c
Josh Berkus writes:
> Why is mmap not workable?It would require far-reaching changes to our
> code
> -- certainly -- but I don't think it can be eliminated from consideration.
Fundamentally because there is no facility for being notified by the OS before
a page is written to disk. And the
Josh Berkus wrote:
> Now you can see why other DBMSs don't use the OS disk cache. There's
> other
> issues as well; for example, as long as we use the OS disk cache, we
can't
> eliminate checkpoint spikes, at least on Linux. No matter what we do
with
> the bgwriter, fsyncing the OS disk cache cau
Josh Berkus writes:
> Why is mmap not workable?
We can't control write order. There are other equally bad problems,
but that one alone eliminates it from consideration. See past discussions.
regards, tom lane
---(end of broadcast)---
Tom, Greg, Merlin,
> But for example,
> if our buffer management algorithm recognizes an index page as being
> heavily hit and therefore keeps it in cache for a long time, then when
> it does fall out of cache you can be sure it's going to need to be read
> from disk when it's next used, because
> It seems inevitable that Postgres will eventually eliminate that
redundant
> layer of buffering. Since mmap is not workable, that means using
O_DIRECT
> to
> read table and index data.
What about going the other way and simply letting the o/s do all the
caching? How bad (or good) would the perf
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > 8.0, on the other hand, has a new algorithm that specifically tries to
> > protect against the shared buffers being blown out by a sequential
> > scan. But that will only help if it's the shared buffers being
> > thr
Greg Stark <[EMAIL PROTECTED]> writes:
> 8.0, on the other hand, has a new algorithm that specifically tries to
> protect against the shared buffers being blown out by a sequential
> scan. But that will only help if it's the shared buffers being
> thrashed that's hurting you, not the entire OS file
Mark Aufflick <[EMAIL PROTECTED]> writes:
> Obviously Q2 is faster than Q1,
That's not really obvious at all. If there are lots of records being returned
the index might not be faster than a sequential scan.
> My assumption is that the sequential scan is blowing the index from any cache
> it mi
Hi Rod,
(B
(B> Any solution fixing buffers should probably not take into consideration
(B> the method being performed (do you really want to skip caching a
(B> sequential scan of a 2 tuple table because it didn't use an index) but
(B> the volume of data involved as compared to the size of the
The world rejoiced as [EMAIL PROTECTED] (Mark Aufflick) wrote:
> Hi All,
>
> I have boiled my situation down to the following simple case:
> (postgres version 7.3)
>
> * Query 1 is doing a sequential scan over a table (courtesy of field
> ILIKE 'foo%') and index joins to a few others
> * Query 2 is
> My concern is that this kind of testing has very little relevance to the
> real world of multiuser processing where contention for the cache becomes an
> issue. It may be that, at least in the current situation, postgres is
> giving too much weight to seq scans based on single user, straight
Hi,
I think there was some discussion about seq scans messing up the cache, and
talk about doing something about it but I don't think it has been addressed
yet. Maybe worth a troll through the archives.
It is certainly true that in many situations, a seq scan is preferable to
using an index. I
On Mon, 2004-07-05 at 15:46 +0200, [EMAIL PROTECTED] wrote:
> On Mon, Jul 05, 2004 at 11:44:13PM +1200, Andrew McMillan wrote:
>
> > > DateTimeIndex was created on both columns (Date/Time):
> > > CREATE INDEX "DateTimeIndex" ON "tablex" USING btree ("Date", "Time");
> > PostgreSQL is always going
On Mon, Jul 05, 2004 at 11:44:13PM +1200, Andrew McMillan wrote:
> > DateTimeIndex was created on both columns (Date/Time):
> > CREATE INDEX "DateTimeIndex" ON "tablex" USING btree ("Date", "Time");
> PostgreSQL is always going to switch at some point, where the number of
> rows that have to be re
On Mon, 2004-07-05 at 12:15 +0200, [EMAIL PROTECTED] wrote:
> Hello,
>
> Can anybody suggest any hint on this:
>
> temp=> EXPLAIN SELECT DISTINCT "number" FROM "tablex" WHERE "Date" BETWEEN
> '2004-06-28'::date AND '2004-07-04'::date AND "Time" BETWEEN '00:00:00'::time AND
> '18:01:00'::time;
>
[EMAIL PROTECTED] wrote:
-> Index Scan using "DateTimeIndex" on "tablex" (cost=0.00..298272.66 rows=89903 width=8)
-> Seq Scan on "tablex" (cost=0.00..307137.34 rows=97900 width=8)
Basically, the difference is in upper "Time" value (as you can see, it's
18:01:00 in the first query and 19:01:0
On Mon, 2004-06-07 at 16:12, Dan Langille wrote:
> On 7 Jun 2004 at 16:00, Rod Taylor wrote:
>
> > On Mon, 2004-06-07 at 15:45, Dan Langille wrote:
> > > A production system has had a query recently degrade in performance.
> > > What once took < 1s now takes over 1s. I have tracked down the
>
On Mon, 2004-06-07 at 15:45, Dan Langille wrote:
> A production system has had a query recently degrade in performance.
> What once took < 1s now takes over 1s. I have tracked down the
> problem to a working example.
What changes have you made to postgresql.conf?
Could you send explain analys
On 7 Jun 2004 at 18:49, Dan Langille wrote:
> On 7 Jun 2004 at 16:38, Rod Taylor wrote:
> > * random_page_cost (good disks will bring this down to a 2 from a
> > 4)
>
> I've got mine set at 4. Increasing it to 6 gave me a 1971ms query.
> At 3, it was a 995ms. Setting it to 2 gav
On 7 Jun 2004 at 16:38, Rod Taylor wrote:
> On Mon, 2004-06-07 at 16:12, Dan Langille wrote:
> > I grep'd postgresql.conf:
> >
> > #effective_cache_size = 1000# typically 8KB each
> > #random_page_cost = 4 # units are one sequential page fetch cost
>
> This would be the issue. You
On 7 Jun 2004 at 16:38, Rod Taylor wrote:
> On Mon, 2004-06-07 at 16:12, Dan Langille wrote:
> > On 7 Jun 2004 at 16:00, Rod Taylor wrote:
> >
> > > On Mon, 2004-06-07 at 15:45, Dan Langille wrote:
> > > > A production system has had a query recently degrade in performance.
> > > > What once to
On 7 Jun 2004 at 16:00, Rod Taylor wrote:
> On Mon, 2004-06-07 at 15:45, Dan Langille wrote:
> > A production system has had a query recently degrade in performance.
> > What once took < 1s now takes over 1s. I have tracked down the
> > problem to a working example.
>
> What changes have you
Richard Huxton <[EMAIL PROTECTED]> writes:
> It's not entirely clear to me why this form is different from the other form
> though.
The code that checks for expressions containing unstable functions
doesn't look inside sub-selects. Arguably this is a bug, but people
were relying on that behavior
On Friday 06 February 2004 07:19, Octavio Alvarez wrote:
> Hi!
>
>I'd like to know if this is expected behavior. These are two couples of
> queries. In each couple, the first one has a WHERE field = function()
> condition, just like the second one, but in the form WHERE field =
> (SELECT functi
Tomasz Myrta said:
> Dnia 2004-02-06 08:19, U¿ytkownik Octavio Alvarez napisa³:
>> In each couple, the first one has a WHERE field = function()
>> condition, just like the second one, but in the form WHERE field =
>> (SELECT function()). In my opinion, both should have the same execution
>> plan,
84 matches
Mail list logo
