I have a simple case, selecting on a LIKE where clause over a single
column that has an index on it. On windows it uses the index - on
linux it does not. I have exactly the same scema and data in each,
and I have run the necessary analyze commands on both.
Windows is running 8.1.4
Linux is
Please unsubscribe me! Thank you!
Also, it would be better to have a message foot saying how to unsubscribe.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to
simon godden wrote:
The only major difference between the hardware is that the windows
machine has 2gb RAM and a setting of 1 shared memory pages,
whereas the linux machine has 756Mb RAM and a setting of 3000 shared
memory pages (max. shared memory allocation of 32Mb). I can't see any
other
(Sending again because I forgot to reply to all)
On 10/4/06, Heikki Linnakangas [EMAIL PROTECTED] wrote:
You can increase the max shared memory size if you have root access. See
http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html#SYSVIPC-PARAMETERS
Scroll down for
On 10/4/06, Richard Huxton dev@archonet.com wrote:
Issue set enable_seqscan=false and then run your explain analyse. If
your query uses the index, what is the estimated cost? If the estimated
cost is larger than a seq-scan that would indicate your configuration
settings are badly out-of-range.
simon godden wrote:
(Sending again because I forgot to reply to all)
On 10/4/06, Heikki Linnakangas [EMAIL PROTECTED] wrote:
You can increase the max shared memory size if you have root access. See
http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html#SYSVIPC-PARAMETERS
[Csaba Nagy - Thu at 10:45:35AM +0200]
So you should check for idle in transaction sessions, those are bad...
or any other long running transaction.
Thank you (and others) for pointing this out, you certainly set us on
the right track. We did have some few unclosed transactions;
transactions
Hi, Alex,
Alex Stapleton wrote:
explain analyze is more helpful because it prints the times.
You can always use the \timing flag in psql ;)
Have you ever tried EXPLAIN ANALYZE?
\timing gives you one total timing, but EXPLAIN ANALYZE gives you
timings for sub-plans, including real row
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Adnan DURSUN
i want to be can read an execution plan when
i look at it.
So, is there any doc about how it should be read ?
You are asking how to read the output from EXPLAIN?
simon godden wrote:
I did that, e.g. initdb --locale=C, re-created all my data and have
exactly the same problem.
I have two indexes, one with no options, and one with the varchar
operator options.
So the situation now is:
If I do a like query it uses the index with the varchar options;
If I
On Wed, 2006-10-04 at 07:38 -0500, Dave Dutcher wrote:
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Adnan DURSUN
i want to be can read an execution plan when
i look at it.
So, is there any doc about how it should be
On 10/4/06, Richard Huxton dev@archonet.com wrote:
simon godden wrote:
From psql, a show all command will list all your config settings and
let you check the lc_xxx values are correct.
lc_collate is C, as are all the other lc settings.
I have run the analyze commands.
Still the same.
--
On Tue, 2006-10-03 at 18:29 -0700, Tomeh, Husam wrote:
* When any session updates the data that already in shared
buffer,
does Postgres synchronize the data both disk and shared buffers area
immediately ?
Not necessarily true. When a block is modified in the shared buffers,
the
simon godden wrote:
On 10/4/06, Richard Huxton dev@archonet.com wrote:
simon godden wrote:
From psql, a show all command will list all your config settings and
let you check the lc_xxx values are correct.
lc_collate is C, as are all the other lc settings.
I have run the analyze commands.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
lc_collate is C, as are all the other lc settings.
I have run the analyze commands.
Still the same.
That is strange. I figured it had to be related to the locale and the LIKE
operator. I'm
Can you post EXPLAIN ANALYSE for the LIKE and queries that should be
using the index? With enable_seqscan on and off please.
OK - I don't know what happened, but now my linux installation is
behaving like the windows one. I honestly don't know what changed,
which I know doesn't help people
I think I am being stupid now.
The query was returning so many rows (87% of the rows in the table)
that a seq-scan was of course the best way.
Sorry - all is now working and the problem was the locale issue.
Thanks so much for your help everyone.
--
Simon Godden
On Wed, Oct 04, 2006 at 10:03:00 +0200,
Luc Delgado [EMAIL PROTECTED] wrote:
Please unsubscribe me! Thank you!
If you really can't figure out how to unsubscribe from a list, you should
contact the list owner, not the list. The list members can't unsubscribe you
(and it isn't their job to)
Bruno Wolff III wrote:
On Wed, Oct 04, 2006 at 10:03:00 +0200,
Luc Delgado [EMAIL PROTECTED] wrote:
Please unsubscribe me! Thank you!
If you really can't figure out how to unsubscribe from a list, you should
contact the list owner, not the list. The list members can't unsubscribe you
To be a bit constructive, could it be an idea to add unsubscribe
information as one of the standard tailer tips? Then unsubscribe info
wouldn't appear in every mail, but often enough for people considering
to unsubscribe. To be totally non-constructive, let me add a bit to the
noise below:
This seems to be the nearly unanimous response to people posting an
unsubscribe request to the postgres mailing lists. I emphatically
agree with the argument - people should know better than that, and the
information included in the e-mail headers should be more than
sufficient. Every
[Joshua]
It is ridiculous that this community expects people to read email
headers to figure out how to unsubscribe from our lists.
I always check the headers when I want to unsubscribe from any mailing
list, and I think most people on this list have above average knowledge
of such
I also don't care about that argument in this situation. People
ignorantly posting an unsubscribe to the list get this kind of response
because it's an annoyance to the list users,
Over time especially now, we will see many more users versus
developers. Most users will never know how (nor
On Wed, Oct 04, 2006 at 08:30:03 -0700,
Joshua D. Drake [EMAIL PROTECTED] wrote:
Although I 100% agree with you Bruno, it should be noted that our lists
are a closed box for most people. They don't follow what is largely
considered standard amongst lists which is to have list information at
On Wed, 04 Oct 2006 09:00:45 -0700
Joshua D. Drake [EMAIL PROTECTED] wrote:
So if you want to shut me up, lets get the footer added.
Of course, that doesn't fix the problem 100%. I am on lists that do
show that info in the footer and people still send unsubscribe messages
to the list.
By the
D'Arcy J.M. Cain wrote:
On Wed, 04 Oct 2006 09:00:45 -0700
Joshua D. Drake [EMAIL PROTECTED] wrote:
So if you want to shut me up, lets get the footer added.
Of course, that doesn't fix the problem 100%. I am on lists that do
show that info in the footer and people still send unsubscribe
I would prefer just making the unsubscribe instructions easy to find on
the web.
They actually reasonably are. If you go to www-community/support-lists
Sincerely,
Joshua D. Drake
---(end of broadcast)---
TIP 5: don't forget to increase
I'd prefer to have a short footer link called something like Mailing
List Page which would take you to a page where you could subscribe,
unsubscribe, or view the archives. I think that making the link short
and also making it a quick shortcut away from the archives tips the
scales in terms of
On Wed, 2006-10-04 at 18:02, Csaba Nagy wrote:
If we didn't want to add it for each list we could just add a link here:
http://www.postgresql.org/community/lists/subscribe
OK, now that I had a second look on that page, it does contain
unsubscription info... but it's well hidden for the
On Oct 4, 2006, at 9:00 AM, Joshua D. Drake wrote:
I also don't care about that argument in this situation. People
ignorantly posting an unsubscribe to the list get this kind of
response
because it's an annoyance to the list users,
Over time especially now, we will see many more users
Steve Atkins wrote:
On Oct 4, 2006, at 9:00 AM, Joshua D. Drake wrote:
I also don't care about that argument in this situation. People
ignorantly posting an unsubscribe to the list get this kind of response
because it's an annoyance to the list users,
Over time especially now, we will see
Hi, Tobias,
Tobias Brox wrote:
How can you have a default value on a primary key?
Just declare the column with both a default value and a primary key
constraint.
It makes sense when the default value is calculated instead of a
constant, by calling a function that generates the key.
In fact,
I'm having an interesting (perhaps anomalous) variability in UPDATE
performance on a table in my database, and wanted to see if there was
any interest in looking further before I destroy the evidence and move on.
The table, VOTER, contains 3,090,013 rows and each row is about 120
bytes wide.
Look at this:
NBET= explain select * from account_transaction where users_id=123456 order by
created desc limit 10;
QUERY PLAN
can you do explain analyze on the two select queries on either side of
the union separatly? the subquery is correctly written and unlikely
to be a problem (in fact, good style imo). so lets have a look at
both sides of facil query and see where the problem is.
Sorry for the delay, the
Thanks Tobias. The difference here though, is that in terms of your
database I am doing a query to select the most recent transaction for
EACH user at once, not just for one user. If I do a similar query to
yours to get the last transaction for a single user, my query is fast
like yours.
On Wed, Oct 04, 2006 at 08:30:03AM -0700, Joshua D. Drake wrote:
They don't follow what is largely
considered standard amongst lists which is to have list information at
the bottom of each e-mail.
In my experience such a footer doesn't do much to prevent people sending
unsubscribe messages to
Steve Peterson [EMAIL PROTECTED] writes:
If I run the statement:
(1): UPDATE voter SET gender = 'U';
on the table in this condition, the query effectively never ends --
I've allowed it to run for 12-14 hours before giving up.
...
When (1) is running, the machine is very nearly idle, with
Tobias Brox [EMAIL PROTECTED] writes:
NBET= explain select * from account_transaction where users_id=123456 order
by created desc, id desc limit 10;
We have indices on the users_id field and the (users_id, created)-tuple.
Neither of those indexes can provide the sort order the query is
[Tom Lane - Wed at 04:33:54PM -0400]
We have indices on the users_id field and the (users_id, created)-tuple.
Neither of those indexes can provide the sort order the query is asking
for.
Ah; that's understandable - the planner have two options, to do a index
traversion without any extra
On 10/4/06, Carlo Stonebanks [EMAIL PROTECTED] wrote:
can you do explain analyze on the two select queries on either side of
the union separatly? the subquery is correctly written and unlikely
to be a problem (in fact, good style imo). so lets have a look at
both sides of facil query and
On Fri, 29 Sep 2006, Jim C. Nasby wrote:
There's no join criteria for umdb.node... is that really what you want?
Unfortunately, yes, it is.
I've taken in all of everybody's helpful advice (thanks!) and reworked
things a little, and now I'm left with this expensive nugget:
select aj.album
Hi Merlin,
Here are the results. The query returned more rows (65 vs 12) because of the
vague postal_code.
In reality, we would have to modify the postal_code logic to take advantage
of full zip codes when they were avalable, not unconditionally truncate
them.
Carlo
explain analyze select
I've got another query I'm trying to optimize:
select aj.album from
public.track t
join public.albumjoin aj
on (aj.track = t.id)
join (select id from public.albummeta am where tracks between 10 and
14) lam
on (lam.id = aj.album)
where (t.name % '01New OrderEvil Dust' or t.name %
Ben [EMAIL PROTECTED] writes:
How can I get the planner to not expect so many rows to be returned?
Write an estimation function for the pg_trgm operator(s). (Send in a
patch if you do!) I see that % is using contsel which is only a stub,
and would likely be wrong for % even if it weren't.
Both commands seem to be saturating the disk. There's nothing else
running in the database, and all of the locks have 't' in the granted
column, which I'm assuming means they're not blocked.
According to the statistics, the original table has 889 mb and
indexes of 911mb, whereas the copy has
I'm pretty sure that the table was empty before doing the load, but I
gave this a shot. It didn't have an impact on the results.
The behavior also persists across a dump/reload of the table into a
new install on a different machine. IIRC dump/reload rebuilds
indexes from scratch.
Steve
47 matches
Mail list logo
