Re: Help with a seq scan on multi-million row table

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't know 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 | 0
10048109 | user_url_id | 23 | -1 | 4 | 3 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0
10048123 | 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(at)logix-tt(dot)com>
To: ogjunk-pgjedan(at)yahoo(dot)com
Cc: pgsql-sql(at)postgresql(dot)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(at)yahoo(dot)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