Re: Help with a seq scan on multi-million row table
От | |
---|---|
Тема | Re: Help with a seq scan on multi-million row table |
Дата | |
Msg-id | 20060511170944.91199.qmail@web50302.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: Help with a seq scan on multi-million row table (Markus Schaber <schabi@logix-tt.com>) |
Ответы |
Re: Help with a seq scan on multi-million row table
(Andrew Sullivan <ajs@crankycanuck.ca>)
|
Список | pgsql-sql |
Hi Markus & Tom, Higher statistics for this column.... hm, I'd love to try changing it to see how that changes things, but I'm afraid I don'tknow how to do that. How can I change the statistics target value for this column? Ah, I think I found the place: => select * from pg_attribute where attname='user_url_id';attrelid | attname | atttypid | attstattarget | attlen | attnum| attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal| attinhcount ----------+-------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+------------- 6124839| user_url_id | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 1646081 | user_url_id | 23 | -1 | 4 | 2 | 0 | -1 | -1 | t | p | i | f | f |f | t | 010048109 | user_url_id | 23 | -1 | 4 | 3 | 0 | -1 | -1 | t | p | i | f | f | f | t | 010048123 | user_url_id | 23 | -1 | 4 | 2 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0 Hm, 4 rows. I need to change the value of the 'attstattarget' column, but for which of these rows? Only attrelid is different. I tried looking at pg_class, but didn't find anything with the above attrelid's. I used: => select * from pg_class where relname like 'user_url%'; Tom: you asked about distinct values. pg_stats shows cca. 60K distinct values, but the real number is: select count(distinct user_url_id) from user_url_tag; count ---------1505933 This number grows daily by... not sure how much, probably 5k a day currently. Thanks, Otis ----- Original Message ---- From: Markus Schaber <schabi@logix-tt.com> To: ogjunk-pgjedan@yahoo.com Cc: pgsql-sql@postgresql.org Sent: Thursday, May 11, 2006 6:33:55 AM Subject: Re: [SQL] Help with a seq scan on multi-million row table Hi, Otis, ogjunk-pgjedan@yahoo.com wrote: > I'm not sure which numbers you are referring to when you said the estimate is off, but here are some numbers: > The whole table has 6-7 M rows. > That query matches about 2500 rows. > > If there are other things I can play with and help narrow this down, please let me know. Did you try to set higher statistics targets for this columns? For experimenting, I'd try to set it to 100 or even higher, then ANALYZE the table, and then retest the query. HTH, Marks -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
В списке pgsql-sql по дате отправления:
Предыдущее
От: Andreas KretschmerДата:
Сообщение: Re: Multi-column index not used, new flipped column index is