I feel the need for speed. What am I doing wrong?
От | Dann Corbit |
---|---|
Тема | I feel the need for speed. What am I doing wrong? |
Дата | |
Msg-id | D90A5A6C612A39408103E6ECDD77B8294CD786@voyager.corporate.connx.com обсуждение исходный текст |
Ответы |
Re: I feel the need for speed. What am I doing wrong?
Re: I feel the need for speed. What am I doing wrong? |
Список | pgsql-hackers |
I have a query using two postgres tables. One is called "CNX_DS_53_SIS_STU_OPT_FEE_TB" and the other is called "CNX_DS2_53_SIS_STU_OPT_FEE_TB". I am getting 3 times slower performance than Microsoft Access when performing a left outer join. Here are the tables in question: connxdatasync=# \d "CNX_DS_53_SIS_STU_OPT_FEE_TB" Table "CNX_DS_53_SIS_STU_OPT_FEE_TB" Attribute | Type | Modifier ----------------+---------------+----------RT_REC_KEY | character(14) |cnxarraycolumn | integer |CRC | bigint | not null connxdatasync=# \d "CNX_DS2_53_SIS_STU_OPT_FEE_TB" Table "CNX_DS2_53_SIS_STU_OPT_FEE_TB" Attribute | Type |Modifier ----------------+---------------+----------RT_REC_KEY | character(14) |cnxarraycolumn | integer |CRC | bigint | not null Here is the query: select a."RT_REC_KEY", a."cnxarraycolumn", a."CRC" from "CNX_DS_53_SIS_STU_OPT_FEE_TB" a left outer join "CNX_DS2_53_SIS_STU_OPT_FEE_TB" b on ( a."RT_REC_KEY" = b."RT_REC_KEY" and a."cnxarraycolumn" = b."cnxarraycolumn") where b.oid is null ; Creating the following index had no effect on performance! create unique index i1 on "CNX_DS2_53_SIS_STU_OPT_FEE_TB" ("RT_REC_KEY", "cnxarraycolumn", "CRC"); Both tables had 6139062 rows of data. In this query ... all rows of data match perfectly, so no results are returned. Is there a way to reformulate this query so that it will use the index?
В списке pgsql-hackers по дате отправления: