Tom Lane wrote:
Gavin Hamill <[EMAIL PROTECTED]> writes:
If I replace the
(allocation0_."Date" between '2006-06-09 00:00:00.00' and
'2006-06-09 00:00:00.00')
with
allocation0_."Date" ='2006-04-09 00:00:00.00'
then the query comes back in a few milliseconds (as I'd expect :)
>This should be fixed by the changes I made recently in
choose_bitmap_and
>--- it wasn't being aggressive about pruning overlapping AND conditions
when a sub-OR was involved. It's possible the new coding is >*too*
aggressive, and will reject indexes that it'd be profitable to include;
but at least
Hi, Francisco,
Francisco Reyes wrote:
> I only wonder what is safer.. using a second or two in commit_delay or
> using fsync = off.. Anyone cares to comment?
It might be that you misunderstood commit_delay. It will not only delay
the disk write, but also block your connnection until the write ac
Hi, Tom,
Tom Lane wrote:
> Well, the other thing that's going on here is that we know we are
> overestimating the cost of nestloop-with-inner-indexscan plans.
> The current estimation for that is basically "outer scan cost plus N
> times inner scan cost" where N is the estimated number of outer t
> > For now, I only could get good performance with bacula and
> postgresql
> > when disabling fsync...
>
>
> Isn't that less safe?
Most definitly.
FWIW, I'm getting pretty good speeds with Bacula and PostgreSQL on a
reasonably small db (file table about 40 million rows, filename about
5.2 mi
For the purpose of the application I need to establish some form of
serialization, therefore I use FOR UPDATE. The query, inside the
function, is like this:
pulitzer2=# explain analyze select id FROM messages JOIN
ticketing_codes_played ON id = message_id WHERE service_id = 1102 AND
receiving_time
Mario Splivalo <[EMAIL PROTECTED]> writes:
> For the purpose of the application I need to establish some form of
> serialization, therefore I use FOR UPDATE. The query, inside the
> function, is like this:
> pulitzer2=# explain analyze select id FROM messages JOIN
> ticketing_codes_played ON id =
"Mikael Carneholm" <[EMAIL PROTECTED]> writes:
> Ok, cool. I don't have time to test this right now as the project has to
> move on (and I guess testing the fix would require a dump+build CVS
> version+restore), but as a temporary workaround I simly dropped the
> xda_dat index (all queries on that
Hi!
I am having trouble with like statements on one of my tables.
I already tried a vacuum and analyze but with no success.
The database is PostgreSQL Database Server 8.1.3 on i686-pc-mingw32
I get the following explain and I am troubled by the very high
"startup_cost" ... does anyone hav
> -Original Message-
> From: [EMAIL PROTECTED]
[mailto:pgsql-performance-
> [EMAIL PROTECTED] On Behalf Of Tarabas (Manuel Rorarius)
> Subject: [PERFORM] Problem with LIKE-Performance
>
> Hi!
>
> I am having trouble with like statements on one of my tables.
It looks like you are getti
Hi Dave,
DD> It looks like you are getting a sequential scan instead of an index
DD> scan. What is your locale setting? As far as I know Postgres doesn't
DD> support using indexes with LIKE unless you are using the C locale.
Actually no, I am using de_DE as locale because I need the german
orde
"Tarabas (Manuel Rorarius)" <[EMAIL PROTECTED]> writes:
> I get the following explain and I am troubled by the very high
> "startup_cost" ... does anyone have any idea why that value is so
> high?
> {SEQSCAN
>:startup_cost 1.00
You have enable_seqscan = off, no?
Please refrain from
Mario Splivalo <[EMAIL PROTECTED]> writes:
>> If there is concurrent locking,
>> you're also running a big risk of deadlock because two processes might
>> try to lock the same rows in different orders.
> I think there is no risk of a deadlock, since that particular function
> is called from the mi
Hi Tom,
TL> "Tarabas (Manuel Rorarius)" <[EMAIL PROTECTED]> writes:
>> I get the following explain and I am troubled by the very high
>> "startup_cost" ... does anyone have any idea why that value is so
>> high?
>> {SEQSCAN
>>:startup_cost 1.00
TL> You have enable_seqscan = off, no?
Hi,
i remember something that you need a special index with locales<>"C".
You nned a different operator class for this index smth. like:
CREATE INDEX idx_image_title
ON image
USING btree
(title varchar_pattern_ops);
You can find the details here:
http://www.postgresql.org/docs/8.1/interact
Dave Dutcher a écrit :
It looks like you are getting a sequential scan instead of an index
scan. What is your locale setting? As far as I know Postgres doesn't
support using indexes with LIKE unless you are using the C locale.
It does if you create your index this way :
CREATE INDEX idx_
Hi Hakan,
HK> i remember something that you need a special index with locales<>"C".
HK> You nned a different operator class for this index smth. like:
HK> CREATE INDEX idx_image_title
HK> ON image
HK> USING btree
HK> (title varchar_pattern_ops);
I also forgot that, thanks a lot for the hint
On 18.04.2006, at 17:16 Uhr, Tarabas (Manuel Rorarius) wrote:
Is there any way to speed the like's up with a different locale than C
or to get an order by in a different Locale although using the
default C locale?
Sure. Just create the index with
create index __index on (
varchar_pattern_o
Hi Tom,
TL> As already noted, it might be worth your while to add an index using the
TL> pattern-ops opclass to help with queries like this.
I have done that now and it works very fine as supposed.
The problem with the high startup_costs disappeared somehow after the
change of the enable_seqscan
"Tarabas (Manuel Rorarius)" <[EMAIL PROTECTED]> writes:
> After removing the enable_seqscan = off and making sure it was gone,
> it is a lot faster again.
> Now it takes about 469.841 ms for the select.
Um, no, enable_seqscan would certainly not have had any effect on the
*actual* runtime of this
"Sriram Dandapani" <[EMAIL PROTECTED]> writes:
> Got an explain analyze output..Here it is
> "Seq Scan on c_chkpfw_hr_tr a (cost=0.00..225975659.89 rows=11000
> width=136) (actual time=2.345..648070.474 rows=22001 loops=1)"
> " Filter: (subplan)"
> " SubPlan"
> "-> Bitmap Heap Scan on chkpf
Thx Tom
I guess I have to abandon the bulk update. The columns in the where
clause comprise 80% of the table columns..So indexing all may not help.
The target table will have on average 60-180 million rows.
I will attempt the in instead of exist and let you know the result
-Original Message-
Tarabas (Manuel Rorarius) wrote:
Hi Tom,
TL> As already noted, it might be worth your while to add an index using the
TL> pattern-ops opclass to help with queries like this.
I have done that now and it works very fine as supposed.
The problem with the high startup_costs disappeared somehow aft
Hi Richard,
RH> As you can see, the plan is still scanning all the rows. In any case,
RH> you've changed the query - this has % at the beginning and end, which no
RH> index will help you with.
I realize that, the index definately helped a lot with the query where
the % is just at the end. The ti
Suppose you have a table codes :
(
game_id INT,
codeTEXT,
usedBOOL NOT NULL DEFAULT 'f',
prize ...
...
PRIMARY KEY (game_id, code)
)
Just UPDATE codes SET used='t' WHERE used='f' AND game_id=... AND
code=.
Thanks for all responses! I agree with most of you, and say that the RI is
best maintened by Database ! Performance must be improved in other ways
(indexes, hardware, etc)!
- Original Message -
From: "Jim C. Nasby" <[EMAIL PROTECTED]>
To: "Craig A. James" <[EMAIL PROTECTED]>
Cc: "PFC"
Gavin Hamill <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I'm thinking the planner is misestimating something, but it's hard
>> to tell what without breaking it down.
> (allocation0_."Date" between '2006-06-10 00:00:00.00' and
> '2006-06-10 00:00:00.00');
>
On Tue, 18 Apr 2006 13:31:48 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> There should be a fix for this by the time PG 8.2 comes out, but in
> the meantime you might find that it helps to write the range check in
> a way that doesn't have identical bounds, eg
> date >= '2006-06-10'::date AND
Gavin Hamill <[EMAIL PROTECTED]> writes:
> On Tue, 18 Apr 2006 13:31:48 -0400
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> There should be a fix for this by the time PG 8.2 comes out, but in
>> the meantime you might find that it helps to write the range check in
>> a way that doesn't have identical bo
Interesting what's EXPLAIN ANALYZE show if you SET
enable_seqscan=off; ?
You should also consider upgrading to 8.1...
On Thu, Apr 13, 2006 at 12:25:02PM +0200, Cris Carampa wrote:
> Hello, postgresql 7.4.8 on SuSE Linux here.
>
> I have a table called DMO with a column called ORA_RIF defined
On Fri, Apr 14, 2006 at 11:12:55AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > In my case it would be helpful to break the heap access numbers out
> > between seqscans and index scans, since each of those represents very
> > different access patterns. Would adding that b
On Fri, Apr 14, 2006 at 08:05:39AM +0200, Terje Elde wrote:
> Jim Nasby wrote:
> >While working on determining a good stripe size for a database, I
> >realized it would be handy to know what the average request size is.
> >Getting this info is a simple matter of joining pg_stat_all_tables and
>
On Tue, 18 Apr 2006 15:51:44 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> Fair enough, no reason to replace one workaround with another. But
> would you try it on your test case, just to verify the diagnosis?
Yup I can confirm it from testing earlier today - as soon as
the two dates are non-equa
On Fri, Apr 14, 2006 at 03:15:33PM -0500, Scott Marlowe wrote:
> On Fri, 2006-04-14 at 15:09, Francisco Reyes wrote:
> > Michael Stone writes:
> >
> > > I still don't follow that. Why would the RAID level matter? IOW, are you
> > > actually wanting 2 spares, or are you just stick with that becaus
On Tue, Apr 18, 2006 at 01:56:44PM +0200, Magnus Hagander wrote:
> Bacula already serializes access to the database (they have to support
> mysql/myisam), so this shouldn't help. Actually, it might well hurt by
> introducing extra delays.
You have any contact with the developers? Maybe they're a p
On Fri, Apr 14, 2006 at 03:13:43PM +0200, Julien Drouard wrote:
> Hi everyone,
>
> I've seen my pg_toast tables are becoming bigger and bigger. After googling I
> would like to modify my max_fsm_pages parameter to prevent that kind of
> problem. So I'm wondering if changing this parameter is eno
On Tue, Apr 18, 2006 at 12:51:59PM +0200, Markus Schaber wrote:
> > In my mind this is tied into another issue, which is that the planner
> > always costs on the basis of each query starting from zero. In a real
> > environment it's much cheaper to use heavily-used indexes than this cost
> > model
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Actually, if you run with stats_block_level turned on you have a
> first-order approximation of what is and isn't cached.
Only if those stats decayed (pretty fast) with time; which they don't.
regards, tom lane
Markus Schaber <[EMAIL PROTECTED]> writes:
> Hmm, how does effective_cach_size correspond with it? Shouldn't a high
> effective_cache_size have a similar effect?
It seems reasonable to suppose that effective_cache_size ought to be
used as a number indicating how much "stuff" would hang around from
You might try rewriting the coalesces into a row comparison...
WHERE row($4, $5, ...) IS NOT DISTINCT FROM row(interface_id, source_id, ...)
See
http://www.postgresql.org/docs/8.1/interactive/functions-comparisons.html#AEN13408
Note that the docs only show IS DISTINCT FROM, so you might have to
Hi
Apologies if this has already been raised...
PostgreSQL 8.1.3 and prior versions. Vacuum done.
Assuming a single table with columns named c1 to cn and a requirement to
select from a particular position in multiple column order.
The column values in my simple example below denoted by 'cnv' a
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> You might try rewriting the coalesces into a row comparison...
> WHERE row($4, $5, ...) IS NOT DISTINCT FROM row(interface_id, source_id, ...)
That would be notationally nicer, but no help performance-wise; I'm
fairly sure that IS DISTINCT doesn't get o
Theo Kramer <[EMAIL PROTECTED]> writes:
> select * from mytable where
> (c1 = 'c1v' and c2 = 'c2v' and c3 >= 'c3v') or
> (c1 = 'c1v' and c2 > 'c2v') or
> (c1 > 'c1v')
> order by c1, c2, c3;
Yeah ... what you really want is the SQL-spec row comparison operator
select ... where (c1,c2,c3) >
Assuming stats are accurate, you're reading through 5.5M index rows in
order to run that limit query. You didn't say what the index was
actually on, but you might want to try giving each column it's own
index. That might make a bitmap scan feasable.
I know this doesn't help right now, but 8.2 will
On Tue, Apr 18, 2006 at 06:22:26PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Actually, if you run with stats_block_level turned on you have a
> > first-order approximation of what is and isn't cached.
>
> Only if those stats decayed (pretty fast) with time; which they
On Tue, Apr 18, 2006 at 06:26:48PM -0400, Tom Lane wrote:
> Markus Schaber <[EMAIL PROTECTED]> writes:
> > Hmm, how does effective_cach_size correspond with it? Shouldn't a high
> > effective_cache_size have a similar effect?
>
> It seems reasonable to suppose that effective_cache_size ought to be
Markus Schaber <[EMAIL PROTECTED]> writes:
> An easy first approach would be to add a user tunable cache probability
> value to each index (and possibly table) between 0 and 1. Then simply
> multiply random_page_cost with (1-that value) for each scan.
That's not the way you'd need to use it. But
Hi all,
I've been struggling with some performance issues with certain
SQL queries. I was prepping a long-ish overview of my problem
to submit, but I think I'll start out with a simple case of the
problem first, hopefully answers I receive will help me solve
my initial issue.
Consider the follow
"patrick keshishian" <[EMAIL PROTECTED]> writes:
> I've been struggling with some performance issues with certain
> SQL queries. I was prepping a long-ish overview of my problem
> to submit, but I think I'll start out with a simple case of the
> problem first, hopefully answers I receive will help
Suppose you have a table codes :
(
game_idINT,
codeTEXT,
usedBOOL NOT NULL DEFAULT 'f',
prize...
...
PRIMARY KEY (game_id, code)
)
Just UPDATE codes SET used='t' WHERE used='f' AND game_id=... AND
code=...
Then check the rowcount : if
Jim C. Nasby wrote:
That said, it's the transactions against disk that typically matter. On
FreeBSD, you can get an impression of this using 'systat -vmstat', and
watch the KB/t column for your drives.
On a related note, you know of any way to determine the breakdown
between read activit
Jim C. Nasby wrote:
On Tue, Apr 18, 2006 at 06:22:26PM -0400, Tom Lane wrote:
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
Actually, if you run with stats_block_level turned on you have a
first-order approximation of what is and isn't cached.
Only if those stats decayed (pretty fast) with time;
On Wed, Apr 19, 2006 at 04:35:11AM +0200, Terje Elde wrote:
> Jim C. Nasby wrote:
> >>That said, it's the transactions against disk that typically matter. On
> >>FreeBSD, you can get an impression of this using 'systat -vmstat', and
> >>watch the KB/t column for your drives.
> >>
> >
> >On a
On Wed, Apr 19, 2006 at 04:47:40PM +1200, Mark Kirkwood wrote:
> Jim C. Nasby wrote:
> >On Tue, Apr 18, 2006 at 06:22:26PM -0400, Tom Lane wrote:
> >>"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> >>>Actually, if you run with stats_block_level turned on you have a
> >>>first-order approximation of wh
Mark Kirkwood <[EMAIL PROTECTED]> writes:
> Jim C. Nasby wrote:
>> Good point. :/ I'm guessing there's no easy way to see how many blocks
>> for a given relation are in shared memory, either...
> contrib/pg_buffercache will tell you this -
I think the key word in Jim's comment was "easy", ie, che
Jim C. Nasby wrote:
Yeah... not gonna happen...
It's completely mind-boggling that FBSD doesn't track writes and reads
seperately.
'iostat' does not tell you this, but 'gstat' does - its the "geom"
system monitor (a bit annoying that the standard tool is lacking in this
regard...).
Cheer
Tom Lane wrote:
Mark Kirkwood <[EMAIL PROTECTED]> writes:
Jim C. Nasby wrote:
Good point. :/ I'm guessing there's no easy way to see how many blocks
for a given relation are in shared memory, either...
contrib/pg_buffercache will tell you this -
I think the key word in Jim's comment was "e
On Wed, 2006-04-19 at 01:08, Tom Lane wrote:
> Theo Kramer <[EMAIL PROTECTED]> writes:
> > select * from mytable where
> > (c1 = 'c1v' and c2 = 'c2v' and c3 >= 'c3v') or
> > (c1 = 'c1v' and c2 > 'c2v') or
> > (c1 > 'c1v')
> > order by c1, c2, c3;
>
> Yeah ... what you really want is the SQ
58 matches
Mail list logo
