I changed the query to :
EXPLAIN ANALYZE select id from wd_urlusermaps where id in (select id
from wd_urlusermaps where share =1 and userid='219177') order by id desc
limit 20;
and it's much better now (from real execute time), but the cost report
higher
then slower one above, may be I should do
[EMAIL PROTECTED] wrote:
On Wed, 6 Jun 2007, Steinar H. Gunderson wrote:
On Tue, Jun 05, 2007 at 05:59:25PM -0400, Tom Lane wrote:
I think the main argument for partitioning is when you are interested in
being able to drop whole partitions cheaply.
Wasn't there also talk about adding the
continue digging shows:
set cpu_tuple_cost to 0.1;
explain analyze select * from wd_urlusermaps where share =1 and
userid='219177' order by id desc limit 20;
SET
时间: 0.256 ms
QUERY PLAN
weiping [EMAIL PROTECTED] writes:
- Index Scan using urlusermaps_userid on wd_urlusermaps
(cost=0.00..6750.55 rows=1094 width=4) (actual time=1.478..16.563 rows=41
loops=1)
Index Cond: (userid = 219177)
Filter: (share = 1)
It's estimating 1094 rows and getting 41 rows. You might
On Tuesday 05 June 2007 10:34:04 Douglas J Hunley wrote:
On Monday 04 June 2007 17:11:23 Gregory Stark wrote:
Those plans look like they have a lot of casts to text in them. How have
you defined your indexes? Are your id columns really text?
project table:
Indexes:
project_pk PRIMARY
weiping [EMAIL PROTECTED] writes:
- Index Scan using urlusermaps_userid on wd_urlusermaps a
(cost=0.00..6750.55 rows=1094 width=96) (actual time=0.544..5.616
rows=41 loops=1)
Index Cond: (userid = 219177)
Filter: (share = 1)
the userid=219177 got 2000+ record and around 40 shared=1, why
[EMAIL PROTECTED] wrote:
On Tue, 5 Jun 2007, Tom Lane wrote:
[EMAIL PROTECTED] writes:
however I really don't understand why it is more efficiant to have a 5B
line table that you do a report/query against 0.1% of then it is to
have
1000 different tables of 5M lines each and do a report/query
Tom Lane wrote:
The degree to which this is a win is *highly* debatable, and certainly
depends on a whole lot of assumptions about filesystem performance.
You also need to assume that constraint-exclusion in the planner is
pretty doggone cheap relative to the table searches, which means it
[EMAIL PROTECTED] wrote:
various people (not database experts) are pushing to install Oracle
cluster so that they can move all of these to one table with a
customerID column.
They're blowing smoke if they think Oracle can do this. One of my applications
had this exact same problem --
Craig James wrote:
Oracle is simply not better than Postgres in this regard. As far as I
know, there is only one specific situation (discussed frequently here)
where Oracle is faster: the count(), min() and max() functions, and I
know significant progress has been made since I started using
Scott Marlowe wrote:
OTOH, there are some things, like importing data, which are MUCH faster
in pgsql than in the big database.
An excellent point, I forgot about this. The COPY command is the best thing
since the invention of a shirt pocket. We have a database-per-customer design,
and one
On 6/6/07, Craig James [EMAIL PROTECTED] wrote:
They're blowing smoke if they think Oracle can do this.
Oracle could handle this fine.
Oracle fell over dead, even with the best indexing possible,
tuned by the experts, and using partitions keyed to the
customerID.
I don't think so, whoever
On 6/6/07, Craig James [EMAIL PROTECTED] wrote:
Last time I checked, Oracle didn't have anything close to this.
When did you check, 15 years ago? Oracle has direct-path
import/export and data pump; both of which make generic COPY look like
a turtle. The new PostgreSQL bulk-loader takes
On Wed, Jun 06, 2007 at 12:06:09AM +0200, Steinar H. Gunderson wrote:
Wasn't there also talk about adding the ability to mark individual
partitions as read-only, thus bypassing MVCC and allowing queries
to be satisfied using indexes only?
I have a (different) problem that read-only data
Hi there,
We run a small ISP with a FreeBSD/freeradius/postgresql 8.2.4 backend
and 200+ users. Authentication happens via UAM/hotspot and I see a lot
of authorisation and accounting packets that are handled via PL/PGSQL
functions directly in the database.
Everything seems to work 100% except
On Tue, Jun 05, 2007 at 03:31:55PM -0700, [EMAIL PROTECTED] wrote:
various people (not database experts) are pushing to install Oracle
cluster so that they can move all of these to one table with a customerID
column.
Well, you will always have to deal with the sort of people who will
base
On 6/6/07, Andrew Sullivan [EMAIL PROTECTED] wrote:
Well, you will always have to deal with the sort of people who will
base their technical prescriptions on the shiny ads they read in
SuperGlobalNetworkedExecutiveGoFast, or whatever rag they're reading
these days.
Always.
I usually
On Wed, Jun 06, 2007 at 02:01:59PM -0400, Jonah H. Harris wrote:
They did this for the same reason as everyone else. They don't want
non-experts tuning the database incorrectly, writing a benchmark paper
about it, and making the software look bad.
I agree that Oracle is a fine system, and I
On Wed, Jun 06, 2007 at 09:20:54PM +0200, Gunther Mayer wrote:
What the heck could cause such erratic behaviour? I suspect some type of
resource problem but what and how could I dig deeper?
Is something (perhaps implicitly) locking the table? That will cause
this.
A
--
Andrew Sullivan |
On 6/6/07, Andrew Sullivan [EMAIL PROTECTED] wrote:
But I think the above is giving Oracle Corp a little too
much credit.
Perhaps. However, Oracle has a thousand or so knobs which can control
almost every aspect of every subsystem. If you know how they interact
with each other and how to use
Jonah H. Harris wrote:
On 6/6/07, Craig James [EMAIL PROTECTED] wrote:
They're blowing smoke if they think Oracle can do this.
Oracle could handle this fine.
Oracle fell over dead, even with the best indexing possible,
tuned by the experts, and using partitions keyed to the
customerID.
I
On 6/6/07, Craig James [EMAIL PROTECTED] wrote:
You didn't read my message. I said that *BOTH* Oracle
and Postgres performed well with table-per-customer.
Yes, I did. My belief is that Oracle can handle all customers in a
single table.
The technical question is simple: Table-per-customer
Question,
Does (pg_stat_get_db_blocks_fetched(oid)-pg_stat_get_db_blocks_hit(oid)*8) =
number of KB read from disk for the listed database since the last server
startup?
Thanks,
Chris
Gunther Mayer wrote:
Hi there,
We run a small ISP with a FreeBSD/freeradius/postgresql 8.2.4 backend
and 200+ users. Authentication happens via UAM/hotspot and I see a lot
of authorisation and accounting packets that are handled via PL/PGSQL
functions directly in the database.
Everything seems
On Wed, 2007-06-06 at 16:58 -0400, Chris Hoover wrote:
Question,
Does (pg_stat_get_db_blocks_fetched(oid)-pg_stat_get_db_blocks_hit
(oid)*8) = number of KB read from disk for the listed database since
the last server startup?
That will give you the number of blocks requested from the OS.
[EMAIL PROTECTED] wrote:
What is a real life example where an intelligent and researched
database application would issue a like or ilike query as their
primary condition in a situation where they expected very high
selectivity?
In my case the canonical example is to search against textual
Gang,
I'm running a mid-size production 8.0 environment. I'd really like
to upgrade to 8.2, so I've been doing some testing to make sure my
app works well with 8.2, and I ran across this weirdness. I set up
and configured 8.2 in the standard way, MacOSX Tiger, current
patches, download
Kurt Overberg wrote:
Explain Outputs:
-- 8.2
- Bitmap Heap Scan on taskinstance (cost=20.71..2143.26 rows=556
width=8) (actual time=421.423..5655.745 rows=98 loops=9)
Recheck Cond: (taskinstance.taskid = task.id)
- Bitmap Index Scan on taskid_taskinstance_key
On Jun 6, 2007, at 18:27 , Kurt Overberg wrote:
select id from taskinstance where taskid in (select id from task
where campaignid = 75);
Now, I know this could (and should) be rewritten to not use the
WHERE x IN () style, but this is actually a sub-query to a larger
query.
Granted, it
29 matches
Mail list logo
