Re: [PERFORM] index not used

If id is PK, the query shoudl return 1 row only... --- Enrico Weigelt <[EMAIL PROTECTED]> wrote: > > Hi folks, > > > I'm doing a simple lookup in a small table by an > unique id, and I'm > wondering, why explains tells me seqscan is used > instead the key. > > The table looks like: > > id

[PERFORM] sunquery and estimated rows

Hi, When I included a subquery, the estimated rows (1240) is way too high as shown in the following example. Can someone explain why? Because of this behavior, some of our queries use hash join instead of nested loop. Thanks, select version(); version

Re: [PERFORM] sunquery and estimated rows

Well, the example shown is simplified version. Now, let's see a little 'real' example (still simplified version): Table test is same as before: \d test Table "public.test" Column | Type | Modifiers -+--+--- id | inte

Re: [PERFORM] pg_stat

nship between "number of index scans done" and "Number of disk block fetch requests for index" as shown in the query. Thank you! --- Litao Wu <[EMAIL PROTECTED]> wrote: > Hi, > > I have query: > select pg_stat_get_numscans(76529669), > pg_stat_get_block

[PERFORM] pg_stat

Hi, I have query: select pg_stat_get_numscans(76529669), pg_stat_get_blocks_fetched(76529669), pg_stat_get_blocks_hit(76529669); The result is: pg_stat_get_numscans | pg_stat_get_blocks_fetched | pg_stat_get_blocks_hit --++

[PERFORM] where to find out when a table was last analyzed?

All, Does PG store when a table was last analyzed? Thanks, __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ ---(end of broadcast)--- TIP 6

[PERFORM] index's relpages after table analyzed

Hi, After a table analyzed a table, the table's relpages of pg_class gets updated, but not those of associated indexes, which can be updated by "vacuum analyze". Is this a feature or a bug? I have some tables and there are almost only inserts. So I do not care about the "dead tuples", but do

Re: [PERFORM] index's relpages after table analyzed

>From PG http://developer.postgresql.org/docs/postgres/diskusage.html: "(Remember, relpages is only updated by VACUUM and ANALYZE.)" --- Litao Wu <[EMAIL PROTECTED]> wrote: > Hi, > > After a table analyzed a table, the table's relpages > > of

Re: [PERFORM] index's relpages after table analyzed

Hi Josh, I know that and that is what I am using now. The problem is I also need to know the relpages each indexe takes and "analyze" seems not update relpages though vacuum and vacuum analyze do. According to PG doc: "Remember, relpages is only updated by VACUUM and ANALYZE" My question is wh

[PERFORM] reindex and copy - deadlock?

Hi, We often experience with the problem that reindex cannot be finished in our production database. It's typically done with 30 minutes. However, sometimes, when there is another "COPY" process, reindex will not finish. By monitoring the CPU time reindex takes, it does not increase at all. Tha

Re: [PERFORM] reindex and copy - deadlock?

access email table because of this. I will use gdb next time. What's this right way to get info as postgres owner? gdb attach pid Thanks again! --- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > We often experience with the problem t

Re: [PERFORM] reindex and copy - deadlock?

0x08100ca1 in PostmasterMain () #21 0x08100862 in PostmasterMain () #22 0x080deed7 in main () #23 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6 (gdb) quit The program is running. Quit anyway (and detach it)? (y or n) y Detaching from program: /bin/postgres, process 18903 --- Tom Lane &l

Re: [PERFORM] reindex and copy - deadlock?

TED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > (gdb) bt > > #0 0x420e8bb2 in semop () from > /lib/i686/libc.so.6 > > #1 0x080ffa28 in PGSemaphoreLock () > > #2 0x08116432 in LWLockAcquire () > > #3 0x0810f572 in LockBuffer () > >

Re: [PERFORM] reindex and copy - deadlock?

if "drop index my_index;" is killed, then how drop index (which is DDL, right?) can be blocked? There must be other process(es) has/have execlusive lock on my_index, which is not our case from pg_locks. Tom, we are in the process of installing the backend with --enable-debug. Thanks,

Re: [PERFORM] reindex and copy - deadlock?

__libc_start_main () from /lib/i686/libc.so.6 (gdb) quit The program is running. Quit anyway (and detach it)? (y or n) y Detaching from program: /xxx/bin.Linux/postgres, process 23663 --- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > One d

Re: [PERFORM] reindex and copy - deadlock?

2 ? S 03:35 15:03 postgres: postgres db1 [local] REINDEX Here are queries from database: 23127 | REINDEX table email 3149 | COPY email (...) FROM stdin --- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > It happened again. > >

Re: [PERFORM] reindex and copy - deadlock?

Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > Our PG version is 7.3.2. > > Hmm. On general principles you should be using > 7.3.6, but I do not see > anything in the 7.3.* change logs that looks very > likely to cure this. >

[PERFORM] network address query

Hi, I have query: explain SELECT * FROM ip_tracking T, ip_map C WHERE T.source_ip::inet >>= C.net; QUERY PLAN --- Nested Loop (cost=0.00..3894833367750.16 rows=517092

[PERFORM] vacuum_mem

Hi, I tested vacuum_mem setting under a 4CPU and 4G RAM machine. I am the only person on that machine. The table: tablename | size_kb | reltuples ---+- big_t | 2048392 | 7.51515e+06 Case 1: 1. vacuum full

[PERFORM] same plan, different time

Hi, I have a query, which runs fast for one id (query 1) and slow for other id (query 2) though both plans and cost are same except these two qeries return different number of rows. explain analyze SELECT * FROM user U LEFT JOIN user_timestamps T USING (user_id), user_alias A WHERE U.user_id = A.

Re: [PERFORM] Timestamp-based indexing

Hi, How about changing: CURRENT_TIMESTAMP - INTERVAL '10 minutes' to 'now'::timestamptz - INTERVAL '10 minutes' It seems to me that Postgres will treat it as a constant. Thanks, --- Tom Lane <[EMAIL PROTECTED]> wrote: > "Matthew T. O'Connor" <[EMAIL PROTECTED]> writes: > > VACUUM FULL ANALYZE

[PERFORM] insert waits for delete with trigger

Hi all, We have table q_20040805 and a delete trigger on it. The delete trigger is: update table q_summary set count=count-1... When we delete from q_20040805, we also insert into related info q_process within the same transaction. There is a PK on q_process, but no trigger on it. No FK on eith

Re: [PERFORM] insert waits for delete with trigger

. Thanks, --- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > Here is info from pg_lock: > > All those locks are already granted, so they are not > much help in > understanding what PID 18951 is wait

Re: [PERFORM] insert waits for delete with trigger

040805 | 19027 | AccessShareLock | t | INSERT INTO q_process (...) SELECT ... FROM q_20040805 WHERE domain_id='2005761066' AND module='spam' q_did_mod_dir_20040805_idx | 19027 | AccessShareLock | t | INSERT INTO q_process (...) SELECT ... FROM q_2004

Re: [PERFORM] Slow select, insert, update

Does that mean reindex is not needed for PG version 7.4? In what kind situations under PG 7.4, reindex is worthwhile? Thanks, Here is doc from 7.3: PostgreSQL is unable to reuse B-tree index pages in certain cases. The problem is that if indexed rows are deleted, those index pages can only be

[PERFORM] Why so much time difference with a same query/plan?

Merry Xmas! I have a query. It sometimes runs OK and sometimes horrible. Here is result from explain analyze: explain analyze SELECT module, sum(c1) + sum(c2) + sum(c3) + sum(c4) + sum(c5) AS "count" FROM xxx WHERE created >= ('now'::timestamptz - '1 day'::interval) AND customer_id='158' AND

Re: [PERFORM] Why so much time difference with a same query/plan?

Does the order of columns in the index matter since more than 50% customer_id = 158? I think it does not in Oracle. Will the performance be better if I change index xxx_idx to ("domain", customer_id, created)? I will test myself when possible. Thanks, --- Litao Wu <[EMAIL PROT

[PERFORM] Postgres Optimizer is not smart enough?

Hi All, Here is my test comparison between Postgres (7.3.2) optimizer vs Oracle (10g) optimizer. It seems to me that Postgres optimizer is not smart enough. Did I miss anything? Thanks, In Postgres: drop table test; create table test ( modulecharacter varying(50), acti

[PERFORM] reltuples after vacuum and analyze

Hi, I noticed that reltuples are way off if I vacuum the table and analyze the table. And the data (296901) after vacuum seems accurate while the reltuples (1.90744e+06) after anlayze is too wrong. My PG version is 7.3.2 (I know it is old). Any thought? Thanks, my_db=# analyze my_tab; ANALYZE

Re: [PERFORM] reltuples after vacuum and analyze

--- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > I noticed that reltuples are way off if > > I vacuum the table and analyze the table. > > And the data (296901) after vacuum seems > > accurate while > > the reltuples (1.907

Re: [PERFORM] reltuples after vacuum and analyze

g?? Please note all above commands are done within minutes and I truely do not believe the table of 189165 rows takes that much space. Furthermore, I notice last weekly "vacuum full" even did not reclaim the space back. Thanks, --- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu &

Re: [PERFORM] reltuples after vacuum and analyze

Believe or not. The above command is my screen snapshot. I believe it is most possibably a PG bug! --- Tom Lane <[EMAIL PROTECTED]> wrote: > Litao Wu <[EMAIL PROTECTED]> writes: > > reasonable size. But I do not understand > > why "analyze" bloats the table