Re: [HACKERS] ODBC and palloc ...

Davide Libenzi wrote:
>
> I think this is not my case.
> See attachment log for details.
>

>From looking at the log that is *exactly* your case. I pulled the
offending query out and cleaned it up a bit.

You have a two-part key (padre & figlio) and you can see the multiple
OR's between them. The MS Jet db engine typically uses a rowset size of
10 (so you see 10 keys below) and a keyset size of a couple of hundred
or so. In other words, it first read in 200 keys (the "keyset") and
then uses these keys to access a "rowset" of size 10 out of the entire
"resultset" (how ever many records you have total). This is called a
Mixed (Keyset/Dynamic) cursor or a "Dynaset". Like I said in my last
email, if you change the datasource to be read-only, then re-link your
table in Access, it will not use this style of retrieval and you should
get some results. OR, you can try the other options I mentioned.

SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'PPPA' AND "figlio" = 'AAA'
OR "padre" = 'KKKL' AND "figlio" = 'LLLA'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'

The only problem with this style of retrieving records is that the
Postgres backend can not handle it. It results in exponential memory
usage as it tries to optimize it. You could type in the above query by
hand to the monitor and see the same result.

Then for fun try rewriting the query to use UNIONS instead of OR's and
you will see how fast it is (assuming you have an index). See below.

SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'PPPA' AND "figlio" = 'AAA'
UNION
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'KKKL' AND "figlio" = 'LLLA'
UNION
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
UNION
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
....

Byron