On 10/5/06, Carlo Stonebanks <[EMAIL PROTECTED]> wrote:
Hi Merlin,
Here are the results. The query returned more rows (65 vs 12) because of the
vague postal_code.
right. interestingly, the index didn't work properly anyways.
regardless, this is easily solvable but it looks like we might be
lo
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
At
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
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
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 % '04OrbitalOpen
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
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
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
[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
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
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,
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
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. It'
> 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 se
The table, VOTER, contains 3,090,013 rows and each row is about 120 bytes
wide. It's loaded via a batch process in one shot, and the load is
followed by an VACUUM FULL ANALYZE. Its structure is shown at the bottom
of the message.
if the table wasn't empty before and has indices defined, try
Look at this:
NBET=> explain select * from account_transaction where users_id=123456 order by
created desc limit 10;
QUERY PLAN
-
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.
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,
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
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"
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 th
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 ut
> 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 incre
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 unsu
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 t
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 informa
> 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
+1
When I want to unsubscribe from a list (very rare in my case, I don't
subscribe in the first place if I'm not sure I want to get it), I start
by looking where I s
> 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
> [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 suc
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 conceiva
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:
[Brun
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 unsubscrib
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 t
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
---(
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
> -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
simon godden wrote:
On 10/4/06, Richard Huxton 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 buff
On 10/4/06, Richard Huxton 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.
--
Simon Godden
--
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
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 do
> -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
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 co
[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;
transaction
simon godden wrote:
If the index isn't used, then we have problem #3. I think this is what
you are actually seeing. Your locale is something other than "C" and PG
doesn't know how to use like with indexes. Read up on operator classes
or change your locale.
http://www.postgresql.org/docs/8.1/stati
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
Sc
On 10/4/06, Richard Huxton 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.
I did that an
(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 Linux-spec
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
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 [
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 runn
51 matches
Mail list logo
