On Fri, 2005-08-19 at 16:03 -0500, John A Meinel wrote:
> Jeremiah Jahn wrote:
> > On Fri, 2005-08-19 at 12:18 -0500, John A Meinel wrote:
> >>Jeremiah Jahn wrote:
> >>Well, in general, 3ms for a single lookup seems really long. Maybe your
> >>index is bloated by not vacuuming often enough. Do you tend to get a lot
> >>of updates to litigant_details?
> > I have vacuumed this already. I get lots of updates, but this data is
> > mostly unchanging.
> >>There are a couple possibilities at this point. First, you can REINDEX
> >>the appropriate index, and see if that helps. However, if this is a test
> >>box, it sounds like you just did a dump and reload, which wouldn't have
> >>bloat in an index.
> > I loaded it using slony
> I don't know that slony versus pg_dump/pg_restore really matters. The
> big thing is that Updates wouldn't be trashing your index.
> But if you are saying that you cluster once/wk your index can't be that
> messed up anyway. (Unless CLUSTER messes up the non-clustered indexes,
> but that would make cluster much less useful, so I would have guessed
> this was not the case)
> >>Another possibility. Is this the column that you usually use when
> >>pulling information out of litigant_details? If so, you can CLUSTER
> >>litigant_details on the appropriate index. This will help things be
> >>close together that should be, which decreases the index lookup costs.
> > clustering on this right now. Most of the other things are already
> > clustered. name and case_data
> Just as a reality check, they are clustered on the columns in question,
> right? (I don't know if this column is a primary key or not, but any
> index can be used for clustering).
> >>However, if this is not the common column, then you probably will slow
> >>down whatever other accesses you may have on this table.
> >>After CLUSTER, the current data will stay clustered, but new data will
> >>not, so you have to continually CLUSTER, the same way that you might
> >>VACUUM. *However*, IIRC CLUSTER grabs an Exclusive lock, so it is as
> >>expensive as a VACUUM FULL. Be aware of this, but it might vastly
> >>improve your performance, so it would be worth it.
> > I generally re-cluster once a week.
> >>>>So there is no need for preloading your indexes on the identity table.
> >>>>It is definitely not the bottleneck.
> >>>>So a few design bits, which may help your database.
> >>>>Why is "actor_id" a text field instead of a number?
> >>>This is simply due to the nature of the data.
> >>I'm just wondering if changing into a number, and using a number->name
> >>lookup would be faster for you. It may not be. In general, I prefer to
> >>use numbers for references. I may be over paranoid, but I know that some
> >>locales are bad with string -> string comparisons. And since the data in
> >>your database is stored as UNICODE, I'm not sure if it has to do any
> >>translating or not. Again, something to consider, it may not make any
> > I don't believe so. I initialze the DB as 'lang=C'. I used to have the
> > problem where things were being inited as en_US. this would prevent any
> > text based index from working. This doesn't seem to be the case here, so
> > I'm not worried about it.
> Sorry, I think I was confusing you with someone else who posted SHOW ALL.
> > it's cached alright. I'm getting a read rate of about 150MB/sec. I would
> > have thought is would be faster with my raid setup. I think I'm going to
> > scrap the whole thing and get rid of LVM. I'll just do a straight ext3
> > system. Maybe that will help. Still trying to get suggestions for a
> > stripe size.
> I don't think 150MB/s is out of the realm for a 14 drive array.
> How fast is
> time dd if=/dev/zero of=testfile bs=8192 count=1000000
time dd if=/dev/zero of=testfile bs=8192 count=1000000
1000000+0 records in
1000000+0 records out
> (That should create a 8GB file, which is too big to cache everything)
> And then how fast is:
> time dd if=testfile of=/dev/null bs=8192 count=1000000
time dd if=testfile of=/dev/null bs=8192 count=1000000
1000000+0 records in
1000000+0 records out
and on a second run:
> That should give you a semi-decent way of measuring how fast the RAID
> system is, since it should be too big to cache in ram.
about 150MB/Sec. Is there no better way to make this go faster...?
> >>I can point you to REINDEX and CLUSTER, but if it is caching in ram, I
> >>honestly can't say why the per loop would be that much slower.
> >>Are both systems running the same postgres version? It sounds like it is
> >>different (since you say something about switching to 8.0).
> > These had little or no effect.
> > The production machine is running 7.4 while the devel machine is running
> > 8.0
> Well, my concern is that maybe some portion of the 8.0 code actually
> slowed things down for you. You could try reverting to 7.4 on the devel
> box, though I think playing with upgrading to 8.1 might be more worthwhile.
And the level of stability for 8.1? I started with 7.4 and it didn't
really feel as fast as it should either.
> > this is a cached version.
> I assume that you mean this is the second run of the query. I can't
> compare it too much, since this is "smith" rather than "jones". But this
> one is 17s rather than the other one being 46s.
> And that includes having 8k rows instead of having 5k rows.
> Have you tried other values with disabled nested loops? Because this
> query (at least in cached form) seems to be *way* faster than with
> nested loops.
> I know that you somehow managed to get 200s in your testing, but it
> might just be that whatever needed to be loaded is now loaded, and you
> would get better performance.
> If this is true, it means you might need to tweak some settings, and
> make sure your statistics are decent, so that postgres can actually pick
> the optimal plan.
> >>copa=> explain analyze select
> >>copa-> from identity
> >>copa-> join litigant_details on identity.actor_id =
> >>copa-> join case_data on litigant_details.case_id = case_data.case_id and
> >>litigant_details.court_ori = case_data.court_ori
> >>copa-> join court on identity.court_ori = court.id
> >>copa-> where identity.court_ori = 'IL081025J' and full_name like 'SMITH%'
> >>order by full_name;
> >> QUERY PLAN
> >> Sort (cost=100502560.72..100502583.47 rows=9099 width=86) (actual
> >> time=17843.876..17849.401 rows=8094 loops=1)
> >> Sort Key: identity.full_name
> >> -> Merge Join (cost=100311378.72..100501962.40 rows=9099 width=86)
> >> (actual time=15195.816..17817.847 rows=8094 loops=1)
> >> Merge Cond: ((("outer".court_ori)::text = "inner"."?column10?")
> >> AND (("outer".case_id)::text = "inner"."?column11?"))
> >> -> Index Scan using case_speed on case_data
> >> (cost=0.00..170424.73 rows=3999943 width=26) (actual time=0.015..4540.525
> >> rows=3018284 loops=1)
> >> -> Sort (cost=100311378.72..100311400.82 rows=8839 width=112)
> >> (actual time=9594.985..9601.174 rows=8094 loops=1)
> >> Sort Key: (litigant_details.court_ori)::text,
> >> (litigant_details.case_id)::text
> >> -> Nested Loop (cost=100002491.43..100310799.34 rows=8839
> >> width=112) (actual time=6892.755..9555.828 rows=8094 loops=1)
> >> -> Seq Scan on court (cost=0.00..3.29 rows=1
> >> width=12) (actual time=0.085..0.096 rows=1 loops=1)
> >> Filter: ('IL081025J'::text = (id)::text)
> What I don't really understand is the next part. It seems to be doing an
> index scan on 3.7M rows, and getting very decent performance (5s), and
> then merging against a table which returns only 8k rows.
> Why is it having to look through all of those rows?
> I may be missing something, but this says it is able to do 600 index
> lookups / millisecond. Which seems superfast. (Compared to your earlier
> 4ms / lookup)
Makes me a little confused myself...
> Something fishy is going on here.
> >> -> Merge Join (cost=2491.43..310707.66 rows=8839
> >> width=113) (actual time=6892.656..9519.680 rows=8094 loops=1)
> >> Merge Cond: (("outer".actor_id)::text =
> >> "inner"."?column7?")
> >> -> Index Scan using lit_actor_speed on
> >> litigant_details (cost=0.00..295722.00 rows=4956820 width=81) (actual
> >> time=0.027..5613.814 rows=3736703 loops=1)
> >> -> Sort (cost=2491.43..2513.71 rows=8913
> >> width=82) (actual time=116.071..122.272 rows=8100 loops=1)
> >> Sort Key: (identity.actor_id)::text
> >> -> Index Scan using name_speed on
> >> identity (cost=0.00..1906.66 rows=8913 width=82) (actual
> >> time=0.133..81.104 rows=8100 loops=1)
> >> Index Cond: (((full_name)::text >=
> >> 'SMITH'::character varying) AND ((full_name)::text < 'SMITI'::character
> >> varying))
> >> Filter: (((court_ori)::text =
> >> 'IL081025J'::text) AND ((full_name)::text ~~ 'SMITH%'::text))
> >> Total runtime: 17859.917 ms
> >>But really, you have worse index speed, and that needs to be figured out.
> I'm assuming your data is private (since it looks like legal stuff).
> Unless maybe that makes it part of the public record.
> Anyway, I'm not able to, but sometimes someone like Tom can profile
> stuff to see what is going on.
I've had tom on here before..:) not my devel box, but my production box
a couple of years ago.
> I might just be messing up my ability to read the explain output. But
> somehow things don't seem to be lining up with the cost of a single
> index lookup.
> On my crappy Celeron 450 box, an index lookup is 0.06ms once things are
> cached in ram.
Speak softly and carry a +6 two-handed sword.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster