pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Join slow on "large" tables
9+ messages / 4 participants
[nested] [flat]

* Join slow on "large" tables
@ 2004-06-07 22:19 Josué Maldonado <josue@lamundial.hn>
  2004-06-07 22:31 ` Re: Join slow on "large" tables Josh Berkus <josh@agliodbs.com>
  2004-06-07 22:47 ` Re: Join slow on "large" tables Scott Marlowe <smarlowe@qwest.net>
  0 siblings, 2 replies; 9+ messages in thread

From: Josué Maldonado @ 2004-06-07 22:19 UTC (permalink / raw)
  To: pgsql-performance

Hello list,

Server is dual Xeon 2.4, 2GBRAM, Postgresql is running on partition:
/dev/sda9              29G  8.9G   20G  31% /home2
/dev/sda9 on /home2 type jfs (rw)

Version()
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 
20020903 (Red Hat Linux 8.0 3.2-7)

I have a view to join two tables inventory details (pkardex) and 
inventory documents header (pmdoc) this view usually runs pretty slow as 
indicated in the explain analyze, pkardex is 1943465 rows and its size 
aprox 659MB, pmdoc is 1183520 rows and its size is aprox 314MB. The view 
definition is:

SELECT pkd_pk AS kpk, (pkd_stamp)::date AS kfecha, pkd_docto AS kdocto,
     ((((pdc_custid)::text || ' '::text) || 
(pdc_custname)::text))::character
     varying(50) AS kclpv, pkd_saldo AS ksaldo, pkd_es AS kes, CASE WHEN 
(pkd_es
     = 'E'::bpchar) THEN pkd_qtyinv ELSE (0)::numeric END AS kentrada, 
CASE WHEN
     (pkd_es = 'S'::bpchar) THEN pkd_qtyinv ELSE (0)::numeric END AS 
ksalida,
     pkd_pcode AS kprocode, pkd_price AS kvalor, pdc_tipdoc AS ktipdoc
FROM (pkardex JOIN pmdoc ON ((pmdoc.pdc_pk = pkardex.doctofk)));


Shared memory is:
/root: cat /proc/sys/kernel/shmmax
1073741824

and postgresql.conf have this settings:
tcpip_socket = true
sort_mem = 8190         # min 64, size in KB
vacuum_mem = 262144             # min 1024, size in KB
checkpoint_segments = 10
max_connections = 256
shared_buffers = 32000
effective_cache_size = 160000   # typically 8KB each
random_page_cost = 2            # units are one sequ

The explain analyze is:
dbmund=# explain analyze select * from vkardex where kprocode='1017';
  Nested Loop  (cost=0.00..32155.66 rows=5831 width=114) (actual 
time=18.223..47983.157 rows=4553 loops=1)
    ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..11292.52 
rows=5831 width=72) (actual time=18.152..39520.406 rows=5049 loops=1)
          Index Cond: ((pkd_pcode)::text = '1017'::text)
    ->  Index Scan using pdc_pk_idx on pmdoc  (cost=0.00..3.55 rows=1 
width=50) (actual time=1.659..1.661 rows=1 loops=5049)
          Index Cond: (pmdoc.pdc_pk = "outer".doctofk)
  Total runtime: 47988.067 ms
(6 rows)

Does anyone can help me how to properly tune postgresql to gain some 
speed in such queries, some people have mentioned a RAM increase is 
necesary, about 8GB or more to have postgresql to run  smooth, any 
comment or suggestion. I really appreciate any help.

Regards,


-- 
Sinceramente,
Josué Maldonado.
"Que se me den seis líneas escritas de puño y letra del hombre más 
honrado del mundo, y hallaré en ellas motivos para hacerle ahorcar." 
--cardenal Richelieu (Cardenal y político francés. 1.585 - 1.642)



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Join slow on "large" tables
  2004-06-07 22:19 Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
@ 2004-06-07 22:31 ` Josh Berkus <josh@agliodbs.com>
  2004-06-07 23:12   ` Re: Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  1 sibling, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2004-06-07 22:31 UTC (permalink / raw)
  To: Josué Maldonado <josue@lamundial.hn>; pgsql-performance

Josue'

>     ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..11292.52 
> rows=5831 width=72) (actual time=18.152..39520.406 rows=5049 loops=1)

Looks to me like there's a problem with index bloat on pkd_pcode_idx.   Try 
REINDEXing it, and if that doesn't help, VACUUM FULL on pkardex.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Join slow on "large" tables
  2004-06-07 22:19 Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  2004-06-07 22:31 ` Re: Join slow on "large" tables Josh Berkus <josh@agliodbs.com>
@ 2004-06-07 23:12   ` Josué Maldonado <josue@lamundial.hn>
  2004-06-07 23:21     ` Re: Join slow on "large" tables Josh Berkus <josh@agliodbs.com>
  2004-06-08 05:18     ` Re: Join slow on "large" tables Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 9+ messages in thread

From: Josué Maldonado @ 2004-06-07 23:12 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Hi Josh and thanks for your response,

El 07/06/2004 4:31 PM, Josh Berkus en su mensaje escribio:

> Josue'
> 
> 
>>    ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..11292.52 
>>rows=5831 width=72) (actual time=18.152..39520.406 rows=5049 loops=1)
> 
> 
> Looks to me like there's a problem with index bloat on pkd_pcode_idx.   Try 
> REINDEXing it, and if that doesn't help, VACUUM FULL on pkardex.
> 

Recreated the index (drop then create) and did the vacuum full pkardex 
and the behavior seems to be the same:

dbmund=# explain analyze select * from vkardex where kprocode='1013';
  Nested Loop  (cost=0.00..2248.19 rows=403 width=114) (actual 
time=846.318..16030.633 rows=3145 loops=1)
    ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..806.27 
rows=403 width=72) (actual time=0.054..87.393 rows=3544 loops=1)
          Index Cond: ((pkd_pcode)::text = '1013'::text)
    ->  Index Scan using pdc_pk_idx on pmdoc  (cost=0.00..3.55 rows=1 
width=50) (actual time=4.482..4.484 rows=1 loops=3544)
          Index Cond: (pmdoc.pdc_pk = "outer".doctofk)
  Total runtime: 16033.807 ms
(6 rows)

At the time the querie was running top returned:

5:11pm  up  1:28,  3 users,  load average: 0.19, 0.97, 1.41
69 processes: 66 sleeping, 1 running, 2 zombie, 0 stopped
CPU0 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
CPU1 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
CPU2 states:  0.1% user,  0.4% system,  0.0% nice, 98.4% idle
CPU3 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
Mem:  2069596K av, 1477784K used,  591812K free,       0K shrd,    2336K 
buff
Swap: 2096440K av,    9028K used, 2087412K free                 1388372K 
cached

   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
  1225 postgres  17   0  257M 257M  255M S     0.6 12.7   7:14 postmaster
  1978 postgres  11   0  1044 1044   860 R     0.2  0.0   0:00 top
     1 root       9   0   472  444   428 S     0.0  0.0   0:04 init
     2 root       8   0     0    0     0 SW    0.0  0.0   0:00 keventd

and free returned:
/root: free
              total       used       free     shared    buffers     cached
Mem:       2069596    1477832     591764          0       2320    1388372
-/+ buffers/cache:      87140    1982456
Swap:      2096440       9028    2087412

I'm not a Linux guru, it looks like a memory leak.


-- 
Sinceramente,

Josué Maldonado.
"Las palabras de aliento después de la censura son como el sol tras el 
aguacero."



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Join slow on "large" tables
  2004-06-07 22:19 Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  2004-06-07 22:31 ` Re: Join slow on "large" tables Josh Berkus <josh@agliodbs.com>
  2004-06-07 23:12   ` Re: Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
@ 2004-06-07 23:21     ` Josh Berkus <josh@agliodbs.com>
  2004-06-07 23:30       ` Re: Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  1 sibling, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2004-06-07 23:21 UTC (permalink / raw)
  To: Josué Maldonado <josue@lamundial.hn>; +Cc: pgsql-performance

Josue'

> dbmund=# explain analyze select * from vkardex where kprocode='1013';
>   Nested Loop  (cost=0.00..2248.19 rows=403 width=114) (actual 
> time=846.318..16030.633 rows=3145 loops=1)
>     ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..806.27 
> rows=403 width=72) (actual time=0.054..87.393 rows=3544 loops=1)
>           Index Cond: ((pkd_pcode)::text = '1013'::text)
>     ->  Index Scan using pdc_pk_idx on pmdoc  (cost=0.00..3.55 rows=1 
> width=50) (actual time=4.482..4.484 rows=1 loops=3544)
>           Index Cond: (pmdoc.pdc_pk = "outer".doctofk)
>   Total runtime: 16033.807 ms

Huh?   It is not at all the same.   Your index scan is down to 87ms from 
27,000!   And the total query is down to 16seconds from 47 seconds.   Don't 
you consider that an improvement?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Join slow on "large" tables
  2004-06-07 22:19 Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  2004-06-07 22:31 ` Re: Join slow on "large" tables Josh Berkus <josh@agliodbs.com>
  2004-06-07 23:12   ` Re: Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  2004-06-07 23:21     ` Re: Join slow on "large" tables Josh Berkus <josh@agliodbs.com>
@ 2004-06-07 23:30       ` Josué Maldonado <josue@lamundial.hn>
  0 siblings, 0 replies; 9+ messages in thread

From: Josué Maldonado @ 2004-06-07 23:30 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh,

El 07/06/2004 5:21 PM, Josh Berkus en su mensaje escribio:
> 
> Huh?   It is not at all the same.   Your index scan is down to 87ms from 
> 27,000!   And the total query is down to 16seconds from 47 seconds.   Don't 
> you consider that an improvement?

Yes there was an improvement with respect the previus query, but still 
16 seconds is too slow for that query. And usually the query takes more 
than 10 seconds even with small data sets returned.

Thanks,


-- 
Sinceramente,
Josué Maldonado.

"La cultura es capaz de descifrar los enigmas en que nos envuelve la vida."



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Join slow on "large" tables
  2004-06-07 22:19 Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  2004-06-07 22:31 ` Re: Join slow on "large" tables Josh Berkus <josh@agliodbs.com>
  2004-06-07 23:12   ` Re: Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
@ 2004-06-08 05:18     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 9+ messages in thread

From: Tom Lane @ 2004-06-08 05:18 UTC (permalink / raw)
  To: Josué Maldonado <josue@lamundial.hn>; +Cc: josh@agliodbs.com; pgsql-performance

=?ISO-8859-1?Q?Josu=E9_Maldonado?= <josue@lamundial.hn> writes:
> Recreated the index (drop then create) and did the vacuum full pkardex 
> and the behavior seems to be the same:

Well, there was a pretty huge improvement in the pkardex scan time,
whether you noticed it or not: 39520.406 to 87.393 msec.  This
definitely suggests that you've been lax about vacuuming this table.

I'm wondering whether pmdoc might not be overdue for vacuuming as
well.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Join slow on "large" tables
  2004-06-07 22:19 Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
@ 2004-06-07 22:47 ` Scott Marlowe <smarlowe@qwest.net>
  2004-06-08 14:36   ` Re: Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  1 sibling, 1 reply; 9+ messages in thread

From: Scott Marlowe @ 2004-06-07 22:47 UTC (permalink / raw)
  To: Josué Maldonado <josue@lamundial.hn>; +Cc: pgsql-performance

On Mon, 2004-06-07 at 16:19, Josué Maldonado wrote:
> Hello list,
> 
> Server is dual Xeon 2.4, 2GBRAM, Postgresql is running on partition:
> /dev/sda9              29G  8.9G   20G  31% /home2
> /dev/sda9 on /home2 type jfs (rw)
> 
> Version()
> PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 
> 20020903 (Red Hat Linux 8.0 3.2-7)
> 
> I have a view to join two tables inventory details (pkardex) and 
> inventory documents header (pmdoc) this view usually runs pretty slow as 
> indicated in the explain analyze, pkardex is 1943465 rows and its size 
> aprox 659MB, pmdoc is 1183520 rows and its size is aprox 314MB. The view 
> definition is:
> 
> SELECT pkd_pk AS kpk, (pkd_stamp)::date AS kfecha, pkd_docto AS kdocto,
>      ((((pdc_custid)::text || ' '::text) || 
> (pdc_custname)::text))::character
>      varying(50) AS kclpv, pkd_saldo AS ksaldo, pkd_es AS kes, CASE WHEN 
> (pkd_es
>      = 'E'::bpchar) THEN pkd_qtyinv ELSE (0)::numeric END AS kentrada, 
> CASE WHEN
>      (pkd_es = 'S'::bpchar) THEN pkd_qtyinv ELSE (0)::numeric END AS 
> ksalida,
>      pkd_pcode AS kprocode, pkd_price AS kvalor, pdc_tipdoc AS ktipdoc
> FROM (pkardex JOIN pmdoc ON ((pmdoc.pdc_pk = pkardex.doctofk)));
> 
> 
> Shared memory is:
> /root: cat /proc/sys/kernel/shmmax
> 1073741824
> 
> and postgresql.conf have this settings:
> tcpip_socket = true
> sort_mem = 8190         # min 64, size in KB
> vacuum_mem = 262144             # min 1024, size in KB
> checkpoint_segments = 10
> max_connections = 256
> shared_buffers = 32000
> effective_cache_size = 160000   # typically 8KB each
> random_page_cost = 2            # units are one sequ
> 
> The explain analyze is:
> dbmund=# explain analyze select * from vkardex where kprocode='1017';
>   Nested Loop  (cost=0.00..32155.66 rows=5831 width=114) (actual 
> time=18.223..47983.157 rows=4553 loops=1)
>     ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..11292.52 
> rows=5831 width=72) (actual time=18.152..39520.406 rows=5049 loops=1)
>           Index Cond: ((pkd_pcode)::text = '1017'::text)
>     ->  Index Scan using pdc_pk_idx on pmdoc  (cost=0.00..3.55 rows=1 
> width=50) (actual time=1.659..1.661 rows=1 loops=5049)
>           Index Cond: (pmdoc.pdc_pk = "outer".doctofk)
>   Total runtime: 47988.067 ms
> (6 rows)

OK, you have to ask yourself a question here.  Do I have enough memory
to let both postgresql and the kernel to cache this data, or enough
memory for only one.  Then, you pick one and try it out.  But there's
some issues here.  PostgreSQL's shared buffer are not, and should not
generally be thought of as "cache".  A cache's job it to hold the whole
working set, or as much as possible, ready for access.  A buffer's job
is to hold all the data we're tossing around right this second.  Once
we're done with the data, the buffers can and do just drop whatever was
in them.  PostgreSQL does not have caching, in the classical sense. 
that may or may not change.

The kernel, on the other hand, has both cache and buffer.  Ever notice
that a Linux top shows the cache usually being much bigger than the
buffers?  My 512 Meg home box right now has 252968k for cache, and
43276k for buffers.  

Now, you're tossing around enough data to actually maybe have a use for
a huge set of buffers, but this means you'll need to starve your cache
to get enough buffers.  Which means that if one process does this kind
of join, drops connection, and two seconds later, another process
connects and does nearly the same thing, it's likely to have to read it
all from the hard drives again, as it's not in the postgresql buffer,
and not in the kernel cache.

Starting a seperate connection, doing a simple select * from table1;
sekect * from table 2, dropping the result set returned, and staying
connected seems to be enough to get 7.4 to hold onto the data.

PostgreSQL's current buffer management algo is dirt simple.  The ones in
the kernel's cache are quite good.  So you can quickly reach a point
where PostgreSQL is chasing it's tail where the kernel would have done
OK.

Your numbers show that you are tossing 659M and 314M against each other,
but I don't know if you're harvesting the whole set at once, or just a
couple row of each.  Indexing help, or is this always gonna be a big seq
scan of 90% of both tables?

If you are getting the whole thing all the time, and want postgresql to
buffer the whole thing (I recommend against it, although a very few
circumstances seem to support it) you need to have 973M of buffer.  That
would be 124544 or we'll just call it 130000.  This high of a number
means you will be getting more than 50% of the RAM for postgreSQL.  At
that point, it seems you might as well go for broke and grab most of it,
~200000 or so.

If you're not always mushing the two things against each other, and
you've got other datasets to interact with, index it.

Oh, in your reply you might to include an explain analyze of the query,
and maybe an output of top while the query is running.




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Join slow on "large" tables
  2004-06-07 22:19 Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  2004-06-07 22:47 ` Re: Join slow on "large" tables Scott Marlowe <smarlowe@qwest.net>
@ 2004-06-08 14:36   ` Josué Maldonado <josue@lamundial.hn>
  2004-06-08 15:27     ` Re: Join slow on "large" tables Scott Marlowe <smarlowe@qwest.net>
  0 siblings, 1 reply; 9+ messages in thread

From: Josué Maldonado @ 2004-06-08 14:36 UTC (permalink / raw)
  To: Scott Marlowe <smarlowe@qwest.net>; +Cc: pgsql-performance

Hello Scott,

El 07/06/2004 4:47 PM, Scott Marlowe en su mensaje escribio:

> OK, you have to ask yourself a question here.  Do I have enough memory
> to let both postgresql and the kernel to cache this data, or enough
> memory for only one.  Then, you pick one and try it out.  But there's
> some issues here.  PostgreSQL's shared buffer are not, and should not
> generally be thought of as "cache".  A cache's job it to hold the whole
> working set, or as much as possible, ready for access.  A buffer's job
> is to hold all the data we're tossing around right this second.  Once
> we're done with the data, the buffers can and do just drop whatever was
> in them.  PostgreSQL does not have caching, in the classical sense. 
> that may or may not change.
> 
> The kernel, on the other hand, has both cache and buffer.  Ever notice
> that a Linux top shows the cache usually being much bigger than the
> buffers?  My 512 Meg home box right now has 252968k for cache, and
> 43276k for buffers.  

I noticed buffers are lower agains cache at least as top shows, dunno if 
I'm wrong:

   8:28am  up  1:00,  2 users,  load average: 0.40, 0.97, 0.75
65 processes: 64 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
CPU1 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
CPU2 states:  0.0% user,  0.1% system,  0.0% nice, 99.4% idle
CPU3 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
Mem:  2069596K av, 1882228K used,  187368K free,       0K shrd,   32924K 
buff
Swap: 2096440K av,       0K used, 2096440K free                 1757220K 
cached

   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
  1508 root      13   0  1040 1040   856 R     0.1  0.0   0:00 top
     1 root       8   0   476  476   432 S     0.0  0.0   0:04 init



> Now, you're tossing around enough data to actually maybe have a use for
> a huge set of buffers, but this means you'll need to starve your cache
> to get enough buffers.  Which means that if one process does this kind
> of join, drops connection, and two seconds later, another process
> connects and does nearly the same thing, it's likely to have to read it
> all from the hard drives again, as it's not in the postgresql buffer,
> and not in the kernel cache.
> 
> Starting a seperate connection, doing a simple select * from table1;
> sekect * from table 2, dropping the result set returned, and staying
> connected seems to be enough to get 7.4 to hold onto the data.
> 
> PostgreSQL's current buffer management algo is dirt simple.  The ones in
> the kernel's cache are quite good.  So you can quickly reach a point
> where PostgreSQL is chasing it's tail where the kernel would have done
> OK.
> 
> Your numbers show that you are tossing 659M and 314M against each other,
> but I don't know if you're harvesting the whole set at once, or just a
> couple row of each.  Indexing help, or is this always gonna be a big seq
> scan of 90% of both tables?

Generally only a small set is queried, the bigest record set expected is 
about 24,000 rows and does not exced the 10MB size, explain analyze 
shows the planner is using the index as expected but performance still poor.

> If you are getting the whole thing all the time, and want postgresql to
> buffer the whole thing (I recommend against it, although a very few
> circumstances seem to support it) you need to have 973M of buffer.  That
> would be 124544 or we'll just call it 130000.  This high of a number
> means you will be getting more than 50% of the RAM for postgreSQL.  At
> that point, it seems you might as well go for broke and grab most of it,
> ~200000 or so.
> 
> If you're not always mushing the two things against each other, and
> you've got other datasets to interact with, index it.
> 
> Oh, in your reply you might to include an explain analyze of the query,
> and maybe an output of top while the query is running.
> 

dbmund=# explain analyze select * from vkardex where kprocode='1013';
  Nested Loop  (cost=0.00..2248.19 rows=403 width=114) (actual 
time=846.318..16030.633 rows=3145 loops=1)
    ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..806.27 
rows=403 width=72) (actual time=0.054..87.393 rows=3544 loops=1)
          Index Cond: ((pkd_pcode)::text = '1013'::text)
    ->  Index Scan using pdc_pk_idx on pmdoc  (cost=0.00..3.55 rows=1 
width=50) (actual time=4.482..4.484 rows=1 loops=3544)
          Index Cond: (pmdoc.pdc_pk = "outer".doctofk)
  Total runtime: 16033.807 ms
(6 rows)

At the time the querie was running top returned:
5:11pm  up  1:28,  3 users,  load average: 0.19, 0.97, 1.41
69 processes: 66 sleeping, 1 running, 2 zombie, 0 stopped
CPU0 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
CPU1 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
CPU2 states:  0.1% user,  0.4% system,  0.0% nice, 98.4% idle
CPU3 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
Mem:  2069596K av, 1477784K used,  591812K free,       0K shrd,    2336K 
buff
Swap: 2096440K av,    9028K used, 2087412K free                 1388372K 
cached

   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
  1225 postgres  17   0  257M 257M  255M S     0.6 12.7   7:14 postmaster
  1978 postgres  11   0  1044 1044   860 R     0.2  0.0   0:00 top
     1 root       9   0   472  444   428 S     0.0  0.0   0:04 init
     2 root       8   0     0    0     0 SW    0.0  0.0   0:00 keventd

and free returned:
/root: free
              total       used       free     shared    buffers     cached
Mem:       2069596    1477832     591764          0       2320    1388372
-/+ buffers/cache:      87140    1982456
Swap:      2096440       9028    2087412


Thanks,


-- 
Sinceramente,
Josué Maldonado.
"El verdadero placer está en la búsqueda, más que en la explicación." -- 
Isaac Asimov



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Join slow on "large" tables
  2004-06-07 22:19 Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
  2004-06-07 22:47 ` Re: Join slow on "large" tables Scott Marlowe <smarlowe@qwest.net>
  2004-06-08 14:36   ` Re: Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
@ 2004-06-08 15:27     ` Scott Marlowe <smarlowe@qwest.net>
  0 siblings, 0 replies; 9+ messages in thread

From: Scott Marlowe @ 2004-06-08 15:27 UTC (permalink / raw)
  To: Josué Maldonado <josue@lamundial.hn>; +Cc: pgsql-performance

On Tue, 2004-06-08 at 08:36, Josué Maldonado wrote:
> Hello Scott,

SNIP...

> > Your numbers show that you are tossing 659M and 314M against each other,
> > but I don't know if you're harvesting the whole set at once, or just a
> > couple row of each.  Indexing help, or is this always gonna be a big seq
> > scan of 90% of both tables?
> 
> Generally only a small set is queried, the bigest record set expected is 
> about 24,000 rows and does not exced the 10MB size, explain analyze 
> shows the planner is using the index as expected but performance still poor.

If that is the case, then shared_buffers should likely only be 1000 to
10000.  anything over 10000 is usually a bad idea, unless you've proven
it to be faster than <10000.

> dbmund=# explain analyze select * from vkardex where kprocode='1013';
>   Nested Loop  (cost=0.00..2248.19 rows=403 width=114) (actual 
> time=846.318..16030.633 rows=3145 loops=1)
>     ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..806.27 
> rows=403 width=72) (actual time=0.054..87.393 rows=3544 loops=1)
>           Index Cond: ((pkd_pcode)::text = '1013'::text)
>     ->  Index Scan using pdc_pk_idx on pmdoc  (cost=0.00..3.55 rows=1 
> width=50) (actual time=4.482..4.484 rows=1 loops=3544)
>           Index Cond: (pmdoc.pdc_pk = "outer".doctofk)
>   Total runtime: 16033.807 ms
> (6 rows)

Well, it looks like your predicted versus actual rows are a bit off, and
in the final bit, the planner things that it is going to be merging 403
rows but is in fact merging 3145 rows.  Try 

set enable_nestloop = off;
and run the explain analyze again and see if that's faster.  If so, try
upping your target stats on kprocode (see "\h alter table" in psql for
the syntax), rerun analyze, and try the query with set enable_nestloop =
on to see if the planner makes the right choice.






^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2004-06-08 15:27 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-07 22:19 Join slow on "large" tables Josué Maldonado <josue@lamundial.hn>
2004-06-07 22:31 ` Josh Berkus <josh@agliodbs.com>
2004-06-07 23:12   ` Josué Maldonado <josue@lamundial.hn>
2004-06-07 23:21     ` Josh Berkus <josh@agliodbs.com>
2004-06-07 23:30       ` Josué Maldonado <josue@lamundial.hn>
2004-06-08 05:18     ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-07 22:47 ` Scott Marlowe <smarlowe@qwest.net>
2004-06-08 14:36   ` Josué Maldonado <josue@lamundial.hn>
2004-06-08 15:27     ` Scott Marlowe <smarlowe@qwest.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox