Re: trouble with (lack of) indexing
От | Søren Boll Overgaard |
---|---|
Тема | Re: trouble with (lack of) indexing |
Дата | |
Msg-id | 20020509235919.GG20986@treebeard.tolkien.dk обсуждение исходный текст |
Ответ на | Re: trouble with (lack of) indexing (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: trouble with (lack of) indexing
("Nigel J. Andrews" <nandrews@investsystems.co.uk>)
|
Список | pgsql-general |
On Thu, May 09, 2002 at 07:38:06PM -0400, Tom Lane wrote: > =?iso-8859-1?Q?S=F8ren?= Boll Overgaard <postgres@fork.dk> writes: > > explain SELECT ht.id,hq.ip,hq.id FROM > > hostsqueue as hq,hoststests as ht WHERE ht.hostsqueue_id=hq.id; > > NOTICE: QUERY PLAN: > > > Hash Join (cost=776.05..2904.36 rows=84 width=24) > > -> Seq Scan on hoststests ht (cost=0.00..2126.84 rows=84 width=8) > > -> Hash (cost=776.04..776.04 rows=4 width=16) > > -> Seq Scan on hostsqueue hq (cost=0.00..776.04 rows=4 width=16) > > > If there's only 84 rows it's hardly going to matter which plan we choose > ;-). Please show us the results from the production database, not the > toy-sized tables. > > If you have 7.2 then EXPLAIN ANALYZE is much more useful to show than > plain EXPLAIN. Also, you could try doing "SET enable_seqscan = off" > and see how the plan changes. I missed the outputs of the explain analyze queries in the previous mail: explain analyze SELECT ht.id,hq.ip,hq.id FROM hostsqueue as hq,hoststests as ht WHERE ht.hostsqueue_id=hq.id; NOTICE: QUERY PLAN: Hash Join (cost=75331.89..127823.94 rows=160293 width=24) (actual time=122572.70..146683.59 rows=142807 loops=1) -> Seq Scan on hoststests ht (cost=0.00..49686.93 rows=160293 width=8) (actual time=170.79..21255.68 rows=142807 loops=1) -> Hash (cost=75243.51..75243.51 rows=35351 width=16) (actual time=122401.10..122401.10 rows=0 loops=1) -> Seq Scan on hostsqueue hq (cost=0.00..75243.51 rows=35351 width=16) (actual time=518.88..122024.50 rows=28610loops=1) Total runtime: 147598.79 msec EXPLAIN And after setting the enable_seqscan to off, I got this: => SET enable_seqscan = off; SET VARIABLE => explain analyze SELECT ht.id,hq.ip,hq.id FROM hostsqueue as hq,hoststests as ht WHERE ht.hostsqueue_id=hq.id; NOTICE: QUERY PLAN: Merge Join (cost=0.00..772324.63 rows=160293 width=24) (actual time=8319.78..116061.18 rows=141645 loops=1) -> Index Scan using hostsqueue_pkey on hostsqueue hq (cost=0.00..137603.50 rows=35351 width=16) (actual time=34.90..90831.97rows=28623 loops=1) -> Index Scan using idx_htsts_hq on hoststests ht (cost=0.00..632228.35 rows=160293 width=8) (actual time=18.41..23343.75rows=141645 loops=1) Total runtime: 116562.03 msec EXPLAIN Actually, I was rather hoping for a large gain in performance, but maybe the "total runtimes" are not actally representative of performance of the actual selects? -- Søren O. ,''`. : :' : public key: finger boll <at> db.debian.org `. `' `-
В списке pgsql-general по дате отправления: