Partition Pruning Slow

[sramdram]

I have a table with 1.3 billion rows partitioned by date_trunc('day',date_col)

When I issue a query for a date that is not present (i.e no partition available for that date)..the query still runs for about 45 seconds before returning 0 rows.

Is this expected behavior? I would expect an instant response assuming partition pruning occurs ( I am comparing with Oracle which expectedly returns the '0 rows' result quickly...although it is terrible for any other heavy query)

[id10t]

Hi!

>> I would expect an instant response assuming partition pruning occurs
And you are right.

>> Is this expected behavior?
No. Check for statistics: run your query with 'EXPLAIN' in the beginning.
Resolve any "NO STATISTICS" or ''PREDICATE VALUE OUT-OF-RANGE" or "STALE STATISTICS".
You can achieve it by running:

Code: Select all

select analyze_statistics('')

[scutter]

Check to make sure that you are actually getting partition pruning.

create table tpart(c1 integer not null) partition by c1;
copy tpart from stdin direct;
>> 1
>> 2
>> 3
>> 4
>> 5
>> 6
>> 7
>> 8
>> 9
>> 10
>> \.

dbadmin=> \o | grep Pruned
dbadmin=> explain local verbose select count(*) from tpart where c1=100;
dbadmin=> 3[label = "StorageUnionStep: tpart_super\nsubdivision: 1048576\nstartEpoch 0\nendEpoch 33\nPruned storages: 7 (7 rows)\nnodeSet: [0] \n[Local Segmented Properties: UNSEGMENTED]\nUnc: count(*)[100,-3] - Integer(8)", color = "purple", shape = "box"];
dbadmin=> \o

(The data is segmented and my initiator node has 7 of the partition keys)

--Sharon

[sramdram]

I analyzed the table (the original explain showed No Statistics)

After analysis, the query was still slow (maybe a little quicker..but not instant)

Here is the output for the pruning test

explain local verbose select count(*) from tpart where c1=100;
3[label = "StorageUnionStep: tpart_super\nsubdivision: 1048576\nstartEpoch 0\ne ndEpoch 481\nPruned storages: 10 (10 rows)\nnodeSet: [0] \n[Local Segmented Prop erties: UNSEGMENTED]\nUnc: count(*)[100,-3] - Integer(8)", color = "purple", sha pe = "box"];

Does that mean partition pruning occurs ?

[sramdram]

the pruning is fast on our 3 node clustered setup..but slow on a single node setup.

[scutter]

Have you verified that you're getting partition pruning on the single node cluster? The output that you pasted in from the explain was for the example I gave you. What does it show for your real query?

You could PROFILE the query on both the faster and slower clusters, and then compare the clock time counters in EXECUTION_ENGINE_PROFILES.

--Sharon

[sramdram]

For my query..the PrunedStorages showed 0 (Number of storages = 21)

15[label = "StorageUnionStep: fw_srcdst\nsubdivision: 1048576\nstartEpoch 0\nendEpoch 603\nthreads 16\nNo of storages: 21\nPruned storages: 0 (0 rows)\nLocally Resegment By: ((((((100 \<*\> 69069) \<+\> internal_strhash(a.storid)) \<*\> 69069) \<+\> internal_numhash(a.tempid)) \<*\> 69069) \<+\> a.tempid1)\nnodeSet: [0] \n[Local Segmented Properties: SEGMENTED]\nUnc: a.storid[1,4] - Varchar(15)\nUnc: a.tempid[1,10] - Numeric(38,0)\nUnc: a.tempid1[1,13] - Integer(8)", color = "purple", shape = "box"];